Kasper de Jonge BI Blog

Vidas Matelis has been twittering live from the DAX session at the SQLPass. I just wanted to share these great new function available in DAX:

• Data Analysis Expressions = DAX
• DAX lets user do multidimensional analysis without user knowing that this is multidimensional analysis
• sample DAX: =[Qty]*[Price] – syntax just like Excel.
• DAX is not replacement for MDX
• DAX provides functions that implement relations database concepts: Filter tables, aggregates, follow relationships
• one of the many sample: =SUMX(RELATEDTABLE([Sales], Sales[Amount]) DAX
• dax has functions to assist with dynamic aggregations of measures: dynamic sample: =VALUES(Time[year]) & “.” & VALUES(Product[ProductID])
• DAX sample: =IF(VALUES(Time[Year])=2008,”Baseline”,”normal”)
• More than 80 Excel functions in DAX
• CTP3 has new FORMAT function to allow to convert any number to string
• DAX sample: [salesAmt]/[SalesAmt](All(Product)) – use measure as function.
• DAX = Sales[SalesAmt]/CALCULATE(Sales[SalesAmt], ALL(Product)) more complex syntax
• CTP3 DAX has 35 Time Intelligence Functions – require date column in the data. Column type Date
• DAX this version does not have custom time periods and weeks. Works with Yr, Qtr,Mth,Day
• DAX: FirstDate, LastDate, FirstNonBlank, StartOfMonth, StartOfQtr, EndOfYear
• DAX: DateAdd, DatesBetween, DatesInperiod – 26 function that return a table of dates
• DAX: PreviousDay, Nextmonth, DatesMTD, DatesYTD, TotalMTD, OpeningBalanceQuarter, ClosingBalanceYear, etc <-more funct
• Year over year growth: =Sales[SalesAmtt]-Sales[SalesAmth)(DateAdd(Time[Date],-1,Year].All(Time))
• AllTime – need to add now in CTP3, not in RTM(will be under cover). So that first year has data.
• QTD Sales =TotalQTD(Sales[SalesAmt],Time[Date], All(Time))
• DAX yearAgo:=Sales[SalesAmt)(ParallelPeriod(Time[Date],-12, Month).All(Time))
• autocomplete in CTP3 add single quotes, but they are optional.

Wow i can’t wait to start playing with these new  functions ! Thanks Vitas for sharing the info !

I also really really hope these time functions will be available in SSAS in the future, they won’t be availble in R2 Donald Farmer told me.

update: What will happen to the time functions when using a SSAS source? Is a Date dimension ok ? I hope so !

Tomislav Piasevoli created a very cool PowerPivot demo where he using a csv file supplied by Vidas Matelis’ Analysis Services Blog to analyze the bloggers. Great sample of using PowerPivot!

Read the entire blog post here.

Thanks to the great discussion some of us had on Twitter and the follow up on the PowerPivotPro blog we now know a PowerPivot API will be available in the future:

Patience guys. The API will be there.

We are just crunched on time to ship V1.0 of PowerPivot (remember – just 18 months from SQL 2008). But we have the full intention of exposing the API in full.

As MS BI consultant i’m really looking forward to PowerPivot but as developer with C# affinity i’m really excited on the prospect of the PowerPivot API. Think of all the potential projects you could do!

Today I tried to solve a much occurring, simple, ad hoc business problem with PowerPivot CTP2 based on a actual reporting services report i recently build on top op SSAS and reading PivotTable Named Sets in Excel 2010. I tried to solve the following scenario: I want to see the OrderQuantity of the current month compared to the monthly average of  OrderQuantity last year by promotion. This post will describe my quest for a solution and findings i have on the way. When CTP3 is released i will do the same again to see the differences.

I started by opening the gemini excel sheet I created in my previous blog post: Working with Gemini and Excel 2010 to make a pivot table. I then created a calculated field called “Cumulative Orderquantity” in the FactInternetSales table, since i was trying to get the average per month I decided to go for the following formula:

=Sum(FactInternetSales[OrderQuantity]) / Max(DimDate[MonthNumberOfYear])

I love the autocomplete feature that let’s you select datamembers as well as functions.  What i didn’t get to work is to divide the Sum(FactInternetSales[OrderQuantity]) by the current DimDate[MonthNumberOfYear], i had to take an aggregation. Maybe there is a way but i didn’t find it.

Putting the new measure on the pivottable results in the following:

One major issue i find here is that the columndimensions are grouped by measure, not making it easy to compare measures. The OLAP pivottable has this by default:

You can clearly see the difference, I have created a new gemini MS connect suggestion about this issue, so if you agree with me here you can vote for my change.

Next up was creating a way to select the current month and last month of last year. I know that in CTP3 time intelligence functions will be available so i will be using them when CTP3 is released. My first idea was to use the named sets as used here PivotTable Named Sets in Excel 2010 and select the months I want myself.

So i started to create the set by simply going to PivotTable Tools -> Options in the ribbon,  and selecting “Create set based on column items”. Now the Named Set creation UI will pop up:

We can easily remove the tuples I no longer want, in my case i want to select the current month with the sum OrderQuantity and month 12 of last year to show the “Cumulative Orderquantity”. After creating the set I tried to select the set in the PivotTable, this appeared to be currently not available in Gemini CTP2, luckily Donald Farmer told me on twitter named sets will be supported in CTP3 so to be continued. What i’m wondering is how the CTP3 time intelligence functions can be selected in the sets but i guess we see soon enough with CTP3 next month.

Since the set can’t be select I tried to select the two months by using a filter, but a relation between month and year is not made in the filter, you can only select a month and not a month in a year, meaning you can only select  a month that will be selected for all the years.

Not very pratical, i created a connect suggestion mentioning this problem.

These are my findings so far, I couldn’t solve my business case with CTP2 of PowerPivot. I have good hopes CTP3 will get me a lot further and maybe even solve it. I hope a lot of work is spent on user friendliness since Excel is the GUI the end user must be able to create the comparisons.

At the MS Excel team blog they have a great blog post where a member of the SQL Server Analysis Services team, Ashvini Sharma, will tell us about the PowerPivot.
The post gives a comprehensive look at the entire PowerPivot product, from front to back and names all components.

Read the entire article here: http://blogs.msdn.com/excel/archive/2009/10/22/introducing-powerpivot.aspx

The big question i have is, will the VertiPaq engine have an API where developers can build there own frontend on PowerPivot… with loading, connection and analysing sources in memory.

Microsoft just announced at the Microsoft SharePoint Conference 2009 in Las Vegas this morning that project codename Gemini will ship as “SQL Server PowerPivot for Excel” and “SQL Server PowerPivot for Sharepoint”. And immediately launched the new site http://www.powerpivot.com/.

Loads of information at the IT pro’s at PowerPivot datasheet.

• “SQL Server PowerPivot for Excel” Is the Excel plugin to get new data analysis features on the desktop, business users can load very large data sets into memory and use powerful relational capabilities to create advanced analytics applications.
• “SQL Server PowerPivot for Sharepoint”. Is when you deploy the excel plugin to SharePoint. PowerPivot encapsulates enterprise data to help centralize BI and data management while providing reliable access to it. The PowerPivot administrative dashboard even enables IT departments to monitor and manage shared applications for security, availability and performance. As you can see below:

Andrew Brust made a great blogpost about opening a Azure database with gemini:

http://www.brustblog.com/archive/2009/09/03/gemini-does-cloud.aspx

Great video about gemini where Donald Farmer and Julie Strauss talk about the inside of gemini:

http://channel9.msdn.com/posts/Charles/Donald-Farmer-and-Julie-Strauss-Inside-Project-Gemini

The MS gemini team posted a new blog with a overview of Gemini, what was new to me was the following part:

Sharing Gemini Applications

While many workbooks are built for personal use, some are worthy of being shared across a workgroup. Here again, Gemini works the way Office users do. Since Gemini data is stored within an Excel document file, any way to move that document – through file shares, emails, publishing to SharePoint, etc. – transport the Gemini contents along as well. Users without the Gemini addin can browse the data, those with the addin get the full experience. Just as Excel and Gemini light up together, Gemini also extends SharePoint capabilities in several ways.

Report Gallery

For the more visually-inclined amongst us, a flat SharePoint list leaves something to be desired. File names, data last updated and by who are useful but only tell part of the story. Gemini provides Silverlight based skins that present different views on document libraries. These views show snapshots of the contents of documents. In the example below, we see two workbooks with two spreadsheets within them:

These snapshots are also live links in that clicking on a thumbnail of the a worksheet will take users directly into ECS with the worksheet loaded.

Scheduled Data Refresh

The Gemini model embedded within the spreadsheet keeps information about where data came from. Once published to SharePoint, users can specify schedules for the data refresh operation so the workbooks use the resources of the server to stay fresh.

And what was even more impressive:

Using Gemini Applications as Data Sources

Once published to SharePoint, Gemini models embedded within workbooks appear as an Analysis Services databases! This means any AS client tool – Excel, Report Builder, etc. – can connect to this database as if it were on just another AS server. The only difference for these clients is use of a URL to the document stored in SharePoint instead of a server name. Gemini services running on SharePoint handle loading the right database, managing its lifetime, and transparently redirecting client queries to the right database on the right server.

This gives some incredible new options, but i wonder what it will take of server perfomance (memory ?) when you have 20 of these models on your server.

Read the entire preview here:

http://blogs.msdn.com/gemini/archive/2009/08/24/overview-of-gemini-features.aspx

UPDATE:

after posting the question about the performance on the original msdn blog post i got an answer from one of the Gemini team members:

You’re correct, the Gemini embedded data engine loads data into memory. However this is only while the models are in use. Gemini’s SharePoint services manage the lifetime of these models and move these in and out of the SP content database transparently from the end users, the only realization users might have is the first time they connect to a model it takes slightly longer because we’re extracting the workbook then extracting the AS database and loading it up in memory.