Format Durations in SQL Server Analysis Services

By:   |   Comments (6)   |   Related: > Analysis Services Development


I have a fact table in my data warehouse that tracks durations of certain events as the total number of seconds that have passed. When users browse the cube, I’d like to show these durations formatted as time using the hh:mm:ss format (hours:minutes:seconds). How can I do this in Analysis Services (SSAS)?


First of all we need a use case to analyze. In this tip, we’ll look at different customer representatives that are assigned to service tickets. In a fact table, we track how long it took in seconds to resolve a ticket.

Track Duration in Seconds for a Fact Table

Let’s create the customer representative dimension and insert 4 representatives. The following script can be used:

CREATE TABLE dbo.DimRepresentative(
  SK_Representative INT NOT NULL
 ,Name    VARCHAR(50) NOT NULL
  SK_Representative ASC

INSERT INTO dbo.DimRepresentative(SK_Representative,Name)
VALUES  (1,'Bruce')

Next we need a fact table to track the durations.

CREATE TABLE dbo.FactCustomerService
 (SK_CustomerServiceFact INT IDENTITY(1,1) NOT NULL
 ,SK_Representative  INT NULL
 ,TicketNumber   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() -- degenerate dimension
 ,TicketTimestamp  DATETIME2(3) NOT NULL
 ,Duration    INT NOT NULL);

Let’s populate it with 100 random durations, randomly assigned to a representative and also randomly spread out over 100 days.

INSERT INTO dbo.FactCustomerService(SK_Representative,TicketTimestamp,Duration)
  SK_Representative = FLOOR(RAND()*4) + 1 -- random assign a duration to a customer representative
 ,TicketTimestamp = DATEADD(DAY,RAND()*100,'2014-01-01')
 ,Duration   = RAND()*100000;
GO 100

The RAND() function is used to generate pseudo-random numbers. The SSMS batch separator GO is followed by the number 100. This ensures the preceding batch is executed 100 times which results in 100 rows being inserted into the fact table.

As an interface to the SSAS cube, we are going to use a view on top of this fact table. This view has a column TicketMonth that will serve as a mini-time dimension, allowing us to analyze the duration on a monthly basis. An additional column MonthOrder will be used to make sure the months are sorted chronologically and not alphabetically (more on that in the next section). Finally, the duration column is divided by 86,400 (the number of seconds in a day). This gives us a time representation in decimal time.

CREATE VIEW dbo.CustomerServiceFact_Cube
 ,TicketMonth = CONCAT('2014, ',DATENAME(MONTH,TicketTimestamp)) -- concat is SQL 2012+ only
 ,MonthOrder  = MONTH(TicketTimestamp) -- used for sorting in the report/cube
 ,Duration  = Duration / 86400.0
FROM dbo.FactCustomerService

The following box plot illustrates the data we’ve just generated:

Box plot

Build the SSAS Cube

The first step is creating a data connection to the database where you’ve created the objects from the previous section. Next a data source view is created that contains the fact table (using the view) and the representative dimension. A connection is created between both tables on the key column SK_Representative.

Data source view

Let’s create the representative dimension next. Kick off the dimension wizard and choose an existing table. Take Sk_Representative as key column and Name as name column.

Creating the representative dimension

The final Representative dimension looks like this:

The Representative dimension

After processing you can take a look at the different representatives using the Browser. By default, SSAS adds an Unknown member.

The members of the Representative dimension

The time dimension is next. Start the dimension wizard and again choose an existing table. Choose the fact table and configure TicketMonth as both key and name column.

Step 1 of creating the Month dimension

In the next screen, deselect DimRepresentative as related table.

Step 2 of creating the Month dimension

Make sure you also select the Month Order column. It doesn’t need to be browsable.

Step 3 of creating the Month dimension

The final Month dimension looks like this:

The Month dimension

TicketMonth is sorted by Month Order by setting the OrderByAttribute property. To learn more about sorting in SSAS, take a look at the tip Sorting a dimension attribute by another dimension attribute in SQL Server Analysis Services. To verify the months and their sort order, you can process the dimension and take a look with the Browser.

The members of the Month dimension

As the last step, we need to create the cube and a measure group. Start the cube wizard and select the fact table as an existing table. In the next screen, we need to choose the measures. Pick Duration and the automatically generated count.

Step 1 in creating the cube

Select both dimensions to be included in the cube.

Step 2 in creating the cube

At the final step, name the cube CustomerService.

Step 3 in creating the cube

The created cube has the following structure:

The cube structure

The dimension usage is automatically configured by the wizard and normally no intervention is needed.

The dimension usage tab

The cube is now finished. After processing it, you can take a look at the data through the Browser.

Taking a look at the sample data

Formatting the time durations in SSAS

In the case that your durations are relatively short, you can simply format the measure directly using the FormatString property. All you have to do is set it to hh:mm:ss.

Setting the format string to hh:mm:ss

Unfortunately, you cannot verify the formatting in the cube browser since SQL Server 2012. It does work though in earlier versions of SQL Server.

Why Microsoft, whyyyyyy?

So if we want to check if the formatting actually works, we need to go to Excel. There’s an Analyze in Excel button in the cube browser that takes us directly to an Excel pivot table.

Browse the data in Excel instead

In Excel we get the following results:

The result with the regular format string. Days are not included.

The grand total clearly illustrates the problem with this formatting: days are not taken into account. Once you go over 24 hours, the formatting starts right back at 0 hours. Excel itself has a format string that deals with this issue: [h]:mm:ss. Unfortunately SSAS doesn’t recognize this alternative formatting. This means the proposed format string of hh:mm:ss can only suit our needs if the duration is never longer than 1 day.

If we need to deal with longer durations however, we have two options: either construct a custom format string using MDX or create a new measure that displays strings.

The first option is explained by the blog post Custom format string for “duration” using MDX by Davy Knuysen (blog | twitter). In this tip I’ll explain the second method to achieve our goal.

The following MDX code will create a new calculated measure that will display the duration the way we want it.

CREATE MEMBER CURRENTCUBE.[Measures].[DurationFormatted]
 AS   Cstr((Int([Measures].[Duration]) * 24)
    + CInt(FORMAT(CDate([Measures].[Duration]), "HH")) )
    + FORMAT(CDate([Measures].[Duration]), ":mm:ss")

Using some Visual Basic functions, we parse the decimal time number and format it to the format string we need. The advantage of this method is that the formatting can be verified in the cube browser, since we are not actually using formatting, but just displaying strings.

The final correct results, days included.

The downside is that we need to create an extra measure in the cube. The original duration measure can be hidden though if needed. The results look like this in Excel:

The final results in Excel
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, May 7, 2015 - 3:14:27 PM - Miguel Back To Top (37127)



We are looking at the data in excel and we want to sort duration descending. Our format for duration is dd"d" hh:mm:ss

So, if duration was 25 hours it shows 01d 01:00:00. How do I sort the duration colum in excel?

04d 3:43:20

01d 5:57:45

32d 7:26:24

should be sorted like this:

32d 7:26:24

04d 3:43:20

01d 5:57:45

Can we accomplish this in excel?


Thursday, May 7, 2015 - 7:29:09 AM - Koen Verbeeck Back To Top (37116)

Hi Miguel,

I'm sorry, but I don't really understand your question. Can you clarify?

Wednesday, May 6, 2015 - 1:38:08 PM - Miguel Back To Top (37108)

OK, so we reach yet another issue.

Durations are formatted as 00d 00:00:00, now, excel can order those measures.

So we have ti me span up and working. In a particular spreadsheet acessing the cube we have these values:

04d 13:43:20
01d 05:57:45
32d 17:26:24
05d 14:31:01
21d 09:33:15
03d 09:06:14
00d 04:52:21

Can we sort this?

Wednesday, April 29, 2015 - 8:46:56 AM - Miguel Back To Top (37064)



Our problemas was related to data type not being update on measure and we fixed it. Our solution was working perfectly

However we had a problem relateds to timespan being too big (months) so we moved to a new soloution as follows:


       When ([Measures].[Quotation Input Time Hide] / [Measures].[Quotation Count Hide] ) <> 0   Then 

              Format(Int(([Measures].[Quotation Input Time Hide] / [Measures].[Quotation Count Hide])/86400), "0#d ")

               + Format (TimeSerial(0, 0,  ([Measures].[Quotation Input Time Hide] / [Measures].[Quotation Count Hide])-

            (Int(([Measures].[Quotation Input Time Hide] / [Measures].[Quotation Count Hide])/86400) * 86400)), "HH:mm:ss")





Thansk for your post, I learned a lot.

Tuesday, April 28, 2015 - 3:54:03 PM - Koen Verbeeck Back To Top (37055)

Hi Miguel,

I tested it by putting all the durations in FactCustomerService to 0.5 seconds, which means that some data in the view looks like 0.000005787037, but I still get good formatting in the cube.

What exactly do you get?

Tuesday, April 28, 2015 - 1:30:07 PM - Miguel Back To Top (37053)


I am having problems whenever a duration is below 8 seconds. The division by zero returns a x.x05E number format and I get nothing right on cube.

Can you guess what I am doing wrong, I can give you more detail.


get free sql tips
agree to terms