Category Archives: 17576

PowerPivot & DAX & Data Explorer BlogRoll – 31st March 2013



PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 31st March 2013.


 


SQLBI
Checklist for Memory Optimizations in PowerPivot and Tabular Models – bit.ly/17aAaNr


Beyond Rational
MS BI # 85 – PowerPivot #2 – Download , Install , Connect to SQL Server and Getting Started – bit.ly/14BHhyJ


Bacon Bits
Use Data Explorer to Hack into Very Hidden Sheets – bit.ly/ZIcNqk


readwrite enterprise
Microsoft’s Data Explorer: Picking Up Where Bing Leaves Off – bit.ly/16XQXDd


Matt Masson
Consuming CANSIM Datasets using Data Explorer – bit.ly/10llbL8


PowerPivotPro
Always Show Yesterday, Today, or Tomorrow’s Data – bit.ly/14KrjCL


Lightswitch For The Enterprise
Self Reporting via PowerPivot In Highly Deferred Execution Mode – bit.ly/VtkjBj


Data Explorer
Data Explorer Online Search Overview – bit.ly/10eJLfx


PowerPivotPro
New Version of Data Explorer Released – bit.ly/16il19t


Katie & Emil Newsletter
Add-in Data Explorer and Excel tutorial – conta.cc/XDYIHw


SQL blog.com
BI Beginner: Data Explorer is a must have – bit.ly/YBl7bO


Gobán Saor
M’s back – Create a PowerPivot Time Dimension using Excel’s Data Explorerbit.ly/ZqJVmq


Gobán Saor
The Model of a very modern PowerPivot – without the add-in – bit.ly/YDgRFE

PowerPivot & DAX & Data Explorer BlogRoll – 24th March 2013



PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 24th March 2013.


 


Romi’s Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar – bit.ly/1188Lrc


Kasper de Jonge PowerPivot Blog
NBA team spending and their results with Excel, PowerPivot and Data Explorerbit.ly/10sJuJo


SQLServerCentral.com
Stairway to PowerPivot and DAX – Level 4: The DAX BLANK() Function – bit.ly/Zllw1a


BaconBits
5 Cool Things You Can Do With Data Explorerbit.ly/Ydiorz


LessThanDot
How I used Data Explorer to create a demo – < a href=”http://bit.ly/10hIYhi”>bit.ly/10hIYhi


PowerPivotPro
ISFILTERED: A Better Way to Detect Totals – bit.ly/ZRvCon


Brent Greenwood’s BI Blog
Pareto Chart with PowerPivotbit.ly/14cfXXs


Gobán Saor
[Updated]Look’s like Star Schema is back on the menu! – bit.ly/YBcB8Z


Chris Webb’s BI Blog
UK/US Date Format Bug in PowerPivot and SSAS Tabular – bit.ly/WMd8Jy


Romi’s Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar – bit.ly/1188Lrc


Machlis Musings
New Excel Data Explorer add-in bolsters data analysis – bit.ly/16Uah4g


MSSQLTips
Creating Hierarchies in PowerPivotbit.ly/X8uAZS

PowerPivot & DAX & Data Explorer BlogRoll – 17th March 2013



PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 17th March 2013.


 


MS Office Blog
Learn about Data Explorer Formulas – bit.ly/118wr2d


Javier Guillén Pervasive Business Intelligence
PivotStream and Mariner join forces to deliver PowerPivot solutions – bit.ly/118uzqc


Chris Webb’s BI Blog
Finding Shakespeare’s Favourite Words With Data Explorerbit.ly/133FUcz


Erik Svensen
How to use Data Explorer for Excel to extract data from Statistics Denmark – bit.ly/YKjCag


MSDN Blog
A new build of Data Explorer, and an Auto Update feature – bit.ly/WwuR7P


SSIS Junkie
M Lives! – bit.ly/10JyB8r


Gobán Saor
DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in – bit.ly/XjZZn4


Kasper de Jonge PowerPivot Blog
What is eating up my memory the PowerPivot / Excel edition – bit.ly/113zmpl


