Data Warehousing and Business Intelligence

About Me

Hi, my name is Vincent Rainardi. I am passionate about data warehousing and BI. I work as a consultant (contractor) in data warehousing and business intelligence in London. I do a lot of work with Microsoft BI, Oracle, Informatica, BO. I also work with other DW/BI platforms including Teradata, Netezza, Spotfire and QlikView. I started working with data warehousing and BI in 1996 where I used Comshare Commander PRISM OLAP engine in a project for Toyota (on Oracle). I have design and built data warehouses and BI for RBS, Barclays, UBS, Bluebay, IMS Health, Catlin, Lastminute.com, Veolia, Avery Dennison and Toyota. My background is physics engineering, and I was trained in investment banking, electronics, finance, SQL Server, Oracle, Teradata, .NET and of course data warehousing (TDWI).

I wrote a book about data warehousing on SQL Server platform, published by Apress in December 2007, titled “Building a Data Warehouse, with examples on SQL Server“. The book explains how to design and build a data warehouse on SQL Server, covering business requirements, methodology, architecture, infrastructure, data modelling, ETL, data quality, metadata, reporting and OLAP. In the past I wrote data warehousing & BI articles on SQLServerCentral.com but now I write on this blog instead. With this blog I intend to share my passion about data warehousing and business intelligence. I will be writing about Microsoft BI, i.e. SQL Server, SSAS, SSIS, SSRS, and also about data warehousing in general such as dimensional modelling and ETL architecture, as well as other platforms such as Oracle.

I have MCTS and MCITP on SQL Server 2008 BI and I have 2 MCPs on Database Design and Database Administration. In Nov 2009 I presented at sqlbits in Wales. It’s about creating cubes directly from operational/transaction systems (slides here). In March 2008 I presented Data Warehousing Data Modelling session at SQLBits4 in Manchester (slides here). March 2010: SQL Server 2008 Data Warehousing Features at SSUG in London (slides here). April 2011: Advanced Dimensional Modelling in SQLBits8 in Brighton (slides here)

Apart from doing data warehousing and BI (they are not only my job, they are also my hobby, so I also do it at night, week end, on the train to work), I enjoy spending time with my family, playing chess, renungan (life thoughts) and investing. See my blog on chess here, renungan here and investing here. I live in Cambridge with my wife and 2 children. You can email me at vrainardi@gmail.com. I welcome any discussion about data warehousing, chess, investing or life thoughts.

Vincent Rainardi, 27th Nov 2009 (last updated 2nd March 2015)

