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
)
)
)
)
)
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.
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]
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.
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:
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.
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
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
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.