What should be my Date range in the dCalendar table?

[mrxlsx]

Hello all,

This is a cross-post from Chandorg where it attracted almost nobody.

This is a PowerPivot question.

I have Fact table and couple of dim tables. In my Fact table I have my sales DATE range from 01-Jan-2008 TO 31-Dec-2014 in the OrderDate column.

Where as in my ShipDate column this range starts from 01-Jan-2008 TO anywhere 80+/- days +31-Dec-2014, where as another dim Table's Date column starts from 30+/-days of 01-Jan-2008 TO 180+/- days of 31-Dec-2014.

What should be my dCalendar table's date range start from and end till??

Thanks

 

[scottsen]

I generally think of it as "the range of dates I care about" -- meaning, that I am likely to put on rows of my pivot, or columns of my chart over time, or whatever. Safe would be "the min date you see, through the max date you see" in your fact table.

 

[mrxlsx]

Hi scottsen,

Thanks for replying. But in general, OrderDate and ShipDate may not be the same always. If I want to analyze on ShipDate (which is "n" days +/- of OrderDate), you need to have them in your source table, right? So you have to consider the whole date range that you can see in your source tables. Otherwise whenever you need to analyze, you will miss the same if you don't have.

Anyways I got the point. Thanks a ton, man.

 

[scottsen]

Right I am saying... your date table should cover from the smallest of ANY date, through the max of ANY date. But... really, it's more based on what you want to report on. Maybe your first sale was Apr 1, but you want your report to start on Jan 1... you would want Jan 1 in your calendar table. Or, maybe you have 10 years of sales, but you know you only care about the most recent 3 years... in which case, your calendar table only needs 3 years of data. "it depends" -- the answer of every consultant, always and everywhere

 

[mrxlsx]

Scott,

Thank you for the reply ,

One more question. Can I have formulas in my Fact and dim tables between table to table? or I shall have only pure values?

mrxlsx

 

[scottsen]

Once your data is in Power Pivot, you are going to have two choices for "formulas". See Calculated Fields vs Calculated Columns | Tiny Lizard

And those formulas can certainly span across tables, though, I'm not 100% sure I cam getting your question.

 

[mrxlsx]

Scottsen,

I am trying to know that whether we can have formulas in the Source tables (When I say source tables, be it CalendarTable, FactSales, Customers, ProdCategories, SalesmanKey etc) which we import into PowerPivot.

See this scenario:

If a particular customer wants to change his Mode of shipping from Airways to Railways or he wants to change one of his Products ordered, we change in our source tables because we cannot change anything in the PowerPivot once we pull the data into the same, right?

So if we have formulas in our source tables, we change some thing in a table that reflects in other table say, Freight for Airways is 9%, Railways 4%, Roadways is 6%.

Can I use this flexibility in my data or not? The reason why I am asking this is I doubt that there are formulas already in the source tables, Will they be in conflict with the formulas in the PowerPivot? Will PowerPivot allow us that flexibility of having formulas in the source tables or they should be pure values for the analysis purpose?

I think your answer may differ now.

 

[TimRodman]

I think you might want to run your data through Power Query when bringing it into Power Pivot if you want to add a lot of formulas. Power Query allows you to add some pretty powerful calculations.

 

[mrxlsx]

Hi Tim,

Thanks for the news, I use Power Pivot with Office 2010. But Power Query is available ONLY with Office 2013, right?

I need to check this what level of functionality it offers in terms of using formulas.

 

[TimRodman]

I used Power Query when I was on 2010 (just Google for the download link), but there was a trick to get it to feed into Power Pivot that someone showed me (it might have been Scott). I'll see if I can find it.