Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for August 2011

Kinect for BI?

leave a comment »

It was only a matter of time, but here’s what I think is the first use of Kinect for Microsoft BI:

http://www.zdnet.com/blog/microsoft/how-microsofts-kinect-could-bring-gesture-recognition-to-business-apps/10489

OK so it’s very, very basic and only works on Dynamics Business Analyzer, but it shows what could be done. Maybe something to think about for a BI Power Hour presentation on Crescent? Although until the day comes that we all work in CSI-style labs with large screens to wave our arms around in front of, I seriously doubt it would have any practical use. Anyway, if you wanted to do something truly cool you’d not even bother moving at all and build your reports using the power of your mind – I saw Guy Smith-Ferrier do a very entertaining session called “Mind Control Your Computer in C#” recently, so the idea isn’t as far-fetched as it seems!

Written by Chris Webb

August 31, 2011 at 10:35 am

Posted in BI

Installing SSAS 2008R2 SP1 breaks PowerPivot

with 8 comments

Here’s something I found out a few weeks ago, just before I went on holiday: installing SP1 for SSAS 2008R2 breaks a PowerPivot installation on the same machine. After I’d installed SP1 on my laptop I found I got an “Unable to open file” error message in PowerPivot whenever I tried to import data, at which point PowerPivot crashed. For me the fix was easy – reinstall PowerPivot and it worked again – but I’m glad I found this out before I needed to use PowerPivot in front of a customer.

Written by Chris Webb

August 30, 2011 at 10:44 am

Posted in PowerPivot

What is Microsoft’s mobile BI strategy?

with 5 comments

My post about Sharepoint and the MS BI strategy last month generated a lot of debate; an equally hot topic is that of Microsoft’s mobile BI strategy, or lack of it. It’s something I’ve heard a lot of people sounding off about recently which is why I found the this article interesting:
http://www.v3.co.uk/v3-uk/the-frontline-blog/2102901/wheres-mobile-bi-strategy-microsoft
Apart from a diplomatic quote from Donald Farmer in his current guise as QlikView evangelist-in-chief, the article picks up on a thread from the Microsoft BI Facebook page and perhaps tries to read too much into it…

Written by Chris Webb

August 23, 2011 at 11:39 pm

Posted in BI

New MDX Book: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

leave a comment »

I’ve just seen that my friend Tomislav Piasevoli’s new MDX book has just been published – it’s called “MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook” and you can get it here:

http://www.packtpub.com/mdx-with-microsoft-sql-server-2008-r2-analysis-services/book

Image MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

I was one of the technical reviewers on the book, so I’m not even going to pretend to review it, but it’s definitely worth checking out if you’re looking to deepen your knowledge of MDX and see some worked examples.

Written by Chris Webb

August 11, 2011 at 11:51 am

Posted in Books, MDX

DAX Queries, Part 6

with 5 comments

Before I start this post, I’d like to mention that Cathy Dumas of the dev team has started blogging on BISM topics here: http://blogs.msdn.com/b/cathyk/. There are some good posts up already, with more to come I’m sure.

Anyway, back to DAX queries. Just a quick post today about the ROW() function, which returns a table containing a single row of data. Here’s an example of its use inside a query:

evaluate(
row(
"Column 1"
, "Some text"
, "Column 2"
, countrows(dimdate)
)
)

image

It’s pretty simple: the parameters represent a series of column name/DAX expression pairs for your table, so in this example I’m defining a table with two columns and one row, with the first column returning the text “Some text” and the second column returning the result of the expression countrows(dimdate). I understand it is intended for use by client tools such as Crescent when they want to return a series of grand totals without any detail data.

Written by Chris Webb

August 10, 2011 at 4:37 pm

Posted in DAX

DAX Queries, Part 5

with one comment

Rob Collie (or rather David Hager, writing on Rob Collie’s blog) recently posted a detailed explanation of how to use the new DAX TopN() function inside a calculation; I assume, however, that’s it going to be more commonly used inside queries. Here’s a simple example that returns the top 10 rows from DimDate by the sum of Sales Amount:

evaluate(
topn(
10
, DimDate
, calculate(sum(FactInternetSales[SalesAmount]))
, 0)
)

image

Finding the top n of something is a pretty common requirement; an equally common requirement is finding multiple top ns for another list of values, such as the top 10 products sold every year, remembering that you may well have a different top 10 products for each year. In MDX you would, of course, solve this by using the TopCount() function inside the Generate() function and in DAX the solution is very similar, using either the DAX Generate() or GenerateAll() functions. Here’s a query that shows how to do this:

define
measure DimDate[Sum of Sales] = sum(FactInternetSales[SalesAmount])
evaluate(
addcolumns(
filter(
generate(
values(DimDate[CalendarYear])
, topn(
10
, values(DimProduct[EnglishProductName])
, DimDate[Sum of Sales]
, 0)
)
, DimDate[Sum of Sales]>0)
, “Sum of Sales”
, DimDate[Sum of Sales]
)
)
order by
DimDate[CalendarYear] asc
, DimDate[Sum of Sales] desc

Here’s some of the output:

image

There’s quite a lot going on here, so let me explain some things…

First, notice how I can define a new calculated measure in the Define clause of a DAX query, as follows:

define
measure DimDate[Sum of Sales] = sum(FactInternetSales[SalesAmount])

I need to use this expression several times in the query, so it makes sense to define it just once. Next, here’s how I get the top 10 Products for each Year:

generate(
values(DimDate[CalendarYear])
, topn(
10
, values(DimProduct[EnglishProductName])
, DimDate[Sum of Sales]
, 0)
)

The Generate() function iterates through every row in the table returned by values(DimDate[CalendarYear]) and evaluates the TopN() call in the context of each row; it then unions each top 10 together. In the main query I’m also using the Filter function to remove all the rows returned where there were no sales, just to make the results look tidier. The GenerateAll() function does pretty much the same as the Generate() function, but it will return a row containing null values in situations where the second parameter (in this case, the call to TopN) returns a table with no rows. Last of all, I’m using the AddColumns() function to ensure that the Sum Of Sales is added on to the resultset, and also using the Order By clause to ensure that the rows are returned in a meaningful order.

In part 6, I’ll look at the Row() function.

Written by Chris Webb

August 6, 2011 at 10:39 pm

Posted in DAX

Follow

Get every new post delivered to your Inbox.

Join 3,311 other followers