Jake Smillie

Dealing with large and small SCD dimensions flexibly

JakeSmillie — Tue, 12 Apr 2011 11:09:58 GMT

How many times have you been sat with a customer explaining the differences between a Type-1 and Type-2 attribute? In many cases they are unsure – at least in the requirements gathering phase - how they want to deal with certain attributes in both their Data Warehouse and any reporting they conduct off of that. Indeed they may also want to track the changes in the warehouse, but not show them as changes in reports.

The customers may say, ‘no I don’t care about the history’ and then 2 months later wonder why they cannot see ‘what was’.

As experts, it is our job to ‘know best’ from our experience and model their data structures that provide flexibility for users, so when they change their mind and say to you ‘why can’t I see what was’ we can look smug and say… ‘you can’. Also, on the flip side, your customer might get worried about dismissing data and say they want to keep history of everything without thought, as they do not want to commit to losing any data. You know that some attributes will not require history in reports e.g. ‘Customer First Name’, but the customer wanted to keep everything, so that is what we do.

So, to model dimensions both large and small where you think you might want to change your mind if attributes are Type-1 or Type-2 you can do so in the following way.

I am going to use a simple products dimension to demonstrate how to do this with the following attributes:

So, with initial discussions with the business they have said to you that RRP is a Type-2 attribute and the rest are Type-1. Fair enough.

However, your experience tells you that if a product changes department, the business may not want to attribute historic sales of that product to its new department as it will skew the historical performance, so you have the foresight to make it flexible, to enable the customer to change their mind at a later date.

So, to do this I have 2 tables for my dimensions. One for the current record and one for the history.

The purposes of these tables are as follows

Dim_Product_Current – holds the current record attributes of the product. There is only one version for each product.

Dim_Product_History – holds every version and tracks changes on all attributes Type-1 and Type-2 creating the surrogate keys and RowStart and RowEnd dates along they way. NB. It is optional to track changes on Type-1 as well as Type-2 attributes if you are sure that the the attributes are, and will always be Type-1. Additionally, you will have to be careful on how you design your dimensions such that you do not create many more changes than is necessary. e.g. if a Type-1 attribute changes every day, then you will need to think a different way to model that attribute such that your dimension history table will not balloon fast, but that also applies to normal Type-2s as well.

As a side, I like to keep information in my dimension that tells me whether or not the record has been inferred by an early arriving fact and I like to hold the first introduced date (i.e. the first time the record was seen by the data warehouse).

So, to load this data, the following work flow should take place.

for each newOrChangedRecord

    compare to  Dim_Products_Current

        if new     then

            add to Dim_Products_History

            add to Dim_Products_Current

        end if

        if type_2  then

            end currentRow in Dim_Products_History

            add newVersionRow in Dim_Products_History

            update row in Dim_Products_Current

        end if

        if type_1 then

            end currentRow in Dim_Products_History ' optional if tracking all changes '

            add newVersionRow in Dim_Products_History ' optional again - as above '

            update row in Dim_Products_Current

        end if

loop

The SSIS version of this is shown below.

First we find all the changes that have happened and stage them into a Dim_Products_SCD table marking them wit the type of change, New_Row, Type_1 or Type_2.

Then we use set based updates and inserts to handle the changes in both Dim_Products_Current and Dim_Products_History.

So, lets see what this does. If we consider the following table of original rows.

So, once this has passed through the ETL for the first time, we should see 10 rows in both, Dim_Products_Current and Dim_Products_History.

and alas, we do.

Dim_Products_History

Dim_Products_Current

All sales from this point forward will map to the surrogate Product_Key in the Dim_Products_History.

So now, if we make some changes.

new Product 4545: NewProduct in Produce at 5.00

change the currently type-2 attribute RRP of Pinot Grigio to 5.99 – 2.30 was too much of a bargain

change the currently type-1 attribute of Department of crisps to BWS as we think they may sell better with the beers.

So the new source looks as follows:

So, after running the ETL, we expect to see 3 new rows in the Dim_Products_History and 1 new row in the Dim_Products_Current – for the new product.

Alas we do.

Dim_Products_History

Dim_Products_Current

So, all crisps, and Pinot Grigio sold after 2010-05-04 will get the new surrogate keys in the fact 11 and 12 respectively. Well, that’s all great, but it is confusing to the users and report writers of the warehouse to have 2 tables. Well, to get round this we can create a view warehouse.Dim_Products and, because we can turn Type-1 and Type-2s on and off as we please, without having to re-load facts, suddenly our dimension is very powerful indeed.

So, consider the following view.

alter view [warehouse].[Dim_Products] with schemabinding

as

select

       hist.[Product_Key]

      ,cur.[Product_Business_Key]

      ,cur.[Product_Name] /* I can turn on and off my type1 or type2s */

      ,cur.[Department]   /* I can turn on and off my type1 or type2s */

      ,cur.[Department_Name]  /* I can turn on and off my type1 or type2s */

      ,hist.[RRP]

      ,hist.[RowStartDate]

      ,hist.[RowEndDate]

      ,hist.[FirstIntroducedDate]

      ,hist.[RowIsCurrent]

      ,hist.[Inferred]

      ,hist.[Type_2_Checksum]

      ,hist.[Type_1_Checksum]

from [warehouse].[Dim_Products_Current] cur

inner join [warehouse].[Dim_Products_History] hist

on

    cur.Product_Business_Key = hist.Product_Business_Key

This results in the following

So you can see that Crisps has moved to BWS for all history (Type-1) and RRP has changed (Type-2) for Pinot Grigio. Now I want department to Type-2 as the customer has decided to change their mind. No problem. All the fact keys have got their correct surrogate key, all I need to do is change the view!

alter view [warehouse].[Dim_Products] with schemabinding

as

select

       hist.[Product_Key]

      ,cur.[Product_Business_Key]

      ,cur.[Product_Name] /* I can turn on and off my type1 or type2s */

      ,hist.[Department]  /* I have changed this row */

      ,hist.[Department_Name]  /* I changed this row */

      ,hist.[RRP]

      ,hist.[RowStartDate]

      ,hist.[RowEndDate]

      ,hist.[FirstIntroducedDate]

      ,hist.[RowIsCurrent]

      ,hist.[Inferred]

      ,hist.[Type_2_Checksum]

      ,hist.[Type_1_Checksum]

from [warehouse].[Dim_Products_Current] cur

inner join [warehouse].[Dim_Products_History] hist

on

    cur.Product_Business_Key = hist.Product_Business_Key

This very small change has the ability to change the whole rules of Department for the whole dimension.

