THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Separate date and time in #powerpivot (and #bism tabular)

In PowerPivot you can import data from several sources and it is often the case that you import a DateTime column from a database. From an analytical point of view, you usually make analysis over dates (day/month/year/quarter/week) and over time (hour/minute) but to do that it is often useful to have two independent set of attributes: one for the date (the Calendar table) and one for time (for those businesses where this element is relevant). When your table is not too big, you might import the table with the original DateTime column and only then you can create derived calculated columns in DAX. However, when your table is big (i.e. millions of rows, or more), then it is a good idea to make the calculation outside PowerPivot.

For example: if you have 15 millions rows for one month, assuming that the datetime has a granularity of one second and you have a linear distribution over 24 hours, you will have a dictionary of about 2.5 millions of unique values for just this column. This makes that column pretty expensive. Now, when you split the column in SQL, you might save a lot of space. If you have SQL Server 2008 R2, you can use the following syntax in a SQL query (or a SQL view) in order to make the split in a smart way.

This if for the date:
CAST( DateColumnName AS DATE)

PowerPivot still reads the column as a DateTime, but hour/minute/seconds disappears and the number of unique values is reduced to the number of distinct days in your data. Making it easier to join with a Calendar table, of course!

This is for the time rounding to seconds:

Again, PowerPivot read this as a DateTime and put a date anyway (the current day when you import data by using the formula above) – however, you probably will derive hour and minute in calculated columns (but doing that in SQL is a good idea, too).

In case you want to round the time to the minute instead of seconds, you can leverage on SMALLDATETIME data type by using this cast:

Using this technique I saved a 15 million rows table into an Excel workbook of 66 Mb, using no more than 400Mb or RAM once loaded in memory. Remember, columns with unique values for each row are the most expensive ones in PowerPivot!

Published Thursday, September 1, 2011 12:24 PM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Tim said:

I have this exact problem with millions of rows from an imported column from a database.  I cannot get the CAST(DateColumnName AS DATE) function to work.  Any other suggestions.

October 6, 2017 1:54 PM

Marco Russo (SQLBI) said:

Probably you have a database other than SQL Server. You should look at the documentation of the SQL language of your database to understand how to strip out the time part from datetime.

October 12, 2017 6:55 AM

Tim said:

No it's a SQL service it says in my connection name. Here's the code i'm trying to strip out the time stamp.  [dbo].[INV TRANSACTIONS FACT 2017].[TRAN DATE].  This resides inside of a select statement. Thanks for you help.

October 18, 2017 12:15 PM

Marco Russo (SQLBI) said:

Unless you have a very old version of SQL Server, a view or a query in SQL with the expression


should work.

October 20, 2017 8:28 AM

Tim said:

Hi Marco,  Sorry for the slow response, I've been out of commission preparing for quarterly review.  We probably do have an old SQL version but haven't been able to identify it as of yet.  I've updated the above line of code with cast and as date and now get an error message "Type DATE is not a defined system type" when validating the query editor.  Any other insight?  Thanks for you help.

October 30, 2017 2:45 PM

Marco Russo (SQLBI) said:

You might have a very old version of SQL.

You might try:


November 4, 2017 9:16 PM

Tim said:

I was hoping that would be the solution but to no avail.  I now receive the error message "The SQL statement is not valid. A column name cannot be blank.". Again thanks for your input.

November 6, 2017 5:59 AM

Marco Russo (SQLBI) said:

Are you sure that you are using Microsoft SQL Server?

November 13, 2017 2:15 AM

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement