Defining DAX Measures In The With Clause Of An MDX Query

I think of putting this functionality into OpenView

Chris Webb's BI Blog

It’s a little-known fact (but certainly not completely unknown – it was mentioned in Marco, Alberto and my SSAS Tabular book I think) that you can define measures using DAX in the WITH clause of an MDX query. This means you can write queries like the following against an SSAS Tabular model:

with
measure ‘Date'[Demo Calc] =
countrows(‘Date’)

select {measures.[Demo Calc]} on 0,
[Date].[Calendar Year].members on 1
from [Model]

image

The official documentation, such as it is, is here:
http://msdn.microsoft.com/en-us/library/hh758441.aspx

Unfortunately you can’t use it from Excel 2013 using the new ‘create calculated measure’ functionality; I also talked to the nice people behind OLAP PivotTable Extensions and there are some very good reasons why they can’t support this either.

What use is this then? You’re only going to be able to use it in scenarios where you control the generation of the MDX on the client side, such as SSRS reports…

View original post 96 more words

Point-In-Time Dimension Reporting In DAX

Nice touch

Chris Webb's BI Blog

Before I start, I have to state that the technique shown in this post isn’t mine but was developed by my colleague Andrew Simmans, who has very kindly allowed me to blog about it.

Over the last few months I’ve been working on an SSAS Tabular project that has not only presented some interesting modelling challenges, but has shown how DAX can offer some new and interesting solutions to these challenges. Consider the following scenario: a supermarket sells products, and we have a fact table showing sales of products by day. Here’s some sample data:

image

To complicate matters, each product has one product manager but product managers for particular products change from time to time. Normally this might be solved by adding the product manager name to the Product dimension table and implementing a Type 2 Slowly Changing Dimension. In this case, though, we want something slightly different: instead of…

View original post 622 more words

New MDX Divide() Function

New function for MDX 🙂

Chris Webb's BI Blog

A few weeks ago I saw Rob Collie’s blog post about the DAX Divide() function, and I was a bit surprised that I hadn’t seen it before. Then, yesterday, I found that the same function has appeared in MDX in SSAS 2012 Multidimensional. Here’s the entry in BOL:
http://msdn.microsoft.com/en-us/library/jj873944.aspx

The syntax is:
Divide (<numerator>, <denominator> [,<alternateresult>])

Numerator and Denominator are self-explanatory; alternateresult is the constant value you want to return in case of division by zero, and if it is not specified a null is returned.

It turns out that it was added after RTM of SSAS 2012, and officially first appeared in SSAS 2012 SP1. This is the first new MDX function since… what, 2005? 2008? Five long years at least.

[A loud bump is heard as Chris falls off his chair in amazement]

This is important because there have been requests for a safe divide function in MDX…

View original post 271 more words

The New Free Microsoft BI Interactive Dashboarding Tool

OpenView Personal Edition Picture

OpenView Personal Edition

Hi everyone,

This is the MDX version of OpenView the Interactive Dashboarding for Analysis Services. This one has the advantage of being able to work on the old SQL Server Analysis Services 2005/2008 and 2008 R2 edition and of course the new 2012 both for MD and for Tabular.

What I am sending you is a blank piece of paper. I did not have the time for a users guide. I will be completing it in a week I hope. Since it is summer vacation maybe you won’t be able take a look at it at all.

The software basically needs no database to store its own data. It saves and opens the special ovw extensioned documents and stores the dashboard’s data in those files.

Anxious to hear from you all. (Good or bad or ugly J)

The size of the install file is 6.5 Megabytes. It is zipped in the rar format. It is an msi installer. The files with extension ovw are automatically opened with this application.

You can install it on computers using Windows XP/Vista/7/8 and Windows 2003/2008/2008 R2/2012 server OS’s.

Need for edition of .net framework 4 and a reachable Analysis Service on the local machine or the local network. All security is Windows Authentication. No usernames and passwords.

The link for download is:

http://www.openanalyzer.com/files_setupx/personal/OpenViewPersonal.rar

and youtube link is here

http://www.youtube.com/watch?v=5XrtDhPxZik

Best,

Hakan Altınok