As you can see, Department now acts like a Type-2 dimension. So not only can we change the behaviour of our dimension, we can create more views with our desired behaviour for any Data Marts the business requires. This approach also works well for large dimensions as we do not have to change all versions of records for Type-1 changes which can be expensive updates. All we need to do is update the row in Dim_Product_Current.

Finding previous or next row using T-SQL Faster

JakeSmillie — Mon, 11 Apr 2011 17:08:57 GMT

In many cases, it can be required to find the previous or next row within a table. For instance, when wanting to find out the previous date ‘something’ happened or the previous version of a record.

I am going to show you two different ways to do this, one that performs better than the next.

Consider the following date table from AdventureWorksDW

It may be useful, for a date, to find the previous date. A common business problem that may require this, is in a financial services system where we need to know how many days interest has accrued for.

So the output we are after is something as follows:

As you can see we have the DateKey along with the previous date key: PrevDate.

To get this, many will use the following SQL

set statistics time off

set statistics io off

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

set statistics io on

set statistics time on

select

    prev.PrevDate,

    prev.PrevFullDateAlternateKey,

    d.DateKey,

    Datediff(day, prev.PrevFullDateAlternateKey, d.FullDateAlternateKey) as DaysInterestAccrued

from dbo.DimDate d

inner join

    select

        d.DateKey,

        MAX(p.DateKey) PrevDate,

        MAX(p.FullDateAlternateKey) as PrevFullDateAlternateKey

    from dbo.DimDate d

    left outer join dbo.DimDate p

on

        d.DateKey > p.DateKey

    group by

        d.DateKey

) prev

on

    d.DateKey = prev.DateKey

Table 'DimDate'. Scan count 1190, logical reads 13934, physical reads 2, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 682 ms.

So here, in the inner select we are finding the previous date for each date. We then join on this inner select to find the details we require.

Another way to achieve identical results is to use cross apply

set statistics time off

set statistics io off

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

set statistics io on

set statistics time on

select

    prev.PrevDate,

    prev.PrevFullDateAlternateKey,

    d.DateKey,

    Datediff(day, prev.PrevFullDateAlternateKey, d.FullDateAlternateKey) as DaysInterestAccrued

from dbo.DimDate d

cross apply

    select

        MAX(prev.DateKey) PrevDate,

        MAX(prev.FullDateAlternateKey) as PrevFullDateAlternateKey

    from dbo.DimDate prev

    where

        d.DateKey > prev.DateKey

) prev

Table 'DimDate'. Scan count 2377, logical reads 5567, physical reads 5, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 234 ms.

For the dataset I am using in AdventureWorksDW, the cross apply version of this code reduced the execution time by about half due to both having to do less logical reads and to not having to do expensive Nested Loop joins on the the full dataset within DimDate in the sub select.

UK OUG & Shared Services Security

JakeSmillie — Thu, 18 Mar 2010 17:35:00 GMT

This week I attended and presented at my first UK Oracle User Group, Special Interest Group (SIG) for Essbase and Planning. Attending the UK OUG was definitely beneficial and great to find out what other people are doing with the products. I would thoroughly recommend this to anyone who has an interest in Essbase and Planning to attend these events. You can join the UK OUG by signing up http://www.ukoug.org/ and you should.

As I said I also presented a piece on Hyperion Shared Services 11.1.1.3 with tips and tricks on how to setup security and why you should use it. To see the slides please join UK OUG and go http://www.ukoug.org/lib/show_document.jsp?id=11565

Enjoy, and I hope to see you at future UK OUG meetings!

Modelling & Loading a Slowly Changing Many-to-Many Relationship

JakeSmillie — Thu, 18 Feb 2010 13:56:31 GMT

I haven’t blogged for a while, mostly because I have been busy. Today I am on a train to London , so I thought what better thing could I do to fill the time than to blog about something I have been meaning to blog about for a while: Modelling Slowly changing many-to-many relationships in a Data Warehouse.

This is something that, on paper, seems easy, but also very easy to get wrong / fall into common pitfalls.

When I was first asked to model such a scenario I went looking in text books and Google (or do I mean Bing now?). Anyway, I was shocked to see a distinct lack of information about this or at least no information that went into enough detail. So here is my in detail explanation on how to do this.

Consider the scenario where you have products and groups of products. A product can belong to many groups and they can also move in and out of groups, but we want to measure sales at any given time for these groups. Your Products dimension can slowly change, as can your ProductGroups, as can your ProductGroup_Mappings!

Below are extracts of an example Product, ProductGroup dimensions and a Sales Fact Extract.

Group 1 has Product A up until 20th-Feb-2009 and has Product B always. So if we wanted to get the Sum of all sales for Group 1 we would get 5(Transactions 1,2,4,5,6). We would not include 3 because this sold product A when it was not part of Group 1 (after 20th-Feb-2009).

To model this in a Data Warehouse, you may initially do it like the following:

Linked on Business Keys

So from the above you can see after 20th-Feb-2009 Product A is no longer part of group 1. This would be fine, and work if you are using SQL to retrieve data because you can add some between clauses using the RowStart and End dates etc when doing a retrieval to find total sales for Group 1, but for me, this is missing the point of a Data Warehouse. Querying a well designed Data Warehouse should be simple, I don’t want to have to worry about complex where clauses to get the data I want. It should all just work! Besides if you had an OLAP engine such as SSAS on top of your Data Warehouse it would not know what to do with RowStart and End Dates.You need to be able to write queries by just using simple joins without where clauses on RowStart and End dates to do things like this. The best way to think about this is VLOOKUPS in Excel. SSAS will in essence do VLOOKUPS from your fact to your dimension to get the correct dimension member.

So let’s see how this would aggregate all sales for Group 1.

So what’s happening here?:

First find all products in Grp1 (I used a VLOOKUP for this: Matches twice and return Products A & B.

Then:

(sum all sales where Product = A) + (sum all sales where Product = B): I used SUMPRODUCT for this

As you can see it has matched on all sales for Product A which is wrong. We want it to exclude transaction 3 as it happened when Product A was not in Group 1.

This approach on joining on business keys works fine for groups that do not slowly change. E.g. You add a product to a group and all sales of that product are attributed to that group. You then remove it and none are attributed to that group.

Ah… I need to do the relationship on surrogate keys. After all this is why we have them in slowly changing dimensions!

Linked on Surrogate Keys

Product Dimension for reference:

Now we see and extra row (2) due to the slow change of product A. Now lets see what happens when we link on keys to the sales.

Sales data for reference:

So again, we first get the product keys that are attributed to Group 1: (P1,P2 & P3) and we then aggregate all sales where those are the keys for.

Bingo! We have the right answer. Modelling on surrogate keys is definitely the way forward. But is that it? The more astute of you will realise that this has the exact same problem as the Business Key modelling. If we had a sale of Product A on the 22-Feb-2009, after Product A has been removed from group 1 but before it slowly changed itself (on 01-Mar-2009) the join would include that sale, wouldn’t it?

Well lets see…

Transaction 7 is the new transaction.

Well yes our suspicions are correct it does break!

What this means is that if any change to a group happens (add a remove a product to it) we need to slowly change the products in the ETL or ELT process before any sales data (or any fact data) comes through. So lets see what the new Product dimension looks like when this happens.

There is a new product key for product A which starts on the 20th-Feb-2009 (when Product A is removed from Group1) and then again for P5 is the normal slow change on 01-Mar-2009. This makes the sales fact look like this:

Now it definitely works. Good stuff.

Conclusion

- You need to model such scenarios using surrogate keys and not business ones

- Every time the members of a group change you need to force a change on the affected dimensions. This process makes the loading of the warehouse complex especially when you have multiples of these relationships and they change frequently, but who said loading Data Warehouses was easy?

There is one other way to do this relationship without having to force the change of the members and that is to include the date key in the many-to-many mapping. Whilst this would work, it could have a detrimental affect on performance as you could quickly have millions of rows in your mapping table / bursting view (if you join on dates dimension).

Finally I have attached my Excel spreadsheet of my working so you can have a play. Enjoy!

ManyToManyModelling.xls

Jake

SmartView – Timeout Increase NetRetryCount

JakeSmillie — Mon, 02 Nov 2009 12:44:00 GMT

Recently I have been tearing my hair out with SmartView as whenever I perform a large query against my Essbase cube I receive a timeout that suggests I should up the NetRetryCount.

“Fine” I thought, so I updated the Essbase.cfg file with the following (just to be sure):

NETDELAY 8000
NETRETRYCOUNT 8000

and restarted Essbase and re-tried my query.

Same Error! That config change seems to have done nothing.

A little hunting around later, I discovered there was another setting I need to change (in APS essbase.properties file). Of course! I am connecting through provider services to connect to Essase. It is APS that is timing out. So I update essbase.properties to:

olap.server.netConnectRetry=8000
olap.server.netDelay=8000
olap.server.netSocketTimeOut=800

restarted Provider Services (and Essbase for good measure) and re-tried my query.

Grrr! Same error! What is going on?

Some more hunting around and reading endless posts of making sure you restart the services after changing the config settings I stumbled across this forum http://www.network54.com/Forum/58296/message/1212599342/Hyperion+figured+out+my+issue

It seems Internet Explorer has a default timeout, and it seems that SmartView uses these settings:

http://support.microsoft.com/kb/813827

http://support.microsoft.com/kb/181050

IE 6 has a default of timeout of 60 seconds and 7 has a default of 30 seconds. I added the following registry settings

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]

"ReceiveTimeout"=dword:00075300

"KeepAliveTimeout"=dword:000493e0

"ServerInfoTimeout"=dword:000493e0

Where 000493e0 = 5 minutes and 00075300 = 8 minutes.

I restarted Excel and I no longer received the timeout Increase NetRetryCount issue. I will now include this in any install I do of SmartView

I hope this helps someone else. I feel your pain!

Importance of Unrealised FX Gain and Loss

JakeSmillie — Fri, 07 Aug 2009 05:18:41 GMT

Many businesses want to see measures across multiple currencies, for example: sales for all countries or customer balances for all account currency types. Typically when dealing with multiple currencies a ‘base’ currency to report on is chosen to make life easier (you can’t simply add Euro sale values to Sterling sale values and get the correct total sales answer). This means data needs to be converted to base currency so we can accurately see how we are doing.

Currency exchange rates are by no means static, so movement in exchange rates can influence your measures.

For example, if you wanted to see total balances across months you might see something like this:

Looking at this we can see that May 2009 was a great month!…. Or was it?

Well lets look at the raw data that makes this chart.

You can see in May 2009 we actually lost €100,000. The above report paints a very different picture to what actually happened! The reason why the chart shows such a large jump is that Sterling became a lot stronger against the Euro giving the illusion of a good month, when really it wasn’t.

This means we have to track Unrealised FX Gain and Loss to understand the numbers better.

Depending on whether you are using ‘End of Day’ or ‘Start of Day’ exchange rates this is done differently.

End of Day Rate

To calculate unrealised FX using end of day rates use this formula:

(Today’s Measure @ Today’s Exchange Rate) - (Today’s Measure @ Previous Exchange Rate)

Start of Day Rate

To calculate unrealised FX using start of day rates use this formula (rates should be in direct quotation e.g. local ccy value * rate = base ccy value)

(Today’s Measure - (measure delta)) * (exchange rate delta)

Once unrealised FX is calculated we can show a slightly different report:

We will use the Balance £ – Unrealised FX Accumulation to display in our report, which gives us:

This shows something quite different from the report above, but it shows a more accurate visualisation of the numbers. It shows that May 2009 was actually a bad month.

Now that you understand the importance of tracking Unrealised FX you need to decide at what granularity to track it. I would recommend tracking it at the lowest level possible so that you can see it at any aggregation level. This makes your design a little more tricky but gets you the most flexibility.

Multi Level Hierarchy Dimension Design Tip

JakeSmillie — Fri, 07 Aug 2009 03:34:16 GMT

When building a dimensional modelling solution, somewhere along the line you will almost certainly have at least one multi level hierarchy within one or more dimensions. A good example of this is a Product hierarchy, which might go:

Department->Sub Department->Type->Product

Typically for each level member you will have an ID (be it numeric or alpha numeric) and a Description. In the Adventure Works database there is a product hierarchy that goes: Department->Sub Department->Model->Product, so this seems like a nice example to share this tip.

Lets have a look at how you might store this data in your conformed dimension:

So here, Product 709 rolls into Mountain Bike Socks, Socks and Clothing.

Building this in a cube is simple, but sometimes not all reports are suited to cube and are better delivered using SQL.

Lets consider a requirement whereby the user wants a dump of all sales between dates and wants to filter by any level in the Product hierarchy (e.g. any Clothing, or just Socks).

There are many ways to provide this solution but there is 1 way I like.