Some Random Thoughts
Unpivoting Data in Data Explorer- bit.ly/118xIGy


Javier Guillén Pervasive Business Intelligence
Configuring “drill to details” behavior in PowerPivot: Part 1 – bit.ly/YlDZbE


PowerPivotPro
PowerPivot on Airplanes and NBC – bit.ly/142RIen

PowerPivot & DAX & Data Explorer BlogRoll – 10th March 2013



PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 10th March 2013.


 


Extended Results
Calendar Analytics – bit.ly/VYvfLU


PowerPivotPro
PowerPivot Discovers a New Form of Communication in the Animal World, Makes Headlines in Science! – bit.ly/YhOPhN


Matt Masson
Using Data Explorer with Power View – bit.ly/XYGt2H


Customer Feedback SQL Server Content Samples
Data Explorer: An introduction to table relationships and the Expand operation – bit.ly/XWWtlN


PowerPivotPro
“Introducing” Data Explorerbit.ly/Z4BSdv


Chris Webb’s BI Blog
Data Explorer–Where Does The Real Work Get Done? – bit.ly/VOkkVc


Dynamics NAV Financials
PowerPivot to the People – bit.ly/YtSzzK


PowerPivotPro
A funny thing happened in Redmond… – bit.ly/12rBmfH


Gerhard Brueckl’s BI Blog
Another Post about Calculating New and Returning Customers – Part 2 – bit.ly/YKRMHL


Chris Webb’s BI Blog
Calling A Web Service From Data Explorer, Part 2 – bit.ly/Y9gdi1


Vignesh C
Consume csv with Data Explorerbit.ly/14lWZcN


Matt Masson
Dynamic Lookups With Data Explorerbit.ly/Vw6Zkg


Some Random Thoughts
Introduction to Data Explorer Preview for Excel – bit.ly/ZaWrDt


Dan English’s BI Blog
Installing Data Explorer Preview & Demo with IMDB Data – bit.ly/W0vvJe


Chris Webb’s BI Blog
Calling A Web Service From Data Explorer, Part 1 – bit.ly/WErC9X

PowerPivot & DAX & Data Explorer BlogRoll – 3rd March 2013



PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 3rd March 2013.


 


Chris Webb’s BI Blog
Importing Data From Multiple Log Files Using Data Explorerbit.ly/YWPTvA


SSISJunkie
Traversing the Facebook Graph using Data Explorerbit.ly/VWYY6J


Matt Masson
Access the Windows Azure Marketplace from Data Explorerbit.ly/YHwRFq


Altius Consulting
Best Oscar winning Film? My first Data Explorer adventure… – bit.ly/VWYC01


Ian’s SharePoint Blog
SharePoint OData and the Excel Data Explorerbit.ly/160EaiL


Chris Webb’s BI Blog
Public Preview of Data Explorerbit.ly/XPmXlK


Microsoft
Microsoft “Data Explorer” Preview for Excel – bit.ly/Y6WY8M


PowerPivotPro
Toggling Conditional Formatting On/Off via Slicer – bit.ly/XLISNW


SQLBI – Marco Russo
Update to the PowerPivot for Excel 2013 licensing – bit.ly/XLILlB


TechCrunch
Microsoft Launches Updated Office 365 For Business, Adds ProPlus With Full Office Apps And New Small And Medium Business Versions – http://tcrn.ch/YHyaEb


MSSQLTips
Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel – bit.ly/WAQ2kD


PowerPivotPro
How to buy PowerPivot 2013, and the $30 Volume Licensing Workaround – bit.ly/ZYLrzc


MSSQLTips
Combining Data from Multiple Relational Data Sources into One Table in PowerPivot for Excel – bit.ly/13dFUFY

DAX, The Dark Knight



Excel Formulas Are easy, So Why Is DAX So Hard?