24 Comments »

  1. Hi Vincent,
    I like your blog and just to let you know that I found some links in your blog aren’t working.
    For example I can see the following links/post from Google Reader, but when I click on it, it said “page not found”.
    1. https://dwbi1.wordpress.com/2010/01/07/ssas-and-ssrs-are-now-in-informatica-metadata-manager/
    2. https://dwbi1.wordpress.com/2010/01/10/qlikview-vs-powerpivot-connecting-to-ssas-cube/

    btw, are you indonesian?

    Cheers,
    Uzzie

    Comment by Uzzie — 2 February 2010 @ 12:49 am | Reply

    • Hi Uzzie, I updated the URL of some posts a few days ago. I have updated the content page, and tested that all links there are working. Probably it takes a while for Google to catch up. Yes I am Indonesian, from Surabaya/Malang.

      Comment by Vincent Rainardi — 2 February 2010 @ 9:02 am | Reply

  2. Nice blog Vincent! keep up the good work.

    Comment by Sagar — 9 September 2010 @ 8:23 pm | Reply

  3. […] And well, if you are still on two minds whether to write a blog and contribute, read BI expert Vincent Rainardi’s take on Why write a blog? I hope it will give you the final push to start your own […]

    Pingback by Conditional Navigation in SSRS - Some Random Thoughts — 1 December 2010 @ 9:29 pm | Reply

  4. Vincent ..I follow you on SQL Central……Now i follow your blogs….I am a junior level DW/ETL developer….I just want say that your blogs are of immense help for people like me and your article about “why write a blog” is a good one…!! keep blogging with new topics…..your experience would help us a lot…!!

    Comment by mathew — 18 April 2011 @ 2:15 pm | Reply

  5. Awesome blog and lots of great info. mate. I just started blogging about BI (www.bicortex.com) and it is a hard work (if you want to do it right). Anyhow, keep it up and I’ll come and visit soon !!!

    Comment by Marcin — 13 January 2012 @ 3:09 am | Reply

  6. Hello Vincent,

    I found your info via a utube video from Alan Kuu (http://www.youtube.com/watch?feature=player_detailpage&v=ZnevXMiZyOc)

    In the video he uses excel 2010 to build a pivotTable and then he enables What-if to make number inputs in the pivotTable.

    However, I have done exactly the same with my pivotTable, but I get a message saying:… “The data could not be retrieved from the external data source…the cell writeback operation has a calculated as current coordinate, which is not allowed” .

    People tell me that this is a so-called “writeback” problem….but I understand almost nothing about this area.

    Is it possible that you could provide the precise step by step instructions on how to do fix this, so even a normal person can understand the instructions ?

    I am really amazed how microsoft can develop these fantastic tools, but they do a horrible job at explaining to a normal user how to use and implement these tools.

    I will really appreciate your help.

    Regards,

    Alex

    Email: alexkriman@hotmail.com

    Comment by Alex — 30 March 2012 @ 10:37 pm | Reply

  7. Vincent – I just found your blog and you seem to be doing a great job.. Great work..I am going to be a regular visitor from now on!!!

    Comment by mahesh — 1 June 2012 @ 2:17 am | Reply

  8. Great blog, easy to follow

    Comment by Astri — 13 August 2012 @ 11:51 pm | Reply

  9. 6.Hello Vincent, your blog is so neat! I am new to WordPress, could you please tell me which theme your blog is using?
    How to add codes and pictures like in https://dwbi1.wordpress.com/category/ssis/? Do I need to upgrade in order to get that features? Thanks!

    Comment by surfbidn — 24 August 2012 @ 4:24 pm | Reply

  10. Thanks for reply. It looks like we need to install “Alex Gorbatchev’s SyntaxHighlighter” first. I downloaded the plugged in. But I could not upload the files to my wordpress space. Any suggestion? Thanks a lot! — Surfbidn

    Comment by surfbidn — 28 August 2012 @ 8:18 pm | Reply

    • Hi Surfbidn. What plug in? To upload what files? To add a picture, just click on the camera icon. To add codes, just type open square bracket, sourcecode language=”sql”, close square bracket, your code, then square bracket, forward slash sourcecode, close square bracket, just like the example on http://en.support.wordpress.com/code/posting-source-code/. We don’t need to install any plug in to upload pictures or codes.

      Comment by Vincent Rainardi — 28 August 2012 @ 9:51 pm | Reply

  11. Thanks for your reply. Want to see results of directly inputs without plug in? http://tongxintongyu.wordpress.com/2012/08/29/test/ It seems not working:(

    Comment by surfbidn — 29 August 2012 @ 8:30 pm | Reply

  12. You are lucky that your theme already has everything set up. So you can directly use it. BTW, is your blog free version one?

    Comment by surfbidn — 30 August 2012 @ 1:27 pm | Reply

  13. Hi Vincent! I hope you are doing well. I am one of the many that bought your book ‘Building a Data Warehouse With Examples in SQL Server’. I think it is a great book but I need few clarifications.

    Since on the NDS + DDS architecture, the data warehouse keys are maintained on the NDS, how do we go about loading the DDS dim tables? For example, in the NDS customer table, we used SCD transform and have SCD type 2 fields. Updates on the ‘historical’ fields will create a new row. How will the new row impact the record on the DDS dim_customer table? How will we load the dim_customer table? If the SKs are maintained on the dim tables, it would have been easy.

    Should we use SCD Transform to load the DDS dim tables as well like we used on the NDS master tables?

    Does it mean that if we have a new row for a certain customer on the NDS customer table, we will also have new row for the customer on the DDS customer table?

    Comment by win — 4 September 2012 @ 12:42 am | Reply

    • Hi Win, thank you for your questions. They are good ones!

      NDS
      SK|CustomerID|Name|Type|Eff_Date|Exp_Date
      1|G678|Hinton Ltd|Equity|01/01/2008|28/09/2011
      2|G678|Hinton Ltd|FX|29/09/2011|null

      DDS
      SK|CustomerID|Name|Type|Eff_Date|Exp_Date
      1|G678|Hinton Ltd|Equity|01/01/2008|28/09/2011
      2|G678|Hinton Ltd|FX|29/09/2011|null

      Q: How do we load DDS dim tables?
      A: SK is maintained in NDS. DDS uses NDS’ SKs.
      New row in NDS: new row in DDS. Update in NDS: update in DDS.
      SCD process takes place in NDS. DDS mimics NDS.

      Q: How will the a row in NDS impact the DDS dim table?
      A: A New row in NDS: a new row in DDS with the same SK.

      Q: Should we use SCD Transform to load the DDS dim tables like we used on the NDS master table
      A: No. SCD process takes places in NDS. DDS just copies NDS (change tracking).

      Q: Does it mean that if we have a new row for a certain customer on the NDS customer table,
      we will also have a new row for the customer in the DDS customer table?
      A: Yes

      Hope this helps,
      Vincent

      Comment by Vincent Rainardi — 4 September 2012 @ 6:50 am | Reply

  14. Hi Vincent, its a nice blog. You have covered quite interesting topics, some very basic but explained very neatly. I havent read your SQL Server book but sure it must be excellent read as this blog.You mentioned about idea of writing book using Oracle/ODI…I strongly recommend you to consider it..That will be really awesome…There isnt much choice as far ODI..if it there is..it is focussed on tool not sure if goes in detail about using ODI to implement a data warehouse with good practices.. would suggest to include CDC using GoldenGate as well as journalizing. good luck.. and will keep visiting your blog :-)

    Comment by Parag — 10 October 2012 @ 11:54 pm | Reply

    • Thanks Parag. I’m currently pursuing my interests on investing so no longer thinking of writing another DW/BI book.

      Comment by Vincent Rainardi — 11 October 2012 @ 6:41 am | Reply

  15. Vincent, very nice blog! I came upon your blog today and found these tremendous useful information, immediately I saved it in my favorite.
    Can I ask your opinion on Data Vault? Do you store the data in Data Vault structure in your DW design? Do you use Wherescape to perform the loading? I am currently exploring these concepts and tools, want to hear expert’s opinion on these. Thank you!

    Comment by Beverly — 18 March 2015 @ 12:17 am | Reply

    • Hi Beverly, thank you for your question.
      I’m not too sure about implementing Data Vault because:
      a) We end up with many tables requiring many joins which I’m afraid could lead to performance issues (more tables than Star Schema).
      b) I don’t know where to put measures. I’ve read the links below but none of them mention where to put measures. (in Satellite?)
      c) It seems to be more complex to navigate/use/understand (compared to Star Schema / Dimensional Model).
      d) It seems that the main advantage is traceability (where the data comes from, and when it was loaded), and this is already satisfied with BatchKey and SourceSystemKey in Dimensional Model.
      e) It seems that one of the main features of Data Vault is Temporal (all of the data, all of the time kind of thing, like a Temporal DB). But I’m not sure if the DW projects I worked on require this. I mean they require history of attributes & past values of measures, but this need is well satisfied by Dimensional Model (SCD type 2 and Periodic Snapshot).
      f) Resilience to change is also a main feature in Data Vault, but Dimensional Model is already flexible enough, i.e. we can add attributes to a dim and measures to a fact table without causing any issues to existing queries.
      g) I’m not sure how to do Hierarchy, One or Two Dimension (see link 7), Accumulating Snapshot Fact Table, Many-To-Many Currency Conversion, Dynamic Grouping (see Link 8), Parent Key, and City and Rank (see Link 9). Not “how to do them”, but “how to resolve the same business problem using Data Vault”. May be it’s just me being not familiar with Data Vault, and much more familiar with Dimensional Model.
      h) I’m not sure how many successful implementations of Data Vault have been done in banking (most of my DW projects are in banking sector) and therefore I don’t have confidence (yet) in using it for banking or investment data warehouse. Dimensional Warehouses on the other hand, has been implemented for many years in many big banks and investment companies.
      i) Friendliness to BI tools: I’m not sure if BO Universe or Cognos 11 Metadata model, and SSAS DMV are Data Vault friendly. From what I know I think they would prefer Dimensional model than Data Vault model.

      I admire Dan Linstedt’s effort in explaining about Data Vault (see below links), and writing many good articles about it. And I think over the years Data Vault has grown (now 2.0) and becomes more mature (first time I read about DV is probably 3-4 years ago in SQLBits Brighton, somebody from the Netherland was presenting it). But being better than Kimball’s Dimensional Model is very difficult; it has been used for 20 years and hugely popular. To be honest when I first read about Data Vault I thought it would be getting bigger and bigger and eventually replaces Dimensional Modelling (because it is better) but after a few years I don’t think it is as popular as I hoped. So to answer your questions, no I don’t store the data in Data Vault structure. And no, I don’t use Wherescape to do the loading.

      Links:
      1. http://www.tdan.com/view-articles/5054/
      2. http://www.tdan.com/view-articles/5155/
      3. http://danlinstedt.com/datavaultcat/standards/dv-modeling-specification-v1-0-8/
      4. http://danlinstedt.com/datavaultcat/data-vault-2-0-being-announced/
      5. http://danlinstedt.com/datavaultcat/a-short-intro-to-datavault-2-0/
      6. http://en.wikipedia.org/wiki/Data_Vault_Modeling
      7. https://dwbi1.wordpress.com/2010/06/17/one-or-two-dimensions/
      8. https://dwbi1.wordpress.com/2014/05/20/banding-and-grouping-in-data-warehousing/
      9. https://dwbi1.wordpress.com/2012/02/15/city-and-rank/

      Comment by Vincent Rainardi — 19 March 2015 @ 8:32 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 243 other followers

%d bloggers like this: