12: A Few Ideas: PowerPivot and DAX Solutions
UPC Code Recognizer In None
Using Barcode Control SDK for Microsoft Office Control to generate, create, read, scan barcode image in Microsoft Office applications.
UPC Code Drawer In None
Using Barcode generation for Microsoft Office Control to generate, create GTIN - 12 image in Microsoft Office applications.
Figure 12-32 PowerPivot data not in pivot table
UPCA Recognizer In None
Using Barcode reader for Microsoft Office Control to read, scan read, scan image in Microsoft Office applications.
UPC-A Supplement 5 Generator In C#.NET
Using Barcode maker for VS .NET Control to generate, create Universal Product Code version A image in .NET framework applications.
you start using cell references Please note that adding or removing entries in the PowerPivot Field List drop-zones may invalidate any cell references you may have or lose your CUBE() function formulas altogether If you are interested, you can view the MDX behind CUBERANKEDMEMBER() and CUBEMEMBER() To do so, view the Immediate Window in VBA (press alt-f ), type the following command, and press enter:
UPC A Maker In VS .NET
Using Barcode printer for ASP.NET Control to generate, create Universal Product Code version A image in ASP.NET applications.
Universal Product Code Version A Maker In .NET
Using Barcode printer for VS .NET Control to generate, create UPC-A image in .NET applications.
Making GTIN - 12 In VB.NET
Using Barcode printer for .NET framework Control to generate, create UPC Code image in VS .NET applications.
Scanning Data Matrix In None
Using Barcode reader for Software Control to read, scan read, scan image in Software applications.
Code128 Scanner In VS .NET
Using Barcode scanner for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
Generating GTIN - 128 In None
Using Barcode creator for Font Control to generate, create EAN 128 image in Font applications.
Self-joins are not supported in PowerPivot Here is the SQL to denormalize the Northwind Employees table:
Linear Creator In VB.NET
Using Barcode generator for .NET Control to generate, create 1D Barcode image in VS .NET applications.
Code 128C Generation In Java
Using Barcode encoder for Android Control to generate, create ANSI/AIM Code 128 image in Android applications.
select EEmployeeID, ELastName, EFirstName, MLastName + ', ' + MFirstName as Manager from employees as E left outer join employees as M on EReportsTo = MEmployeeID
GS1 - 13 Decoder In Visual Basic .NET
Using Barcode reader for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
ECC200 Maker In Java
Using Barcode printer for Java Control to generate, create DataMatrix image in Java applications.
Now you can analyze sales by employee by manager This example is also in Appendix A about SQL queries for PowerPivot, but included here in case you miss it in Appendix A
Creating Code 128A In Java
Using Barcode encoder for Java Control to generate, create Code128 image in Java applications.
Generating Data Matrix ECC200 In None
Using Barcode encoder for Microsoft Excel Control to generate, create Data Matrix 2d barcode image in Office Excel applications.
Prac tical PowerPivot & DAX Formulas for Excel 2010
Code 3 Of 9 Printer In VS .NET
Using Barcode generator for .NET framework Control to generate, create Code 39 Extended image in .NET applications.
Barcode Drawer In None
Using Barcode creation for Font Control to generate, create bar code image in Font applications.
Drawing Bar Code In None
Using Barcode printer for Office Excel Control to generate, create bar code image in Microsoft Excel applications.
Generate UPC - 13 In Visual Basic .NET
Using Barcode creation for .NET Control to generate, create EAN-13 Supplement 5 image in .NET framework applications.
Appendix C is an introduction to writing DMX queries for PowerPivot A DMX query allows you to return data from an SSAS data mining model or mining structure There is another way to use data mining data without having to write DMX If you download the Data Mining Add-in for Excel, you can generate data mining data in a worksheet with no writing of DMX yourself required You can then import or link the worksheet into PowerPivot You have to use the Table Tools/Analyze ribbon and not the Data Mining ribbon the latter ribbon uses SSAS graphical interfaces and does not create data in a worksheet The add-in is downloadable from wwwsqlserverdataminingcom As of this writing, only the Excel 2007 version was available, but it should work with Excel 2010 (32-bit only)
Very early in this book, there was a discussion of importing data from an SSRS report into a PowerPivot model You can also export from an SSRS report into a PowerPivot model use the Export to Data Feed button, while viewing a report in Report Manager, to do so You can even reverse the whole process and base an SSRS report on a PowerPivot model To do this, you must first have published your PowerPivot for Excel model to PowerPivot for SharePoint
If you want to share your PowerPivot for Excel models and pivot reports, you can publish from Excel to SharePoint Go through File | Save & Send | Save to SharePoint to accomplish this Naturally, for this to work you have to have SharePoint (2010 not 2007), and that means a 64-bit SharePoint server In addition, you will need SQL Server 2008 R2 and SSAS installed in SharePoint-integrated mode Further steps involve enabling PowerPivot for SharePoint and Excel Services within SharePoint administration PowerPivot for SharePoint uses SSAS 2008 R2 in integrated mode to look after the data Excel Services is responsible for rendering the workbook containing the PowerPivot pivot reports for thin clients, such as Internet Explorer
Is It Really a Cube
I hesitated before adding this topic how to see PowerPivot as a cube Please do not try this on an important PowerPivot model if you change the cube design, you can easily invalidate the model! Copy an Excel workbook containing a PowerPivot model Change the file extension from xlsx to zip Unzip the zip file In the folder called xl, open the folder called customData In this last folder is a file called item1data this is
12: A Few Ideas: PowerPivot and DAX Solutions
the cube data behind PowerPivot Copy item1data to your SSAS backup directory and change the file extension from data to abf (Analysis Services backup file) You can now restore the backup to an SSAS database and cube this only works with SSAS 2008 R2 in SharePoint-integrated mode (or in-memory VertiPaq mode)
How Old Is Nancy
Many years ago, when people learned SQL for SQL Server with the Northwind database, there was a classic query It was called Nancy married the CEO The idea was to practice the SQL Update statement and change Nancy s Title from Ms to Mrs and her LastName from Davolio to Fuller If you look at the self-join on the Employees just above, Andrew Fuller has no manager he s the boss! This, possibly, will be the DAX equivalent of that SQL classic It s called How old is Nancy Here s the DAX formula to work out age (on the Northwind Employees table):
It seems a shame to end a book on something that doesn t always give the correct result that is, if you try it any time of the year apart from later on in December Here s the one that does work (both results are shown in Figure 12-33):
=IF(MONTH(TODAY())>MONTH(Employees[BirthDate]), YEAR(TODAY())-YEAR(Employees[BirthDate]), IF(AND(MONTH(TODAY())=MONTH(Employees[BirthDate]), DAY(TODAY())>=DAY(Employees[BirthDate])), YEAR(TODAY())-YEAR(Employees[BirthDate]), (YEAR(TODAY())-YEAR(Employees[BirthDate]))-1))
Can you find a more elegant way of calculating Nancy s age That s it, some nice DAX to finish Well done if you got this far! Finito (oh, maybe apart from just three appendixes)