Recently, Rob Collie, aka PowerPivotPro, posted a blog entry entitled ‘The Ballad of Ken Puls, DAX Convert’. Whilst it was an interestiung post, this is not to specifically offer any comment on that blog, or to stroke Ken’s ego <g>, I was intrigued by the part where Rob mentioned that Ken had said, and I quote,


 


“I firmly believe that PowerPivot is the future of Excel.  No question in my mind.  It’s insanely easy to get some killer BI in your hands with very little learning, which is awesome.  But…  What I don’t get is DAX.  The light just isn’t going on for me.  If you want to mobilize the Excel Pro army to really make this take off, DAX needs to become accessible for us.”

 


This rang a bell with me, as about six weeks ago I taught a 2 day PowerPivot course with Chris Webb to a class of 15 technology savvy people. These are smart people, but it was clear when covering the more advanced aspects of DAX that they were straining to grasp some of the concepts. On my journey home I was pondering about this. I have stated before that I believe that Microsoft over-egg the simplicity of PowerPivot, suggesting that any user can pick up PowerPivot and create fantastic BI immediately. I think that, whilst easy to get started, to really get the true potential out of PowerPivot you have to have some understanding of Data Modelling.


Have Microsoft got it wrong with DAX as well when they took a deliberate decision to make DAX Excel-like in its syntax? In many ways, this is understandable, their experience of MDX, which really is hard, could weigh heavily on their outlook. But, it has been a double-edged sword in my view. Excel users people are quite ready to give DAX a go because it looks as easy as Excel formulas, but conversely, when they get to advanced DAX (CALCULATE, FILTER, ALL, etc.), they can easily get dis-heartened because it is not as easy as Excel formulas.


So I thought I would look at why DAX is so hard for an Excel user. I came up with 7 topics that I decided to look at, namely:


  • Data Typed
  • Integer Dates
  • Formula Formatting
  • Evaluation Context
  • Extendible
  • Debugging Aids
  • Ease of use

 


Data Typed


Integer Dates


Formula Formatting


Extendible


Debugging Aids


DAX is great if your formula works, but when it doesn’t you need good tools to help you debug it. Unfortunately. Microsoft haven’t provided any debugging tools whatsoever, so debugging is hard, incredibly hard. It should be part of our course, but with no tools to talk about there is nothing to suggest yet.


Just to complicate matters, the data usually consists of thousands, or even hundreds of thousands or millions, of rows, and reducing it to a reduced set is not staright-forward.


Marco Russo, Darren Gosbell, Angry Koala, and Paul te Braak have created a nice Excel addin called DAX Studio which is as good as it gets. it is a useful tool for building DAX formulae, as described by Javier Guillen in his post Using DAX studio to create PowerPivot measures, and this technique can be used to rebuild the formula from scratch checking as you go, but it is not reall a debugging tool, and was not meant as such.


Compare this to Excel. In Excel, the helpful debugging facilities are:


  • Evaluate Formula
  • Watch Window
  • F9

 


The Evaluate Formula facility lets you evaluate a formula in the activecell, stepping through it as each stage of the formula seeing what the result at that stage is, until it is fully resolved. It allows allows stepping in and out of the cell value if the expression being evaluated is a single cell.


The Watch Window allows you to add a cell or a range of cells to be watched, thw window showing the current value, reflecting any changes.


F9 allows you to select any expression within the formula bar and see what it resolves to. The expression can be a single cell, a function, or a set of functions, as long as it is an expression that can be resolved in its own right.


So, three facilties to assist in debugging the formula should make Excel formula debugging easy? In reality, I never use Evaluate Formula or the Watch Window as they do not provide enough value in my view. F9 is very useful, in that it is possible to evaluate any part of the formula. This is in contrast to Evaluate Formula, which evaluates the whole formula which means that the TRUE path is only evaluated, whereas with F9 you can select any part and have it evaluated regardless of whether the actual data would force the formula to follow that path or not.


Okay, Excel has a few tools, which is a few more than DAX, but they are hardly what you would call sophisticated. It still mainly relies on experience, intuition, and insight to be able to debug the more complex Excel formulae. Debugging DAX is diffocult, but debugging complex Excel formulae is not the easiest task either.


