Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Format Durations in SQL Server Analysis Services


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

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)?

Solution

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
 CONSTRAINT [PK_DimRepresentative] PRIMARY KEY CLUSTERED 
 (
  SK_Representative ASC
 )
);
GO

INSERT INTO dbo.DimRepresentative(SK_Representative,Name)
VALUES  (1,'Bruce')
  ,(2,'Selena')
  ,(3,'Gordon')
  ,(4,'Harvey');

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)
SELECT
  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
AS
(
SELECT
  SK_CustomerServiceFact
 ,SK_Representative
 ,TicketNumber
 ,TicketTimestamp
 ,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
);
GO

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")
,VISIBLE = 1
,ASSOCIATED_MEASURE_GROUP = 'Customer Service';

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, May 07, 2015 - 3:14:27 PM - Miguel Back To Top

Hi,

 

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 07, 2015 - 7:29:09 AM - Koen Verbeeck Back To Top

Hi Miguel,

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


Wednesday, May 06, 2015 - 1:38:08 PM - Miguel Back To Top

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

Hi,

 

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:

Case

       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")

       Else

             null

End

 

Thansk for your post, I learned a lot.


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

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

Hi,

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.

Att.,


Learn more about SQL Server tools