The Ultimate Date Table

 
image

“Looks like it’s time for me to get myself a date.”

-Ace Ventura, PowerPivot Detective

The Importance of a Date/Calendar Table

I get a lot of questions from people who are struggling with the time intelligence functions in DAX.  And nine times out of ten, the answer is that they don’t have a proper date table.

I know it’s tempting.  You’ve got your sales table, and hey, there’s a Date column in there!  So you use it, and pass that column as a parameter to, say, DATESBETWEEN, or DATEADD.

Sometimes that will give you an error.  And other times, it won’t…  but the results will be funky.

You need a separate Dates table, or perhaps you prefer to call it a Calendar table.  A separate table, whose only purpose is to store dates (and the properties of dates, like DayOfWeek, etc.)  And it contains consecutive dates – no “gaps.”  Even if your business is never open on weekends, you need unbroken ranges of dates.

Oh, and then you need to relate it to your Sales table.  (Or whatever fact/measure tables you have).

Much More Than a Single Column

A single-column table that contains merely dates is enough to make the time intelligence DAX functions operate smoothly.  But you will almost certainly want other fields too.  Like Year.  MonthName.  DayOfWeek.  The list goes on.

Maybe something like this:

image

And yes, you can cobble this together on your own in Excel.  Tedious work though.

Would You Like One for Free?  Try DateStream from Boyan Penev!

Imagine just being able to open up PowerPivot and always having three nice date tables awaiting import:

image

That’s what Boyan Penev has put together for you.  Three great calendar tables that you can download directly into PowerPivot, for free.

He published them to Azure DataMarket, a service from Microsoft where data providers can actually sell you their data sets – things like weather, demographics, etc.

Boyan did this for free though – I suspect half as a service to the community, and half as a project to learn how to provide a service on DataMarket.

It’s pretty damn cool, and really, the story should end there.  If you’ve used DataMarket before, then it DOES end there.  Go get the date tables and try them out.

But if this is your first exposure to DataMarket, it takes a few minutes to get it set up.  It’s not bad as long as you don’t make the mistakes I did.

How To Get It – Short Version

Hey, it’s on Azure DataMarket.  The URL is in the next section below, or you can just go to Azure DataMarket and search on “DateStream.”

DataMarket is going to be a wonderful service someday, but right now it has a few warts, so there is a Long Version too.

How To Get It – Long Version with Occasional Snarky Commentary

Step 1:  Go to the DateStream page on DataMarket.

image

Step 2:  Get confused.  OK, now is where things get choppy, because frankly, the DataMarket site itself has a terrible user interface.  I sent a full page of feedback to the DataMarket team about a month ago and as far as I can tell, they ignored it.  (Which is pure karma – I used to be one of the people at MS who ignored 90% of the feedback coming in, and now I get to be the one who is ignored).

I don’t want this to be a tutorial on how to navigate their website, or even how NOT to design a website.  So let’s just hit the highlights and try to get to Boyan’s date tables as soon as we can.

Step 3:  Get an account.  OK, this isn’t bad.  Another MS site that requires a Live ID.  Most of us have three of those by now.

Step 4a:  Scan the DateStream page looking for the “Download to PowerPivot” button or link.

Yeah that’s right.  There is no such link – you can get to one by navigating a few levels deeper but I’m going to skip that.  Don’t despair though, good things await you!

Step 4:  Find the URL of the DateStream Service

This is NOT the same as the URL of the DateStream page.  But it IS displayed on the page.  Here’s the URL you need:

https://api.datamarket.azure.com/BoyanPenev/DateStream/v1/

OK, copy that.  You will need it.

Step 5:  Launch PowerPivot, Go Into the PowerPivot Window

And click this button:

 

image

If you don’t have that button, you need a newer version of PowerPivot.  Go get that from PowerPivot.com and resume the next step.

Step 6:  Fill in the Dataset URL From Step 5

image

Step 7:  Account Key

See that last text box in the picture above?  The one with the long code in it that I’ve partly blurred?  That’s my account key.  I highly recommend clicking that Find button.  It’s actually pretty damn useful.

Be careful – the DataMarket site has TWO long nasty codes like that for you.  One of them is the one you want, and the Find button takes you to that one:

image

THIS is Your Account Key

Do NOT, under any circumstances, do what I did, and confuse Account Key with Customer ID:

 

image

This is NOT Your Account Key.
Do NOT Be Tempted to Use This!

Step 8:  Click Next, and Pick Your Table or Tables

image

NOW we are on familiar ground.

Last Note:  Parameterization?

