-
1. Re: How to join two fact table with a common dimension at different granularity
Jonathan Drummey
Oct 17, 2012 10:55 AM
1 of 1 people found this helpfulHi,
Depending on how you are setting up your view, you might be able to use an aggregate like MIN() or ATTR() on the monthly target when SUM(Daily Sales) gets you that total.
If that doesn't work, then you may need to use a table calculation to accomplish your aggregation, in that case posting a packaged workbook (.twbx) with some sample data would be good.
Jonathan
-
2. Re: How to join two fact table with a common dimension at different granularity
Hi Jonathan,
Thank you for your prompt answer. The MIN() and ATTR() are helpful at the first place, however, it is not working once I try to do some aggregate on those monthly target.
For example, the target # is stored at per product per month, and I could use ATTR() or MIN() to get the correct number displayed at a month/product pivot table. However, when I try to get the total target number for each month, across products, the function didn't give me the number I want.
How to do monthly target number in this case ?
Thank you
-
3. Re: How to join two fact table with a common dimension at different granularity
Hey Ken -
Another approach you could take is to simply NOT join all three tables together. Instead:
- Join your date dimension and daily sales fact table together in one data source
- Bring in your monthly goal fact table in as a second, distinct data source
- Use Data Blending to marry the two together.
I've attached a sample, including the 3 tables (in an Excel worksheet) that I created to drive the sample....
-
4. Re: How to join two fact table with a common dimension at different granularity
Chris,
That is AWESOME. It works and the key is to set the level of details to Monthly.
BTW,
I want to make the same mark/bar chart like what you did in you sample, but ended up with this below. How to get to that chart from here.
Thank you
