Disclaimer

All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Tuesday, February 15, 2011

How to join 2 tables conditionally

In a project, more often than none that it is desired to join a dimension table to a fact table based on certain conditions instead of a direct join.

For example, I have a time dimension table that keeps two calendar codes and we only want the data that its calendar code is '001' to be matching with the data in the fact table, otherwise for certain product or location, it's measure would be counting both calendar code.

Now before we proceed, let's look at another option of doing this. We can apply a filter on the time dimension table to filter out all the records that it's calendar code is '001' and then join this table to the fact. Note that if done this way, then the filter will be applied to this dimension and will not be changed. So what if this time dimension is a part of a conformed dimension that joins to two fact tables that one fact that does need both calendar code to be counted? Therefore, we will see if we can change the join logic between the time dimension and the fact table.

So let's look at the below data model:


So let's change the join between Retail Sales Calendar Time and A_RTL_Sales_Audit_AGG

Instead of using a foreign key join, let's use a complex join in physical layer (Yes, I just broke the best practice rule again!):



In the expression builder, enter the following code:
CASE WHEN SUBSTRING("Retail Sales Calendar Time".RETAIL_YEAR_WEEK_TYPE_CD FROM 7 FOR 3) = '001' THEN "Retail Sales Calendar Time".RETAIL_WEEK_ENDING_DATE END = A_RTL_SALES_AUDIT_AGG.CW_ENDING_DATE

Here what I am doing is looking up the column "Retail Sales Calendar Time".RETAIL_YEAR_WEEK_TYPE_CD and if the last 3 digits of that column is '001', then use the column "Retail Sales Calendar Time".RETAIL_WEEK_ENDING_DATE as the joining column, otherwise the join will not return any matching data.

Let's run some test:



And the resulting SQL generated by this query:


However, if the report is using measures from another fact table that doesn't want this condition to be applied:



Then the SQL generated as a result will not have the above condition applied to the query:



Thanks
Until next time!

2 comments:

Anonymous said...

nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanx a ton once again, Regards, obiee training in hyderebad

sauddocuments said...

do u give training also

Related Posts Plugin for WordPress, Blogger...