I am trying to join a Calendar Table to several fact tables by week, month and year. The fact tables are simple…weeks are 1-52, months are 1-12 and years are 2006 - 2009. Both the calendar table and the fact tables have multiple records for weeks, months, are years, so there is no 1-to-many, but rather a many-to-many relationship.
Ultimately, I want to be able to query data by week, month and/or year across all fact tables, as well as, create time objects that references current date for time objects such as current week, current month, etc.
I am a Universe Designer novice and a first time “poster”. Kindly respond with pointers on how best to communicate via post and/or seek help.
First of all you need to make a small change in your Datamodel… as a may to many relation between tables will not give you desired results.
For this you need to add a table in between your fact table and the calendar table. this table will have only unique records. due to this your many to many relationship will get converted to many to one and one to many giving you the correct result.
This concept of adding a table to change the cardinality between the dimension table and the fact table is known as “BRIDGE TABLE”
You can achieve by means of derived tables in universe… but thats not a good practice. therefore … either you create a table at database level or a materialized view at database…
Read more about this… and as per your requirement design the new table. this will provide you a robust Datamodel.
Please disregard shiva’s post for now until we can clarify something on your calendar table.
Week 1, 2009. How many rows in your calendar table contain that?
That is, what level is your calendar table at?
Is it a daily or weekly calendar table?
That’s exactly my point. While not dismissing Shiva’s suggestion just yet, I think we need to see what the state of the calendar table is and what the join fields between calendar and fact are.
Wow…Thanks for the feedback. I’ll try to provide some more detail.
First, there are 5 fact tables with both year and month dimensions. The month dimension is the smallest time period unit in 4 of the 5 tables. The last has week, but are not compatible with the month tables. There are currently only 3 years of data (and building). As I stated previously, months are 1-12 and year is 2007-2009 (not 2006 yet, but may build history later). There are multiple products within multiple categories so both months and years are multiplied in the fact tables.
Now, the calendar table we created is by day (date). So, that means 365 days per year with a corresponding month, fiscal month, year, fiscal year, etc. as other fields. The Calendar Table is build out through 2015. The objective is to create current month (and year) dimensions that simply reference the calendar table based on today’s date. That way, all my monthly reports are automatically updated based on the date. I envisioned doing this with a formula like “case when sysdate = cal_date then fiscal_month end”. So the formula matches the current date (sysdate) with a date on the calendar table and simply finds the fiscal month on that record.
I’m not sure if this the best way or most efficient way to go, but it just seemed straight forward and logical.
Per Shiva’s note, I previously created a Month Table (and a separate Year Table), which was a simple (12 records, 1-12) and joined to all fact tables. This worked OK (a little slow), but created over 500 loops, which I addressed with contexts. My universe looked like a crazy spiderweb. Like I said, it worked, but just seemed inefficient.
(BOB member since 2007-12-11)