DAX Duration Measure in Days, Hours, Minutes, Seconds

Credit where credit is due. This is an enhancement of the technique originally posted here by @konstantinos and @smoupre.

A client needed a duration expressed in this format: 2 days, 05:03:52

In other words, Whenever the duration exceeds 86,400 seconds, express that portion in days, then build the remainder in hh:mm:ss format.

This is the DAX I came up with. It assumes a measure with duration in seconds is available. In this case [Total Duration in Seconds]:

Duration = 
// Duration formatting : Given a number of seconds, returns a format of "n days, hh:mm:ss"
// We start with a duration in number of seconds
VAR Duration = [Total Duration in Seconds]
VAR Days = INT ( Duration / 86400)
VAR Hours = INT ( MOD( Duration - ( Days * 86400 ), 86400 ) / 3600)
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ), 3600 ) / 60)
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ), 3600 ), 60 ), 0) // We round up here to get a whole number
// Day or Days
VAR D =
 IF ( Days = 1, 
 CONCATENATE ( Days, " day " ),
 IF ( Days > 1, 
 CONCATENATE ( Days, " days " ),
 ""
 ) 
 )
// Hours with leading zeros
VAR H =
 IF ( LEN ( Hours ) = 1, 
 CONCATENATE ( "0", Hours ),
 CONCATENATE ( "", Hours )
 )
// Minutes with leading zeros
VAR M =
 IF (
 LEN ( Minutes ) = 1,
 CONCATENATE ( "0", Minutes ),
 CONCATENATE ( "", Minutes )
 )
// Seconds with leading zeros
VAR S =
 IF (
 LEN ( Seconds ) = 1,
 CONCATENATE ( "0", Seconds ),
 CONCATENATE ( "", Seconds )
 )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
 CONCATENATE(D,
 CONCATENATE(H,
 CONCATENATE(":", 
 CONCATENATE(M,
 CONCATENATE(":",
 S 
 ) 
 ) 
 )
 )
 )
Posted in Analysis Services, Business Intelligence, DAX, SQL Server | Leave a comment

Welcome (back) to my new (old) site

Somewhere around 2013 I was so awed by Microsoft’s Office 365 offering that I went “all-in” and moved my website to the then-nascent platform. Earlier this year the powers-that-be “sunsetted” the public-facing website portion of that product, so here we are, back in the open-source world.

I’ve been stupendously lazy about my web-site over the past 5 years. For a good reason – I’ve been very busy with Business Intelligence projects and professional services. One of my 2018 resolutions was to update my web-site and nudge the activity up just a bit. So I got a bit of early inspiration by learning that no-one could access the public Office 365 site anymore.

I’m still knocking back the cobwebs, but welcome – or welcome back.

Posted in Community, Microsoft, Uncategorized | Leave a comment

MDX for Date Formatting

This is a simple MDX snippet, but it comes up often enough that I think it’s worth a blog entry. A frequent question that comes up is how to format dates for display in MDX, especially in the parameter boxes on Reporting Services reports. The best practice is to let whatever front end you are working with do the formatting, but sometimes that isn’t always possible or feasible.

Here’s an example that uses 3 of the most common approaches. This example runs against Adventure Works.

WITH

MEMBER [Measures].[X] AS VBA!Format([Date].[Calendar].CurrentMember.Name, “medium date”)

MEMBER [Measures].[Y] AS CStr(Year([Date].[Calendar].CurrentMember.Name)) + ” ” + CStr(Month([Date].[Calendar].CurrentMember.Name))

MEMBER [Measures].[Z] AS CDate([Date].[Calendar].CurrentMember.Name),FORMAT_STRING = “mmmm yyyy”

SELECT

{[Measures].[X], [Measures].[Y], [Measures].[Z] } ON 0,

[Date].[Calendar].[Date].Members ON 1

FROM

[Adventure Works]

[Measures].[X] – Uses the VBA format function

[Measures].[Y] – Uses native functions to get at the numeric date components

[Measures].[Z] – Uses the FORMAT_STRING function to apply a mask to the incoming date value.

 

Given a choice, I would use the method employed by [Measures].[Z]

Posted in Uncategorized | Leave a comment

I am positive that measure is not negative – Weird Analysis Services Cube Behavior

I ran into a situation recently that had me stumped for a bit, and there didn’t seem to be any other posts about this when I searched.

I was working with a cube containing a very straightforward measure. This was an integer column and used an AggregationType of SUM. No other calculations were involved.

When view the top level value for the measure (in other words, the All member for all attributes in all dimensions), the value of the measure was negative.

Once you drilled down, the values became positive.

Normally when I see behavior like this, I would assume that unary operators were at work, and were set incorrectly. But that clearly was not the case here – since I had built this dimension myself, I knew that there was no account intelligence or unary operators.

I traced back through the query in the Data Source View all way to the underlying relational data. There were a few negative values, but not enough to account for the large negative numbers I was seeing.

It finally turned out that the magnitude of the SUM was overflowing the Int data type. By changing the data type of the measure to BigInt, the values started to show up as expected.

My usual practice it to let the data types of the underlying database determine the data type of the measure in the cubes. Here was one case where the data created an overflow so that the data type needed to be adjusted.

Now the drilled down values are correct, and so is the All level