Evaluation Context


Evaluation context is probably the


Evaluation context is the aspect of DAX that people struggle with most when they start with PowerPivot. Understanding filter context and row context, when each comes into play, how you can override aspects of the filter context, all of this can seem mind boggling at first.


Ease of use

CUBEs For Dessert



Introduction


A few weeks ago I showed how to create a fully dynamic table using CUBE formulae against a PowerPivot model in Cooking With Cubes.This example presented data from the AdventureWorks database of sales of product categories over years, all integrated with a Fiscal Year slicer.


I recently had a similar need, but with the further challenge of presenting two levels in a hierarchy (actually, this was related tables in a snowflake schema). I needed to show all of the items in the first level, and for each of these items, all of the associated items at the second level including those items that have no sales value. The AdventureWorks database has a similar setup with products, where each sale is for a particular product, each product is of specific product sub-category, and each product sub-category is of a specific product category. Within this model, we would be showing sub-categories of Mountain Bikes and Road Bikes against the category of bikes, but not against any other category.


 


I will demonstrate my solution using the same AdventureWorks data. Whilst this will be similar to the previous post in showing how to manage the presentation using Excel formulae, it is extended to multiple data levels and some more complex formatting. Additionally, and the main point of this post, I show a technique to display the related values in that hierarchy, not simple with straight CUBE formulae, by adding some MDX trickery in the CUBE formulae.


Figure 1 shows the final results of all of the formulae and formatting.



Figure 1 – Final Results


Preparing With Good Ingredients


First I need to prepare the PowerPivot model so that I have have product name, product sub-category name, and product category name all on the Product table. To achieve this, I added two calculated columns