One thing I have not yet figured out is how to limit the date range I import.  The table starts in the year 1900, which goes back a bit far for my needs, and makes the dataset take a long time to download.

You’ll notice that when importing from DataMarket, the Preview and Filter UI lacks the filter dropdown buttons:

image

No Filter Dropdowns, Just Checkboxes

But the DateStream homepage DOES indicated that parameterization is possible:

image

So if you’ve got that figured out, drop me a note Smile

33 Responses to The Ultimate Date Table

  1. Dan English says:

    Boyan has a post on how to parameterize the URL to get a range of dates. You can check it out here and he has quite a few examples based on what you are trying to do – http://www.bp-msbi.com/2011/10/range-queries-with-azure-datamarket-feeds/.

  2. Boyan Penev says:

    Thanks for the post Rob!

    First, you can filter the date range. Have a look at:

    http://www.bp-msbi.com/2011/10/range-queries-with-azure-datamarket-feeds/

    It’s very fiddly, but it works. And..I got an email from a DateStream user telling me they implemented the range querying and it works for them, so give it a quick go. Importing less than the whole feed of 2 centuries of data is much faster, too.

    Also, there are four more localised tables coming – in Hebrew, Danish, German and Bulgarian. Please let me know if any of your readers would like to participate and translate the feed in other languages as well. The goal is to have the whole world using it, after all :)

    • timrodman says:

      Boyan,

      Thanks for this table. Also, thanks for the tip about filtering the date range. I discovered something in the marketplace (maybe it’s new), that allows you to more easily create the hyperlink that you want.

      1. Go here:
      https://datamarket.azure.com/browse/data?query=datestream

      2. If you have subscribed to the feed using the “SIGN UP” button mentioned in the other comments on this blog, you should see three words to the right of the feed: “data”, “subscribed”, and “use”. Click on the word “use”.

      3. The available tables are now listed on the bottom of the screen and you can filter on the columns or even exclude certain columns if you don’t want them. Just use the down arrows next to each column name.

      4. After you set your filters and column exclusions, the URL is automatically created for you in the area above the table. You can use this URL in the “Data Feed Url” field when you bring the feed into Power Pivot. Your data will be filtered and columns will be excluded.

      5. To combine filters on the same column, like a year is “greater than or equal to” and “less than or equal to”, you still need to dissect the URL a little bit. Just put a %20and%20 in between the filter criteria.

      This is the same method that you mention here:
      http://www.bp-msbi.com/2011/10/range-queries-with-azure-datamarket-feeds/

      It’s just a nicer graphical way to accomplish it.

      Tim

  3. Ben Niebuhr says:

    I took the advice to use a dimDATE table right away, but in my field, we work with hourly granularity. I have had some trouble with getting some of the calculations to return as I expect. I freely admit that it is probably user error, as I only started using PowerPivot a few months ago.

    Does anyone have any tips for working with sub-daily data. I have been using an hourly date table, complete with all the applicable descriptions. I have especially had trouble with the fact that my database is in GMT, but I would like to have everything totaled in EST (it is a 24hr data stream).

    Any help would be appreciated. And thank you for offering such a rich resource in this blog. I read every post.

  4. Max Uritsky says:

    Ouch, that UI comment hurts. Seriously enough, could you resend your UI feedback to datamarket@microsoft.com?

    I saw at least some part of it on the MVP group list, but I want to make sure we get the full one.

    We are working on improving some of the user workflow and performance of PowerPivot and DataMarket integration, so expect some thing to change soon… The are two good points to note: it actually works and we actually listen :)

  5. jakob says:

    After selecting BasicCalendarUS and pressing the Finish i got the following error:

    “The remote server returned an error: (403) Forbidden.”

    What does that mean?

    Kkv. Jakob

  6. Sven says:

    Same error for me. Anybody knows how to fix that?

  7. Gjor says:

    When i try to download the BasicCalendarEnglish published by Datestream , I’m getting the following error “BasicCalendarEnglish: The remote server returned an error: (403) Forbidden”. The publishers is telling me that there are not problems at his end.

    Any help will be greatly appreciated.

    Regards,

    Gjor

  8. Adam says:

    When trying to connect, I get the following error:
    “The remote server returned an error: (407) Proxy Authentication Required”.

    I’m guessing that this is because the company I work for has some sort of network security settings preventing me from connecting.

    Does any of you have a work-around, or an alternative way of “creating” a dynamic date master table?

  9. Idan Cohen says:

    I am getting : “BasicCalendarEnglish: The remote server returned an error: (401) Unauthorized.”

    i already managed to connect from another powerpivot workbook with the same account.

    Does any one have a solution ?

    Thanks

  10. Neil says:

    This is my error: Cannot connect to the specified feed. Verify the connection and try again. Reason: The remote server returned an error: (407) Proxy Authentication Required.

    • Paul says:

      Hi Neil,
      I have the same problem, did you find a solution?

      • Kyle Hale says:

        Late to the party here, but the solution is to (no joke) create a file called excel.exe.config in the folder where your EXCEL.EXE file sits. It needs the following contents in it

        Restart Excel and you’re good to go.

        • Kyle Hale says:

          Sorry, WordPress (probably rightfully so) removed the contents, here it is except I removed the opening tags from each line, so add those back in:

          ?xml version=”1.0″ encoding=”utf-8″ ?>
          configuration>
          system.net>
          defaultProxy useDefaultCredentials=”true”>
          proxy usesystemdefault=”true”/>
          /defaultProxy>
          /system.net>
          /configuration>

          • Young Salsa says:

            6/4/2014 Update. I can confirm the excel.exe.config file worked. I just copied a file from where excel.exe sits which is here. C:\Program Files\Microsoft Office\Office14 and just copied a file in there and named it noted above. Now, I am ready to blow some people minds!

  11. gceker@hotmail.com says:

    Same error for 403 forbidden like everyone else. Any ideas ?

  12. powerpivotpro says:

    I’ve asked Boyan to check in on this thread, he may have an answer.

  13. John H says:

    The API URL listed on the blog post is no longer correct — make sure to use the one listed on the Azure site.

  14. Will B says:

    I got the 403 Forbidden error. After clicking “sign up” though on the Azure page for DateStream (it’s $0.00/mo) it let me access fine. It seems your account key is used to validate access to streams you have signed up for… Pretty confusing!

  15. powerpivotpro says:

    Everyone on this comment thread to date who has been struggling with errors has now been connected with Microsoft to see what the problem is.

    Hopefully someone will report back on what they find out :)

  16. kcsteven says:

    I’m seeing the 403 error as well.

  17. PedroCZ says:

    Guys, any updates on this. Do you have any alternatives?

  18. For the 403 error, this solved it for me: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cd5c7877-b7e4-4041-a1d6-feaa33e5e74e/powerpivot-datamarket-place-and-proxy-authentication

    Make an excel.exe.config file with below code, and put it in the same directory as excel.exe

  19. mandeep says:

    if you get the 403 it means that you have not subscribed to the data feed. That is the step you are missing

  20. Santi Azpilicueta says:

    Hi Rob,

    Thanks for the great info you provide… I love these tables, except they are huge! I finally figured out how to get a filtered feed (even with the new changes to the Excel 2013).

    The trick is to edit the Azure connection, select advanced, and go to “Inline Service document”, where you will add the filter to each of the URLs in the form of a query string… Note: I copy the very long string and work with it in a Notepad(++)…

    An example filter from 2010 to 2030 will look like this:

    ?$filter=YearKey%20ge%202010%20and%20YearKey%20le%202030

    So a partial section of that line could look like this for example:

    For more detailed information look at this Boyan Penev post: http://www.bp-msbi.com/2011/10/range-queries-with-azure-datamarket-feeds/

    I hope this is of value for someone. It took me some time to figure it out for 2013!

  21. Eloi Baixeras says:

    Sorry but I couldn’t download your table, I get on the internet a message with error 403 error.

    Could you help me?

  22. serky says:

    Thanks for this very useful blog. I successfully connected to Azure from Power Pivot and downloaded the DateStream table but had one small problem. I used timrodman instructions from July 26, 2013 at 4:09 pm to filter the data to year >=2000. This worked perfectly in Azure but Power Pivot downloaded all the data ie – from 1900. Eventually I had to save the filtered date set as a csv and load to Power Point separately. So my questions are:
    1. how do I get Power Pivot to recognize the filtered data in Azure?
    2. how do I get Azure to save my filter instructions so I don’t have to re-enter the filter each time I log in to Azure?
    Thanks

  23. I signed up for an account and subscribed plus I created the excel.exe.config file as described above and still nothing works. In fact, I had to go back and delete that new config file because I couldn’t open Excel after that. Still just get an ‘unauthorized’ error when trying to get the date files. I would still like to use them though if there are any other suggestions.

  24. Never mind….the azure marketplace link may not work in PowerPivot but you can filter and download the file, which is what I did.

Leave a Comment or Question