1 / 48

Required Slide

Required Slide. Required Slide. SESSION CODE: BIU02. Budgeting Solution Built with PowerPivot. Alberto Ferrari BI Architect and Consultant SQLBI.COM. Who am I?. alberto.ferrari@sqlbi.com Independent consultant 10+ years on SQL Server 5+ years on BI & OLAP Book author

zebulon
Download Presentation

Required Slide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Required Slide

  2. Required Slide SESSION CODE: BIU02 Budgeting Solution Built with PowerPivot Alberto Ferrari BI Architect and Consultant SQLBI.COM

  3. Whoam I? • alberto.ferrari@sqlbi.com • Independent consultant • 10+ years on SQL Server • 5+ years on BI & OLAP • Book author • Expert Cube Development with Microsoft SQL Server Analysis Services • PowerPivot for Excel 2010:Give your data a meaning • Founder of SQLBI.COM

  4. Agenda • From the userpoint of view… • Focus on common PowerPivotdata models • Ratios and Percentages • Banding (discretization) • Simulations (CourierSimulations) • Non additive measures (Current account balance) • Dynamic Sets • In the meantime • Some insightsinto the DAX programminglanguage

  5. Ratios and Percentages How to compute ratios and percentages

  6. Whatis a filtercontext? • Eachcell, in a pivot table, defines a filtercontext Duringcellevaluation, the whole database isfiltered with the filtercontext, rowsoutsideit are notconsidered. (Australia, 2001)

  7. The background • A ratio need to «exit» from the filtercontext Ratio = divide the «part» over the «whole» But…The filtercontexthides the «whole»… (Australia, 2001) (2001)

  8. The solution: change the filtercontext with ALL • Weneed to define a new measure • Based on CALCULATE • We create e new context for a specificdimension • In order to makeitconsiderall the values for thatdimension Perc = SUM(FactInternetSales[SalesAmount]) / CALCULATE (SUM (FactInternetSales[SalesAmount]),ALL (DimSalesTerritory[SalesTerritoryCountry]) )

  9. Ratios and Percentages Excel sheet with ratios and percentages

  10. Banding How to handleanalysis by discretization

  11. Analysis of product sell price • Product pricechanges over time, due to • Discounts • Price variations • Price is a continuousdimension • Leading to a very high fragmentation • Classicalsolution: BANDING • From 0 to 100 USD • From 101 to 500 • … • Very common pattern, even with SSAS

  12. Banding in PowerPivot: the quick and dirtysolution Evenifthisworks… a better data model would be welcome! = IF ( FactResellerSales[DiscountedPrice] <= 5, "01 LOW", IF ( FactResellerSales[DiscountedPrice] <=30, "02 MEDIUM", IF ( FactResellerSales[DiscountedPrice] <=100, "03 MEDIUM", IF ( FactResellerSales[DiscountedPrice] <= 500, "04 HIGH", "05 VERY HIGH"))))

  13. Banding: a data driven model SELECT P.BandName, SUM (S.ExtendedAmount) FROM dbo.FactResellerSales S JOIN PriceBands P ON S.UnitPrice BETWEEN P.FromPrice AND P.ToPrice GROUP BY P.BandName

  14. Banding data model with PowerPivot We can leverage BAND Expansion with a different data model SELECT P.BandName, SUM (S.ExtendedAmount) FROM dbo.FactResellerSales S JOIN PriceBands P ON S.UnitPrice = P.Price GROUP BY P.BandName

  15. PowerPivot first solution: Band Expansion • PowerPivothas no BETWEEN… • Thusweneed Band Expansion • Weneed… • A configurationsheet with band configuration • Script to expand the configurationtable • Create a new linkedtable • Convertpricesintointegervalues • Setup the relationship • Verysimplesolution, probably the first one

  16. Band Expansion Band Expansion, first trial

  17. Banding with DAX • We can leverage DAX to implement BETWEEN • No need to performtableexpansion • Usage of FILTER to find the correct band • Usage of MAXX to convert a tableinto a value BandName=MAXX (FILTER (PriceBands, FactResellerSales[DiscountedPrice] >= PriceBands[MinPrice] && FactResellerSales[DiscountedPrice] < PriceBands[MaxPrice] ), [PriceBand])

  18. MAXX doesnot work with strings… • MAXX worksonly with dates and numbers, no strings! • Compatibility with Excel??? • Adda new numeric code • Use MAXX to retrieve the numeric code • Create a calculatedcolumn with the code • Set relationship on the code to find the band name • Tricky… yetitworks fine and doesnotneed VB scripts

  19. Banding with DAX First DAX solution

  20. Banding with CALCULATE • The keyis CALCULATE • Changethe filtercontext • Works with anyexpression • Leverages CALCULATE, VALUES and FILTER together = CALCULATE(VALUES (PriceBands[PriceBand]),FILTER (PriceBands, FactResellerSales[DiscountedPrice] >= PriceBands[MinPrice] && FactResellerSales[DiscountedPrice] < PriceBands[MaxPrice] ))

  21. Simulation Whatifwewant to choose a new courier?

  22. The background • Wehave some courierproposals • Parameters are • Weight • Destination • Resultis «Freight» • Weneed to choose the best one • Wecarry on a simulationbased on the pastshipments • Twoproblems • Determine the finalweight of a shipment • Create a goodsimulationenvironment

  23. Analysis of the data Letus take a brief tour on the source data

  24. Compute the shipmentweight • For eachshipment • Sum up the detailrows • Using a standard many to onerelationship • And the function RELATEDTABLE = SUMX(RELATEDTABLE(OrderDetails),OrderDetails[TotalWeight])

  25. Freight for eachcourier • For eachcourier • New calculatedcolumn • Leverage of FILTER to find the right freight • And of MAXX to convert a tableinto a scalar value(yes, itworks with numbers! ) =MAXX( FILTER( 'Couriers', [Country] = 'Orders'[Country] && [MinWeight] <= 'Orders'[Weight] &&'Orders'[Weight] < [MaxWeight] && [Courier] = "SpeedyMail"), 'Couriers'[Freight])

  26. Using CALCULATE weget a more elegantsolution • Using CALCULATE • Ifconfigurationsheetcontainsduplicates Weget an error • Cleanersolution, yet more complicated to understand =CALCULATE( VALUES( 'Couriers'[Freight] ), FILTER( 'Couriers', [Country] = 'Orders'[Country] && [MinWeight] <= 'Orders'[Weight] &&'Orders'[Weight] < [MaxWeight] && [Courier] = "Blu Express") )

  27. Simulation First solution to the simulation pattern

  28. Whatifwehavemanycouriers? • Onecalculatedcolumn for eachcourier • Manycolumns • Data model needs to be updatedbased on data • Soonwewill introduce errors • Letus create a new solution • New data model • Based on many to manyrelationship • Problems • PowerPivotdoesnothandlemany to manyrelationships • Less intuitive, yetverypowerful

  29. Data model with Many to Many • Bridge table • With the calculationparameters • Country • Weight • Tworelationships • With Orders • With PriceList • Problems • PowerPivotdoesnothandlemany to manyrelationships • Relationshipbased on twocolumns • Need to create the bridge • And to expand the configuration

  30. Bridge creation and expansion • Similar to the bandingsolution • Easilysolved with some VbScript code • No support in DAX or PowerPivot for this

  31. Relationship with twocolumns • PowerPivothandlesrelationshipsbased on onecolumnonly • Evenifwe live in 2010… • And the relational model is 41 yearsold • Solution • Create a new calculatedcolumn to hold the relationship • On alltables. 

  32. The DAX Formula • Far from being easy • Usesevaluationcontexts: • FilterContext • RowContext • Uses CALCULATE to transform a ROW Contextinto a FILTER Context =SUMX( Orders, CALCULATE ( IF( COUNT (PriceList[Freight]) = 1, VALUES (PriceList[Freight]), BLANK () ) ) )

  33. Simulation with many to many Solution of the simulation pattern with Many To Manyrelationships

  34. Semi Additive Measures How to handle semi additive measures with PowerPivot

  35. Semi Additive Measure - Definition • Additive Measure • Aggregates with SUM over alldimensions • Non Additive Measure • Aggregates with differentfunction over alldimensions • Example: average of the sale price • Semi Additive Measure • Aggregates with SUM over some dimensions • Aggregates with differentfunction over otherdimensions • Time is the standard exception for aggregations • Examples • Warehousestocking • Current account balance

  36. Example: current account balance • Monthlevelcorrect • Quarterlevelwrong • Yearlevelwrong

  37. Non additive measures • Change the aggregationfunction • Directly inside PowerPivot • We can do it • With the userinterface • Defining a new measure • Wecannot model the aggregationfunction inside the database

  38. Semi additive measures • Aggregationfunctiondepend on the slicer • In the example • Weshould aggregate for LastChild over time • Butweshould use SUM for the otherdimensions

  39. Semi Additive Measures: Data Model • Weneed to define a time dimension • Itisnotrequired by default • Evenifattributeconsolidationcomes from itsadoption • In this situation itismandatory • New measure: • CALCULATE: to set the filter • LASTDATE: to find the last child LastBalance = CALCULATE (SUM(Balances[Balance]), LASTDATE(Date[Date]))

  40. Semi Additive Measures Excel sheetimplementing semi additive measures

  41. Dynamic Sets Here wediscover the underlying SSAS cube in PowerPivot. 

  42. Whatis a set • Handled by Excel • Static Set • Set of tuples (i.e. filtercontexts) • Uses standard MDX code or User Interface • User interface… works, butitcouldhavebeen more userfriendly • Dynamic Set • Need to be written with MDX • And doingit… • Youwilldiscover the SSAS cube inside PowerPivot

  43. Dynamic Sets Excel sheetworking with static and dynamic sets

  44. Links • PowerPivothttp://www.powerpivot.com • DAX Introductionhttp://blogs.msdn.com/powerpivot/archive/2010/04/05/dax-data-analysis-expressions-measures-in-powerpivot.aspx • Marco Russo’s Bloghttp://www.sqlblog.com/Blogs/marco_russo/default.aspx

  45. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

  46. Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session. Related Content • BIU302 – Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX • BIU05-INT – DAX Patterns in PowerPivot • BIU02-HOL – Defining DAX Calculations with MS PowerPivot for MS Excel 2010 • Product Demo Stations

  47. Required Slide Complete an evaluation on CommNet and enter to win!

  48. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

More Related