[ProductSubcategoryName]
=RELATED(ProductSubcategory[ProductSubcategoryName]
[ProductCategoryName]
=RELATED(ProductCategory[ProductCategoryName]
Formula 1 – PowerPivot Model Calculated Columns


Mixing It Up


The data is ready, so let’s build the required data. I need the following:


  1. a text definition of the cube
  2. a set of all categories
  3. a count if the items in the category set
  4. a list of all of the items in the year set, from the slicer or the dimension if no slicer selections
  5. a count if the items in the year set
  6. a list of all of the items in the category set (in columns)
  7. for each listed category, a set of all associated sub-category items
  8. for each sub-category set, a count of all items in that set
  9. a running total of the sub-category set counts
  10. for each sub-category set, a list of items (in rows)
  11. a reference cell for the sales measure

Taking these in the order given, showing cell(s), defined name, definition:


1. Cube Definition


This is simple text for the connection argument to the cube functions.
J1:[_model]PowerPivot Data
I am using Excel 2010 for this example, Excel 2013 would use the value ThisWorkbookdataModel.
In the previous example, I named this cell _cube, here I use _model.


2. Category Set


This is the set of all categories in the model. This is being taken from the calculated column for product category name in the Products table.
J2:[_setCategory]=CUBESET(_model,”[Product].[ProductCategoryName].Members”,”Categories”)


3. Category Set Count


A simple count of the items in the category set, used in later dynamic formulae.
J3:[_setCategoryCount]=CUBESETCOUNT(_setCategory)-2
I subtract two from the actual count because this set contains the All member and blank, I am not interested in these.


4. Year Set


This is the set of years in the model, constrained by the slicer.
J4:[_setYear]
=IF(CUBERANKEDMEMBER(_model,Slicer_FiscalYear,1)<>”All”,
CUBESET(_model,Slicer_FiscalYear,”Slicer Years”),
CUBESET(_model,”[Date].[FiscalYear].Children”,”Dimension Years”))

As in the previous example, to show all years with no slicer selection (not show All), I check the slicer to see the first set member is All, getting the set from the Date dimsnsion if so, otherwise get the set from the slicer.


6. Year Set Count


A simple count of the items in the year set.
J3:[_setYearCount]=CUBESETCOUNT(_setYear)


6. List of Category Set Items


A list of the items in the category set, for use in building the sub-category sets.
K2:N2:[_listCategory]=IFERROR(CUBERANKEDMEMBER(_model,$J$2,COLUMN()-COLUMN($J$2)+2),””)
The name definition uses a dynamic formula
=OFFSET(_setCategory,0,1,1,_setCategoryCount)
This is a simple dynamic OFFSET formula to build a list of the categories.


7. Sets of Subcategory Items


Sets of sub-category items, one set per category.
K3:N3:[_setsSubcategory]=IFERROR(CUBESET(_model,”EXISTS([Product].[ProductSubcategoryName].Members,”&K$2&”)”,”Products:”&K$2),””)
This is where the MDX magic kicks in. The EXISTS function … Returns the set of tuples of the first set specified that exist with one or more tuples of the second set specified …, so we can use it within a CUBESET function to get all of the product subcategory names for the category above, in _listCategory.
Using this technique, instead of building a set of all subcategories, we build a set of the subcategories for a particular category; in our case the category passed to the CUBESET function from the cells in _listCategory.
I prefix the text shown with the text Products: for readability
The name definition uses a dynamic formula
=OFFSET(_setCategory,1,1,1,_setCategoryCount)


8. List of Subcategory Set Counts


A list of counts for the sub-category sets, for use in the layout.
K4:N4:[_setsSubcategoryCounts]=INDEX(CUBESETCOUNT(_setsSubcategories),COLUMN()-COLUMN(_setCategory))
The name definition uses a dynamic formula
=OFFSET(_setCategory,2,1,1,_setCategoryCount)


9. List of Subcategory Set Count Totals


A list of running totals for the sub-category set counts, for use in the layout.
K5:O5:[_setsSubcategoryTotals]=SUM($K$4:K4)-K$4+1
The name definition uses a dynamic formula
=OFFSET(_setCategory,3,1,1,_setCategoryCount+1)


10. Lists of Sub-category Sets Items


Lists of each set sub-category items, within the relevant category.
K6:N30:[_listSubcategoryies]=IFERROR(CUBERANKEDMEMBER(_model,K$3,ROW()-ROW(_setCategory)-2),””)
This is using the same technique to list the set items as in the previous blog.
The name definition uses a dynamic formula
=OFFSET(_setCategory,4,1,MAX(_setsSubcategoryCounts)-1,_setCategoryCount))
Unlike the other dynamic formulae, this one is multi-column, for the multiple categories.


11. Sales Measure


Finally, we have a cell defining the Sales measure
J7:[_sales]=CUBEMEMBER(_model,”[Measures].[Sum of SalesAmount]”,”Sales”)
This is to save repeated definition within the value formulae.


Figure 2 and Figure 3 below show all of the helper cells populated, and the nanmes defined in the NameManager.



Figure 2 – Populated Helper Cells




Figure 3 – Defined Names


It’s All About The Presentation


All that needs to be done now is to lay it out, allowing for a variable number of categories and a differing variable number of sub-categories within each category.


In my layout, I am repeating each category a number of times equal to to the number of subcategores for that category, but only showing the first instance. The years are shown as column headings, using the year slicer to filter the columns. The values are displayed referencing the category, sub-category, and year headings.


The formulae are as follows:


Category items


A7:An:=IFERROR(INDEX(_listCategory,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1)),””)
This matches the ordinal row number within the list of categories against the running totals for sub-category set counts, so as to determine which of the categories we are listing.

 


Sub-category items


B7:Bn:=IFERROR(IF(A7<>A6,””,INDEX(_listSubcategories,COUNTIF($A$7:A7,A7)-1,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1))),””)
This uses the same matching technique to determine which sub-category set to list.

 


Filtered Years


D6:H6:=IFERROR(CUBERANKEDMEMBER(_model,_setYear,COLUMN()-COLUMN(_start)-2),””)
Although the slicer controls the selected years, the year set in _setYear outsorts the data, the filtering is done there.

 


