## 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"
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 " ),
""
)
)
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
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
)
)
)
)
)```

## 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.

## 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

[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]

## 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.

## 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.

## 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

## 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

“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```