Seems obvious now, but it didn’t last week when I was scratching my head trying to figure out where these mysterious negative numbers were coming from.

Posted in Analysis Services, Business Intelligence, MDX, OLAP, SQL Server | Leave a comment

Simple way to test connectivity or compose OLEDB connection strings

This tip has been around a while, but I still run into developers who don’t know it, so it’s worth a post.

If you ever need to test connectivity or compose a valid OLEDB connection string, try this:

  1. Create an empty text file.
  2. Rename the extension from TXT to UDL
  3. Double click this file. The Data Link Utility appears
  4. Back up to the Provider tab, make sure you have the correct Provider selected
  5. Fill in the correct Connection information
  6. Click “Test Connection”

This is a good way to remove many of the variables associated with connectivity, and focus just on the data connectivity and security context.

And there’s an additional benefit – this UDL file is simply a specialized text file that contains an OLE-DB connection string. You can open up this file using any text editor.

Posted in Business Intelligence, Microsoft, SQL Server | Leave a comment

Windows 8 Hot-Keys

One of the best things you can do to get comfortable with a new OS is take some time to learn the keyboard shortcuts. Here are the Windows 8 Hot-Keys

Windows logo key+ C Opens Charms bar
Windows logo key+, Temporarily peeks at the desktop
Windows logo key+. Moves the gutter to the right (snaps an application)
Windows logo key+/ Initiates input method editor (IME) reconversion
Windows logo key+Ctrl+Tab Cycles through apps and snaps them as they are cycled
Windows logo key+Enter Launches Narrator
Windows logo key+F Opens File Search app
Windows logo key+H Opens Share charm
Windows logo key+I Opens Settings charm
Windows logo key+J Swaps foreground between the snapped and filled apps
Windows logo key+K Opens Connect charm
Windows logo key+O Locks device orientation
Windows logo key+PgDown Moves Start Screen or Metro style application to the monitor on the right
Windows logo key+PgUp Moves Start Screen or Metro style application to the monitor on the left
Windows logo key+Q Opens Search pane
Windows logo key+Shift+. Moves the gutter to the left (snaps an application)
Windows logo key+Shift+Tab Cycles through apps in reverse order
Windows logo key+Shift+V Cycles through toasts in reverse order
Windows logo key+spacebar Switch input language and keyboard layout
Windows logo key+Tab Cycles through apps
Windows logo key+V Cycles through toasts
Windows logo key+W Opens Settings Search app
Windows logo key+Z Opens App Bar
Windows logo key+ C Opens Charms bar
Posted in Microsoft, Operating Systems, Software | Leave a comment

Backing up SSAS – Yes or No?

A question came up recently about how to back up SSAS. The vast majority of my clients do not back up SSAS. This isn’t appropropriate for all deployments, though. Here’s the thread:

This runs contrary to customary SQL server practice, but :

You may want to evaluate whether you back up the SSAS servers at all. I have several clients for whom there already was a robust SQL backup (and/or HA) in place. For these clients it turned out that it was quicker to rebuild straight from SQL than it was to load and restore SSAS from file based backups.

In such cases it’s more important to have the metadata (the XMLA script or BIDS solution) than to have the data itself. For most of these clients we don’t worry about metadata because we’re using source code control software of one form or another.

Obviously this approach would be less viable for larger SSAS installations. On the other hand, I’ve been frequently surprised to learn that the rebuild was faster, especially when off-line or off-premise backups are involved.

Tom

Posted in Analysis Services, Business Intelligence, Microsoft, SQL Server | Leave a comment

Agile and Business Intelligence

I had a client ask me about some of the techniques we are currently using,

“Tom, Can you send me authors, book titles and/or links outlining agile development (Story conferences, sprints, scrums, etc…).”

This was my response:

Here is the site that "started it all": http://agilemanifesto.org/

I used this book:
http://www.amazon.com/Agile-Data-Warehousing-World-Class-Intelligence/dp/0595471676
to acquire some of the principles we are currently using.

Obviously the WikiPedia entries are also good to start researching this:
http://en.wikipedia.org/wiki/Agile_software_development

Personally I don't think there's a perfect fit between the "orthodox"
Agile approach and Business Intelligence. However, I think that many Agile
practices are a great antidote for inefficient and counter-productive
practices we often see with database development.

HTH,

Tom
Posted in Business Intelligence | Leave a comment

Microsoft on SSAS and BI Vision Roadmap

A lot of this has been floating around various blogs and press releases, but here’s a very succinct synopsis of where MS is planning to go with Microsoft BI and the BI Semantic Model.

Posted in Analysis Services, Business Intelligence, MDX, SQL Server | Leave a comment

Join me for a Webinar: “Ad-hoc Scenario Analysis Using Existing Infrastructure”

Tom Huguelet Please join me Tuesday for “Ad-hoc Scenario Analysis Using Existing Infrastructure” April 19, 2011 12:00 p.m. ET / 9:00 a.m. PT http://bit.ly/gpyXpr

Hosted by SQL Server Magazine, sponsored by Tableau Software.

I really had fun creating this webinar. Gave me an opportunity to organize and clarify a lot of previous experience from Business Intelligence implementations I have done.

Hope you join me!

Posted in Business Intelligence, Community, Modeling/Architecture | Leave a comment