3

I'm reading Ralph Kimball's books and I'm currently exploring the following data warehouse schema.

AdventureWorks 2008 Data Warehouse Schema

Are both dimension & fact tables populated during the data warehouse creation/update? How often? What about the DimDate table? Do we populate it with all possible dates or only with date used by facts tables?

What is the standard process of generating a data warehouse?

3
  • 2
    What, you're not going with Inmon's DW 2.0? Or Dan Lindstedt's Data Vault? There's lots of standards... ;)
    – TMN
    May 19, 2011 at 14:51
  • @TMN: very interesting. Do you have more?
    – user2567
    May 19, 2011 at 14:53
  • Not handy, those are just the big ones (in addition to Kimball) that I remember off the top of my head. I know Oracle and SAP have their own DW products, and ISTR that there was a coalition of open-source groups (Talend and some others) that were proposing (or going to propose) a standard or guidelines or something. I'll see if I can find my notes and follow up with any substantial links.
    – TMN
    May 19, 2011 at 15:10

3 Answers 3

4

Are both dimension & fact tables are populated during the data warehouse creation/update?

Vague and hard to answer.

How often?

Harder to answer

What about the DimDate table. Do we populate it with all possible dates or only with date used by facts tables.

Really, really hard to answer.

Please keep reading. You need to read up on "Dimension Conformance" and Kimball's idea of the "Dimension Bus".

  1. Dimensions must be populated first. They "accrete" information, sometimes from multiple sources. A common dimension (like "product") will often have multiple viewpoints in multiple applications. This leads to attributes which are loaded separately from separate sources.

  2. Dimensions are "conformed". Data coming in may agree with the existing dimension. Good. Data may not agree. There are many standard "Slowly Changing Dimension" (SCD) algorithms to manage change in dimensional attributes. This is a deep and complex subject. Keep reading.

  3. Facts are matched to conformed dimensions when they're loaded. Fact load schedules depend on the source applications and the warehouse purpose. There's no simple answer to "how often?"

  4. Some dimensions can be pre-populated (like Time) because they're either from external sources (like Time) or they're essentially static. In some cases, it's a handy business fiction to simply declare the dimension static and use special almost-manual utilities to tweak the dimension when a business change occurs. Sometimes a dimension is defined by law or other external standards.

    Pre-populating time is common because it slightly simplifies dimensional conformance. Also, the Time dimension is never a "Slowly Changing Dimension" because time instances never have modifiable attributes.

    Accumulating time as rows are loaded can be annoying because a row in the time dimension often includes rich information like accounting periods and other facts that aren't trivially derivable from the simple Y/M/D date that's available in the input.

2

I'm going to share my one experience building a datacube. I used SQL Server Analysis Services 2005. The company is in retail business and it has stores in several locations. Each store has its own database server but uses the same database schema.

First I pull data from each site into one central database. This is done periodically, in my case monthly. This central database uses the same schema as site database.

Then from this central database, data is massaged to form the 'star schema'. In my case, I wanted to build a sales cube. This sales cube should be able to be sliced by product, date, and location. The sales cube should be able to show sum of item quantity sold, sum of gross sales, and sum of net sales.

In order to create this star schema, I chose to create some views to flatten some table references:

  • One view to join sales header and sales detail tables, exposing sales date, product code, location code, quantity sold, unit price, qty * price, and qty * price - discount. This can be called sales fact table.
  • One view to join product table with its subtables like product category etc. This is the data source for the product dimension.
  • One view to join location table with its subtables. This is the data source for the location dimension.

For date, I created a calendar table containing all dates from 1 Jan 2001 to 31 Dec 2030 that looked like this:

|date      |year|month|dayofweek|
|2001-01-01|2001|1    |0        |
|...

This calendar table is the data source for date dimension.

Next I created a new 'analysis services' project in visual studio. I set the views and tables above as data sources, linked the product code in the sales view to the product dimension, link the sales date to the date dimension, etc, and build the cube.

Analysis services will then set the cube definitions and populate the cube and dimensions. After this process is done, the cube is ready for use.

So the cube is populated when you process it. It will stay the same if you don't reprocess it.

1

Daily updates look reasonable to me, but choose period based on business requirements. I developed solution for trading company, we chose daily overnight updates. Today you can see all transactions and inventory inclusive yesterday, good enough for analytics. Also, we avoided performance problems with transactions systems, we read data before business user start updating data.

When you get data from transaction system, first update your dimensions, you can not insert data into facts tables if you don't have corresponding dimensions.

Populate DimDate with all dates in range. For example, if you don't have sales on 19. may you won't to see that.

Standard process? There are different methodologies. If you like Kimball's approach, try http://www.kimballgroup.com/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.