Sales Totals


C7:C50:=IFERROR(CUBEVALUE(_model,_sales,IF($A7<>$A6,$A7,$A7:$B7),Slicer_FiscalYear),””)
This determines the sum of the values for the current row, be that just a category row, or a category and sub-category row, whilst respecting the slicer selection. The IF in the formula caters for that possibility of a category row or a category and sub-category row.

 


Sales Values


D7:H50:=IFERROR(CUBEVALUE(_model,_sales,D$6,IF($A7<>$A6,$A7,$A7:$B7)),””)
Similar to the sales totals, but using the year heading instead of the slicer, beacuse the year heading has already take the slicewr into account.

 


Conditional formatting is added to the results to improve the viusual effect and readability.


Product Category Labels


The list of product categories is repeated on every row, changing at the appropriate point of course. This value is needed for the value calculation, but I feel the look is improved if the repeated items are suppressed, so I use conditional formatting to set the font colour to white on repeated items. This uses a simple formula of

A7:A50:=A7=A6


Product Category Totals


I list the product category one more time that its associated sub-categories. This is so that I can show category totals as well as category and sub-category totals. These need highlighting so that the totals stand out. To achieve this, I have made them bold with a blue font, borders above and below. The conditional formatting formula for this is

A7:H50:=AND($A7<>””,$A7<>$A6,ROW($A7)-ROW(_start)-1<=MAX(_setsSubcategoryTotals),COLUMN(A6)-COLUMN(_start)-3<=_setYearCount)


But Is It All Too Rich?


One question that may occur to anyone reading this who is skilled with pivot tables is, why bother? Couldn’t the same effect be achieved by formatting a pivot table showing the product category and sub-category and years with the format set to Show Tabular Form and Repeat All Item Labels? This would give a similar look, but it would only show product categories and sub-categories that have a value, it would not show those that are not selling. Agreed, there is a pivot option to Show items with no data on rows which does address this, but the problem here is that it will show all product sub-categories under EVERY product category, my method only shows the relevant sub-categories.


In Summary


In many ways this is similar to the previous post on CUBE formulae, with an extra level of data and more complex formatting. But what I hope that it shows is that by utilising some fairly straightforward MDX the formulae can do far more complex operations, and extend the data displayed. And who knows how far this can be taken as one’s MDX skills increase?


It is possible that many of the formulas could be replaced with DAX measures in the model, maybe that is a post for another time.


You can download the workbook here


Acknowledgements


Allan Folting of Microsoft first showed me this technique, I am grateful for his insights.


Chris Webb has a series of excellent blog posts on MDX For PowerPivot Users, which are worth reading if this technique interests you.

PowerPivot BlogRoll – 6th January 2013



PowerPivot activity announced on Twitter for the week ending 6th January 2013.


 


Time To Name This Year’s Book! – bit.ly/TyZ5nX


Database Pros in Demand – bit.ly/UXN78m


[Commercial Webinar] Deep Dive into Business Intelligence in Excel 2013’s PowerPivot and Power View – bit.ly/115ls8J


Creating Linked Tables in PowerPivot for Excel – bit.ly/TyZ5nX


Excel 2013, Power View, Top 10 “long tail” and how DAX helpsbit.ly/YJTMkd

PowerPivot BlogRoll – 30th December 2012



PowerPivot activity announced on Twitter for the week ending 30th December 2012.


 


Templated Excel workbooks with PowerPivot and Macro’s – bit.ly/12VdWwP


Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel – bit.ly/UAX22u


PowerPivot – Importing Data from Different Data Sources into PowerPivot for Excel – bit.ly/131Qi0v


End of 2012 and news in 2013 for #PowerPivot, #ssas #tabular and BI – bit.ly/VpwWxI


Refreshing PowerPivot Data in SharePoint 2013 – bit.ly/131Q3Ti


[Video] Using PowerPivot with DataMarket Section of Windows Azure Marketplace – bit.ly/12Vd23e