Assuming that you would do this in a stored procedure, you might pass in each level ID for Category, Subcategory, Model and Product as a parameter and then determine what to filter on based on their selection (e.g. If they selection ‘All’ for Subcategory do not filter on this and so on. Doing this is complicated and gives me a headache, you might use 4 different almost identical SQL statements and have IF (SubcategoryIDSelection = ‘All’) then run this query, otherwise run another query. Confused? I am. You may use dynamic SQL to build up the query (which is a nightmare to maintain / understand what is going on).

The way I like to do achieve this is to store a unique hierarchy key as a string for each product. So for Product 709 we would have: ‘0003_0023_000013_000709’ as the ProductHierarchyKey (we pad the keys to make them sortable). This makes filtering on any level of the hierarchy very easy. For example if the user requests to see all sales for Clothing_Socks we pass into the procedure ‘0003_0023’ as the value we can then do a select and filter like this:

   1: SELECT

   2:     p.*

   3: FROM dbo.D_Product p

   4: WHERE

   5:     p.ProductHierarchyCode like '0003_0023%'

Which will bring back products 709 and 710. As you can see, it doesn’t matter what level you pass into the procedure, it will always be the same query (no need to build any funky logic or use dynamic SQL). It also makes it easier to filter on multiple selections, e.g. the user might want to see sales for ‘Socks’ and ‘Road Frames’ together. So we would pass in ‘0003_0023,0002_0014’ then split the parameter by ‘,’ into a table variable (I will let you decide how to do this as there are many techniques) and then execute SQL similar to:

   1: SELECT

   2:     p.*

   3: FROM dbo.D_Product p

   4: INNER JOIN @SelectedProducts sel

   5: ON

   6:     p.ProductHierarchyCode like sel.Value + '%'

This approach, for me, is far simpler and easier to understand.

Hope it helps.

Creating Slowly Changing Hierarchies in SSAS 2005

JakeSmillie — Fri, 03 Jul 2009 10:27:52 GMT

In a Data Warehouse, you’ll typically get at least one slowly changing hierarchy. You’ll probably also have other

slowly changing attributes on the same dimension that are not in the hierarchy, causing many changes.

Setting up this scenario in SSAS 2005 is easy, but it is also easy to do it wrong! Therefore I thought it would be

useful to blog on how I do this and what potential pitfalls one can come across when attempting this for themselves.

Consider a Product dimension that has a Supplier hierarchy. The business wants this hierarchy to change over

time. Additionally there is another Type-2 attribute: RRP, which also changes over time.

So, Product ‘1234’ changed from SupplierGroup1->SupplierA to SupplierGroup1->SupplierB on ‘2002-Feb-01’.

That supplier didn’t work out so it went back to SupplierGroup1->SupplierA on ‘2002-Apr-01’. Also on’2002-Mar-01’ the price changed from £9.99 to £10.99.

If browsing the cube by Supplier Hierarchy and Date you would want to see all sales for Jan-2002 and Apr-2002

onwards to by SupplierGroup1->SupplierA and from Feb-2002 to end of Mar-2002 to SupplierGroup1->SupplierB.

So, lets see how you might go about setting this up in SSAS 2005.

(For the purpose of this blog I have setup 3 dimensions all using the same Product dimension but setup

differently and created a Fact Sales that has 1 sale for every day from 2002-Jan-01 to 2002-Apr-30).

Initially you might set this up like a normal type-1 hierarchy that does not track history, and expect SSAS to sort it out when things change. This is what I call the Bad Hierarchy Product dimension.

Bad Hierarchy Product Dimension

In this scenario we have created a Supplier hierarchy that goes Supplier Group->Supplier->Product Code. The product code being the natural key of the product dimension e.g. ‘1234’.

You can see by the attribute relationship that Product Code rolls up into Supplier. Well, lets see what results that gives us.

Hmm, not quite what we were hoping for. The hierarchy doesn’t seem to have changed at all!

Well this is easy to explain. In the attribute relationship we have told SSAS that Product Code rolls up into Supplier. When SSAS processes dimensions it will retrieve the distinct value of every attribute and then aggregate them based on attribute relationships we define. SSAS will only have one value for product code ‘1234’ and that is, yep you guessed it, ‘1234’. SSAS will then find the first Supplier it finds for Product Code ‘1234’ and say, “right that’s it I’ve got what I want, no need to do anything else” hence the supplier hierarchy never changes.

So the solution is simple:

Type-1 attributes are attributes of the natural key (Product Code)

Type-2 attributes are attributes of the surrogate key (Product Key)

So having just worked out this rule, you may try building the hierarchy by using the surrogate key as the bottom level of the hierarchy. I call this the duplicate product dimension

Duplicate Product Dimension

This time, we have used the Product Key as the lowest level of the hierarchy, and all Type-2 attributes are attributes of the surrogate key. For the Product Key attribute we have changed the ‘Name’ property to be the Product Code, but the ‘Key’ property to be the surrogate key.

So lets see what results that give us.

Hmm well the good news is that the hierarchy has changed, but we seem to be getting duplicate product codes beneath each one.

Well again this is easy to explain. We have 4 surrogate keys for product code ‘1234’. We have set the hierarchy to roll up from Product Key to Supplier. SSAS does not know that product key 1,2,3 and 4 have the same Product code and therefore you want to group by them so it just adds them all.

So, at least our numbers above product code are correct, but this will still not do!

So let see how we do this properly.

Product Dimension

We create a new attribute in the dimension called ‘SupplierLeaf’ that has composite keys: Supplier Group, Supplier and Product Code and set the ‘Name’ property to ‘Product Code’.

We then setup the hierarchy as follows.

So we use the new attribute ‘SupplierLeaf’ as the lowest level of the supplier hierarchy, but rename it to ‘Product Code’ within the hierarchy. We set supplier to be an attribute of Supplier leaf and set supplier leaf as an attribute of the surrogate key. So lets see how this looks.

Bingo! we done it correctly. So, for every slowly changing hierarchy in a dimension you need to add a new ‘Leaf’ attribute. to get it to work correctly.

One you have set this up, it is quite logical and easy to understand how to to it. But as I say, it is easy to get wrong. I hope this helps

Oracle Essbase 11 Excel Add-in Installation Gotcha

JakeSmillie — Wed, 22 Apr 2009 14:14:07 GMT

Today I installed the Oracle Essbase 11 Excel add-in. All worked well and I could connect to Essbase fine.

When another user logged on to my machine and attempted to use it, the user could not connect to Essbase using the Excel add-in (nothing would seem to happen) and when trying to initialise the API would receive error #1030000.

It seems that whilst installing the add-in, the normal environment variables ARBORPATH and ESSBASEPATH were created, but only as user defined variables.

After installation you will need to check this and change them to system environment variables so that any user can use the add-in. They should both be set to:

%HYPERION_HOME%\products\Essbase\EssbaseClient

Kimball Microsoft Data Warehouse: What they don’t tell you! Part 4 – Dealing with Failed Lookups & Early Arriving Facts – Alternative Approach

JakeSmillie — Tue, 28 Oct 2008 15:26:07 GMT

Welcome to my 4th and final post of my 4 part series of the Kimball Microsoft Data Warehouse toolkit. I hope you have found it interesting so far. If you have missed any posts, below is the outline of my series:

Part 1 – SCD Dates - (What dates should I use in my RowStart and RowEnd dates?)

Part 2 – Type-1 Changes and Historical Data

Part 3 – A Composite Type Change (Type-1 & Type-2)

Part 4 – Dealing with Failed Lookups & Early Arriving Facts – Alternative Approach

Part 4 – Dealing with Failed Lookups & Early Arriving Facts – Alternative Approach

The Problem

Early arriving facts and implicitly failed dimension lookups are a common situation in Data Warehousing. One of the best examples of this would be the Customer Loyalty Card scheme. I am sure when shopping you have been asked to open up a new customer loyalty scheme, whilst being ensured that the current purchase will be added to your points total.

Well, in this situation the point of sale will be the first time the new customer exists in a Data Warehouse. When we do a lookup against the Customer dimensions we won’t find the key. What do we do?

What Kimball Say

Kimball give a few ideas in handling a situation like this, but their preferred method is using inferred dimension members. Which, in short, is adding a stub dimension member (for example in the problem described above just adding the Customer number – probably the loyalty card number – to the Customer Dimension and wait for the details of that customer to come into the dimension at a later date). You can read a bit more about this here. Additionally there is a more in depth explanation from Tom Stagg here.

This approach works very well for the problem described and is probably the best method for this type of problem. However, what happens if you need some dimensional data to work out fact data (e.g. If you have a situation where a new product can lend itself to early arriving facts – the transactional system is not kept up to date that well – you might want to store profit in the fact using the ETL to avoid calculating it. For this you need cost price of the product which will be dimensional data and not likely to feed from sales data, but wait a minute: This is a new product! We don’t know about it so how can we know the cost price).

The Solution

Well there are a few things you can do.

- Don’t work out the profit of the sale (We don’t want to do this because this would make our Data Warehouse wrong)

- Load it in initially with the profit as 0 and infer the new product. When we get the cost price of the new product we then update the fact. (This would work, but to me, it sounds very complex to add workflow into dimensions to then work out what facts, if any it needs to update and additionally updating fact rows is considered as best avoided)

- Load them into a Fact_Failed_Lookup table. (This is my preferred approach and I will tell you why)

So, lets take a look at what I mean.

Imagine we have a Product dimension with one product in and an Unknown member.

Products_ID	Product_Key	Product_Name	RRP	Cost_Price	RowIsCurrent	RowStartDate	RowEndDate
-1	0000	Unknown	0.00	0.00	1	01-Jan-1900 00:00:00	31-Dec-9999 00:00:00
1	1234	Lettuce	0.99	0.50	1	01-Jan-1900 00:00:00	31-Dec-9999 00:00:00

If we sell product 1234 we can work out the Profit using the cost price and the transaction price (Price - Cost Price).

Transaction	Date	Product_Key	Qty	Price	Profit
1	20081028	1234	1	0.99	0.49

However if we sell a “Beer” (product code 5555) which isn’t in our Dimension we don’t yet know the cost price and cannot work out the profit.

Transaction	Date	Product_Key	Qty	Price	Profit
2	20081028	5555	1	4.99	xxx

So, we load the transactions as follows.

Fact_Sales

Transaction	Date_ID	Product_ID	Qty	Price	Profit
1	20081028	1	1	0.99	0.49

Fact_Sales_Failed_Lookup

Transaction	Date_ID	Date	Product_ID	Product_Key	Qty	Price	Profit	Estimate
2	20081028	20081028	-1	5555	1	4.99	0	TRUE

When we load the cube we use a view that unions the Fact_Sales and the Fact_Sales_Failed_Lookup key values (additionally we add a view for RDBMS reporting access that does the same)

This means the overall takings are correct but the profit may not be. I like to add Estimate to the cube so that business users can filter on estimates or not if they wish.

Now, each day the failed lookup fact table is monitored by the Standing Data team where any invalid business / natural keys can be modified (e.g. Typo issues) and investigation into failures. Each day as part of the ETL the Failed Lookups are truncated and dimension lookups are re-tried (or this can be scheduled to do so on a weekend perhaps). If they fail again they go back into the failed lookups, if they succeed they go into the real fact.

This is the workflow:

This approach has added advantages in that:

- If typos occur in fact data they can be updated easily and the ETL just fixes it

- If you want / need fact data more frequent than dimension processing, you can just add all fact data that is newer than your dimensional load date to your failed lookup table and mark them as estimates. The next time the dimensions are processed the estimate rows can simply flow into the real fact.

However be warned using this approach making sure the Failed Lookup facts are actually looked at by the Standing Data Team and they don’t just keep growing and growing.

This concludes my series, which I hope you have found useful and valuable.

Kimball Microsoft Data Warehouse: What they don't tell you! Part 3 – A Composite Type Change (Type-1 & Type-2)

JakeSmillie — Mon, 27 Oct 2008 11:46:31 GMT

This is the third part of my 4 part blog series that is aimed to give the world more insight on some of the Kimball Group concepts and, sometimes difference in opinions. Below is the outline of the series.

Part 1 - SCD Dates - (What dates should I use in my RowStart and RowEnd dates?)

Part 2 – Type-1 Changes and Historical Data

Part 3 – A Composite Type Change (Type-1 & Type-2)

Part 4 – Dealing with Failed Lookups & Early Arriving Facts – Alternative Approach

Part 3 – A Composite Type Change (Type-1 & Type-2)

Today I am going to talk about a Type of change that is a combination of Type-1 and Type-2 changes. This is not the well understood Type-1.5 change but a composite Type change where multiple columns are related to one and other and each having a different change type. Below is the outline of the problem.

The Problem

In my previous posts I have used a simple Product dimension, and I will continue to use it. It is quite likely a Product dimension will have some sort of Product Category or Brand attributes e.g. Produce, Beers Wines & Spirits, etc. It is also quite likely that there will be a business key for the product categories e.g. 100 = Produce, 200 = Beers Wines & Spirits, etc. Lets take a look at what that might look like:

Products_ID	Product_Key	Product_Name	Dep_Code	Dep_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Lettuce	2	Grocery	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Lettuce	2	Grocery	1.3	1	24-Oct-2008 00:00:00	31-Dec-9999 00:00:00

In this simple dimension model it would be quite likely to have Department as a Type-2 and Department_Name as a Type-1. The business should be able to change the alias of the department without affecting their figures but if a product changes department then we don’t want historic sales to move with it.

So what would happen if we get a new row like this:

Product_Key	Product_Name	Dep_Code	Dep_Name	RRP
1234	Bag of Lettuce	2	Grocery & Frozen	1.3

So, here the Product_Name (Type-1) has changed as has the Department_Name (Type-1). If we flow it through the Kimball sample ETL approach

In this situation the new row will flow down the Type-1 change route, updating the Product_Name and Department_Name for all historic rows and current row. The table will now look like this:

Products_ID	Product_Key	Product_Name	Dep_Code	Dep_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Bag of Lettuce	2	Grocery & Frozen	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Bag of Lettuce	2	Grocery & Frozen	1.3	1	24-Oct-2008 00:00:00	31-Dec-9999 00:00:00

This is all fine and expected. However have you spotted the issue yet? What happens if Product: 1234 changes Department e.g:

Product_Key	Product_Name	Dep_Code	Dep_Name	RRP
1234	Bag of Lettuce	3	Produce	1.3

In this situation the Checksum for Type-1 and Type-2 will mark a change; Department has changed from ‘2’->’3’ causing a Type-2 checksum to change and Department_Name has also changed from ‘Grocery & Frozen’->’Produce’ causing the Type-1 checksum to change.

So, we update all the historic rows with the new Type-1 data and insert the new Type-2 row (after handling the Type-2 change). Using this logic the Dimension will now look like this.

Products_ID	Product_Key	Product_Name	Dep_Code	Dep_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Bag of Lettuce	2	Produce	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Bag of Lettuce	2	Produce	1.3	0	24-Oct-2008 00:00:00	27-Oct-2008 00:00:00
3	1234	Bag of Lettuce	3	Produce	1.3	1	27-Oct-2008 00:00:00	31-Dec-9999 00:00:00

Uh-oh!! The current row is correct but the previous rows have had their Department_Name’s updated which is wrong!! We want the Dimension to look like this:

Products_ID	Product_Key	Product_Name	Dep_Code	Dep_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Bag of Lettuce	2	Grocery & Frozen	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Bag of Lettuce	2	Grocery & Frozen	1.3	0	24-Oct-2008 00:00:00	27-Oct-2008 00:00:00
3	1234	Bag of Lettuce	3	Produce	1.3	1	27-Oct-2008 00:00:00	31-Dec-9999 00:00:00

What Kimball Say

Well I haven’t read anywhere in Kimball media regarding this specific problem (however I would like to caveat that with the fact I haven’t read all their media), but they do give out a sample SSIS package that would fall over if a dimension was modelled in this way. I do not think by any stretch of the imagination that Kimball have provided this sample as an all conquering, all problem solving solution to all your dimension needs and why should they? However it would be quite easy to fall into the trap of assuming it would work for your dimension. This post’s purpose is to share knowledge and try and prevent people tripping over this problem.

The Solution

Some of you may be reading this and thinking, “I would just use a Snowflake for the Department”, and yes this would solve the issue for the specific problem I have described (where you only have 1 level in the Department hierarchy), but this is probably quite unlikely in a production Data Warehouse, as you would probably have multiple levels; meaning you would just get the same problem in the Snowflaked dimension (I doubt you would want snowflake on top of snowflake for each level in the hierarchy).

In actual truth I do not have a specific (generic) solution to this problem yet, except to say that you need to build in some logic into your Type-1 updates to ensure that if the related Type-2 attribute changes, you don’t update all the historic data also. Each problem will have a slightly different approach to best solving this problem. If I do work out a way to capture all issues I will be sure to post the solution.

Let me know how you have solved this in the past.

Kimball Microsoft Data Warehouse: What they don't tell you! Part 2 – Type-1 Changes & Historical Data

JakeSmillie — Fri, 24 Oct 2008 14:29:48 GMT

This is the second part of my 4 part blog that is aimed to give the world more insight on some of the Kimball Group concepts and, sometimes difference in opinions. In my first blog I talked about my difference in opinion to Kimball Group on handling Type-2 changes in dimensions and what dates to use to end and start the new row. You can read that here.

Below is an overview of my series:

Part 1 - SCD Dates - (What dates should I use in my RowStart and RowEnd dates?)

Part 2 – Type-1 Changes and Historical Data

Part 3 - A Composite Type Change (Type-1 & Type-2)

Part 4 - Dealing with Failed Lookups & Early Arriving Facts - Alternative Approach

Part 2 – Type-1 Changes and Historical Data

In general the concepts of different Type changes within dimension processing is pretty well understood. There is a lot of material out there that describes the different types. ETL-Tools.info has a brief explanation if you need one. There is also a lot of information on how to deal with them within your ETL.

The Problem

In my experience many of your Dimensions within your Data Warehouse will have both Type-1 and Type-2 attributes. This is fine and also accepted, but when we have a member that has gone through many Type-2 changes over time, what do we do when we get a Type-1 change on that member?

Consider the changes below:

Products_ID	Product_Key	Product_Name	Dep_Code	Dep_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Lettuce	2	Grocery	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Lettuce	2	Grocery	1.3	1	24-Oct-2008 00:00:00	31-Dec-9999 00:00:00

On 25th of October we get a Type-1 change to the Product name changing it to ‘Bag of Lettuce’

Do we change Products_ID 1 and 2? or just 2 (the current row)?

What Kimball Say

In fairness, Kimball does suggest the correct way of dealing with this, but I would be more forceful and say it is THE way to deal with it.

So Kimball suggests that we would normally change 1 & 2 (change for all historical rows also) and I believe this to be correct. So lets see why.

If we had this data in the product dimension

Products_ID	Product_Key	Product_Name	Dep_Code	Dep_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Lettuce	2	Grocery	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Bag of Lettuce	2	Grocery	1.3	1	24-Oct-2008 00:00:00	31-Dec-9999 00:00:00

and this data in our sales fact

Date_ID	Products_ID	Qty
20081023	1	1
20081024	2	1

How many “Bag of Lettuce” have we sold in total?

Well lets see… This could be the SQL:

   1: SELECT

   2:     Qty as 'Total Qty'

   3: FROM Fact_Sales s

   4: INNER JOIN Dim_Products p

   5: ON

   6:     s.Products_ID = p.Products_ID

   7: WHERE

   8:     p.Product_Name = 'Bag of Lettuce'

The answer given here would be 1.

This is wrong because we have actually sold 2. (I appreciate that for this type of query I would probably use the Product_Key but I am just demonstrating the issue).

This means our aggregations are invalid and not correct. If we change the historical row(s) (as suggested) as well, our aggregations remain in tact.

So now our product dimension looks like this

Products_ID	Product_Key	Product_Name	Dep_Code	Dep_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Bag of Lettuce	2	Grocery	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Bag of Lettuce	2	Grocery	1.3	1	24-Oct-2008 00:00:00	31-Dec-9999 00:00:00

And if we use the same SQL above then we get the correct answer.

It does mean on large dimensions that change quite frequently, Type-1 changes can become expensive within your ETL, but expensive is OK if you get the correct answer.

The Solution

The solution is easy. Always update all your history for Type-1 changes.

An alternative to this approach is to create a view on top of your dimensions that joins on itself to retrieve the most recent Type-1 attributes but I see this as a bit of a hack and find it much more sensible to keep the dimension as clean and conformed as possible.

Let me know your thoughts.

Kimball Microsoft Data Warehouse: What they don't tell you! Part 1 - SCD Dates

JakeSmillie — Fri, 24 Oct 2008 10:36:00 GMT

I was recently lucky enough to attend the Kimball University "Microsoft Data Warehouse in Depth" course, which I thoroughly recommend to anyone looking at the Microsoft toolkit for their Data Warehouse solution. It is well structured, well thought out and delivered in a clear and authoritative manner. Both Warren and Joy know their stuff (details about Warren and Joy can be found here).

All this being said I did feel they missed out a few 'gotchas', and some of their methods and advice conflicted with a few of my personal opinions regarding Data Warehousing best practices (at least in the Microsoft Toolkit). This blog series is going to talk about these things and why I feel they are important.

Part 1 - SCD Dates - (What dates should I use in my RowStart and RowEnd dates?)

Part 2 – Type-1 Changes and Historical Data

Part 3 - A Composite Type Change (Type-1 & Type-2)

Part 4 - Dealing with Failed Lookups & Early Arriving Facts - Alternative Approach

Part 1 - SCD Dates - (What dates should I use in my RowStart and RowEnd dates?)

The Problem

When I Type-2 change a dimension member what Start and End dates do I use?

Lets have a look at an example scenario:

Products_ID	Product_Key	Product_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Lettuce	0.99	1	01-Jan-1900 00:00:00	31-Dec-9999 00:00:00

Now we load the dimensional data for 24-Oct-2008 which changes the RRP to £1.30 of Lettuce (we are all feeling the credit crunch squeeze!). This would be a Type-2 change. So how do we add a new row and what dates do we use in the RowStart and RowEnd dates? Is it 24-Oct-2008 or 23-Oct-2008?

This is a problem that I have had arguments about in the past, and I bet if you ask 10 different Data Warehouse experts 5 will give you one answer and 5 will give you another. In the main I do not think it matters too much as long as you are consistent, except when using Microsoft SQL Server as your RDBMS (at least pre 2008) or when you are loading more frequently than once per day.

What Kimball Say

Kimball tell you to switch the row like below:

Products_ID	Product_Key	Product_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Lettuce	0.99	0	01-Jan-1900 00:00:00	23-Oct-2008 00:00:00
2	1234	Lettuce	1.3	1	24-Oct-2008 00:00:00	31-Dec-9999 00:00:00

So the RowEnd date is the load date (remember we are loading 24-Oct-2008) –1 day.

If we had a simple Sales fact when we do lookups loading our facts we can either do it like follows:

   1: SELECT

   2:      p.Products_ID

   3: FROM conform.Fact_Sales f

   4: LEFT OUTER JOIN conform.Dim_Products p

   5: ON

   6:     f.Product_Key = p.Product_Key

   7: AND f.Date >= p.RowStartDate

   8: AND f.Date <= p.RowEndDate

   1: SELECT

   2:      p.Products_ID

   3: FROM conform.Fact_Sales f

   4: LEFT OUTER JOIN conform.Dim_Products p

   5: ON

   6:     f.Product_Key = p.Product_Key

   7: AND f.Date BETWEEN p.RowStartDate AND p.RowEndDate

Why is it an Issue?

It is often common practice within a transactional system to mark dates something occurred as the current system Datetime (especially when using SQL Server i.e. GetDate())

Pre SQL Server 2008 there is no Date type so all dates are time specific also. So if we consider a fact table feed like this:

Transaction	Date	Product_Key	Qty
1	23-Oct-2008 00:00:00	1234	1
2	23-Oct-2008 09:00:00	1234	1
3	24-Oct-2008 00:00:00	1234	1

Using the the Kimball approach the data we receive in the join will be:

Transaction	Products_ID
1	1
2	NULL
3	2

Uh oh!! We are missing a product ID! This is because Transaction 2 happened at 9am (not exactly midnight) and due to the RowStart and RowEnd dates being accurate to the second (smalldatetime) or even 1000th of a second (datetime) we miss the join. To get round this you could strip the Fact date to just date (force it to be 00:00:00) but this can be expensive, especially when doing this for millions of fact rows and when there is an alternative way.

The Solution

Instead of using the load day –1 we use the load day as the RowEndDate so our Lettuce now looks like this:

Products_ID	Product_Key	Product_Name	RRP	RowIsCurrent	RowStartDate	RowEndDate
1	1234	Lettuce	0.99	0	01-Jan-1900 00:00:00	24-Oct-2008 00:00:00
2	1234	Lettuce	1.3	1	24-Oct-2008 00:00:00	31-Dec-9999 00:00:00

We now change our join very slightly to the below:

   1: SELECT

   2:      p.Products_ID

   3: FROM conform.Fact_Sales f

   4: LEFT OUTER JOIN conform.Dim_Products p

   5: ON

   6:     f.Product_Key = p.Product_Key

   7: AND f.Date >= p.RowStartDate

   8: AND f.Date <  p.RowEndDate

So we only join on rows less than but not equal to the RowEndDate. So now we get these values:

Transaction	Products_ID
1	1
2	1
3	2

Yay!! we are not missing any Product_IDs now, and all is happy.

This approach has saved expensive date conversions when they are not necessary. As long as the RowEndDate of the previous row is the same as the RowStartDate of the next row then all is happy. This is my standard approach and it is also an approach that will work if you need to move to real time processing (changes occur multiple times a day).

I hope you can see how this approach ticks more of the boxes in issue resolution. Let me know about your thoughts and how you have tackled this problem in the past.

Essbase API Load Data - Error 1042015 - Network error: Cannot Locate Connect Information For [%s]

JakeSmillie — Fri, 15 Aug 2008 10:06:41 GMT

Today I was trying to load data into Essbase v7.1.5 using the VB API command EsbImport.

The Essbase API documentation is great and gives you very useful code examples. The example they give for EsbImport is defined below:

Declare Function EsbImport Lib "ESBAPIN" (ByVal hCtx As Long, Rules As ESB_OBJDEF_T,

                                          Data As ESB_OBJDEF_T, User As ESB_MBRUSER_T,

                                          ByVal ErrName As String, ByVal AbortOnError As Integer)

                                          As Long

Sub ESB_Import ()

   Dim sts          As Long

   Dim Rules        As ESB_OBJDEF_T

   Dim Data         As ESB_OBJDEF_T

   Dim User         As ESB_MBRUSER_T

   Dim ErrorName    As String

   Dim AbortOnError As Integer

   '*********************************

   ' Rules file resides at the server

   '*********************************

   Rules.hCtx     = hCtx

   Rules.Type     = ESB_OBJTYPE_RULES

   Rules.AppName  = "Demo"

   Rules.DbName   = "Basic"

   Rules.FileName = "Test"

   '********************************

   ' Data file resides at the server

   '********************************

   Data.hCtx      = hCtx

   Data.Type      = ESB_OBJTYPE_TEXT

   Data.AppName   = "Demo"

   Data.DbName    = "Basic"

   Data.FileName  = "Data"

   '********************************

   ' Specify file to redirect errors

   ' to if any

   '********************************

   ErrorName      = "IMPORT.ERR"

   '*************************

   ' Abort on the first error

   '*************************

   AbortOnError   = ESB_YES

   '*******

   ' Import

   '*******

   sts            = EsbImport (hCtx, Rules, Data, User, ErrorName, AbortOnError)

 End Sub

This code however is incomplete and will generate an Essbase error 1042015 - Network error: Cannot Locate Connect Information For [%s].

The problem is that the database you have selected to import data into is not active. Therefore before the EsbImport command is called, you need to call EsbSetActive, like below

Dim pAccess AS Integer

sts = EsbSetActive (hCtx, "Demo", "Basic", pAccess)

This will fix your error 1042015 woes.

Data Warehousing - ETL Dealing With Large Dimensions in SSIS

JakeSmillie — Fri, 20 Jun 2008 16:08:00 GMT

Introduction

This is the first part of a 2 part blog that outlines techniques in dealing with very large dimensions (or small ones for that matter) when going through your ETL (Extract, Transform & Load) process in your data warehouse using Microsoft SSIS (SQL Server Integration Services) as your ETL tool.

The Problem

- You have designed your data warehouse and you are left with a very large and complex dimension. (When I say large and complex I mean a million plus rows and 10 plus data sources, but the process works on any size of dimension)

- You still need to meet your ETL time requirements and it would not be plausible to process the full dimension for each ETL run.

For the purpose of this blog I have simplified the problem to a small simple dimension.

Here is a Entity Relationship diagram of the source for our dimension.

This is a very simplified Product dimension and the end result we want is:

Solution

To start I would like to caveat this with the fact that this is by no means the only and definitive way but a way that has worked for me and works well.

All ETL processed go through 4 main steps:

Extract - Extract the data from source systems

Clean - Clean the data for your data warehouse.

Conform - Change the data into your data warehouse format

Load - Load it into your data warehouse

Extract

Firstly we need to Extract the 3 source tables (Product, Product_Pricing, Product_Hierarchy). We only want to extract changes to these tables since the last load as we do not want to process the whole dimension each time. There are many ways that this can be done and is not part of the scope of this blog.

Clean

In clean we keep a current snapshot of the source table and mark any rows that change to 'changed'. For example if a department (1, Produce) in Product_Hierarchy changes to (1, Food & Produce) we change that row and mark it as changed.

Here is the sample data I am using in the clean area.

Product_Hierarchy

Department	Department_Name
1	Produce
2	Grocery
3	BWS
4	Technology

Products

Product_Key	Product_Name	Department
1234	Lettuce	2
1235	Potato	2
1236	Tomato	2
2341	Crisps	1
2342	Biscuits	1
3241	DVD	4
3242	CD	4
4321	Fosters	3
4322	Stella	3
4323	Pinot Grigio	3

Product_Pricing

Product_Key	RRP
1234	0.99
1235	0.89
1236	2.99
2341	1.89
2342	1.47
3241	3.57
3242	2.54
4321	1.50
4322	4.20
4323	2.30

So this is the data we have in our clean tables. We now need to merge these tables into one that would be used as out source dimension to conform.

This is the SQL used:

SELECT

       p.[Product_Key]        as [Source_Product_Key]

      ,p.[Product_Name]        as [Source_Product_Name]

      ,p.[Department]        as [Source_Department]

      ,h.[Department_Name]    as [Source_Department_Name]

      ,price.[RRP]            as [Source_RRP]

FROM [clean].[Products] p -- this is the driving table

LEFT OUTER JOIN [clean].[Product_Pricing] price

ON

    p.[Product_Key] = price.[Product_Key]

LEFT OUTER JOIN [Clean].[Product_Hierarchy] h

ON

    p.[Department] = h.[Department]

WHERE

    p.[Changed] = 1 -- driving table has changed

OR    price.[Changed] = 1 -- the price  has changed

OR    h.[Changed] = 1 -- the department has changed

This merges the the tables and makes sure we get all changes (by using the OR Changed flags). One of the problems you get with multiple sources is that if the Department_Name in Product_Hierarchy changes it could change more than one row in the Dimension (so you can't just use your driving product table to track changes).

For example, if (1, Produce) changes to (1, Food & Produce) as previously mentioned we need to make sure Products 2341 and 2342 are included to conform. Holding the full snapshot of your relational data in your staging area allows you to get ALL changes and only load changes (not the whole dimension). This approach can save many minutes on large dimensions.

The above solution does require the storage space in your staging database but this is a small price to pay for time saved.

Part 2 of this blog will explain how to flow this through a conform stage in SSIS. You can download the sample database I have used to have a play for yourself below

DataWarehouse_Play.zip