Calendar table into Dataflow

[rdnguyen]

When you set up Calendar table as a Dataflow entity, would you do from 1900-01-01 to the current date to make it reuseable for any fact Tables? If so, how would you filter down that Calendar Dim to fit with each dataset for reporting purpose, say data is only 12 years only. 

Kindly advise!

Thank you,

Rich

[lbendlin]

That totally depends on your fact data. If for whatever reason your data goes back to 1900 then sure, do that.  But if your data only goes back to - for example - 1995 then you would waste a lot of resources by loading 35000 rows that you would never use in your time intelligence functions.

Yes, a calendar needs to be covering your fact date ranges. No, it doesn't need to extend beyond that.

[rdnguyen]

Hi Ibendlin,

I understand your suggestion. I am curious about setting up a single Calendar Dim dataflow that is usable for all possible fact tables that may come up later on. That means I am expecting to learn a way to make that Calendar Dataflow to dynamically filter down to the earliest and latest date presented in a fact table by the time I am joining dataflow together? Can I really do that to save time copy and paste the Calendar dim over each time I build fact dataflow?

[lbendlin]

A calendar table should not be very large, and it should not be slow.  In my opinion there are not enough reasons to use a dataflow for that.  Instead, use a BMT. Can be an Excel file in a Sharepoint, or a table in a SQL server.