1 / 23

Using OLAP in MDX Applications

Using OLAP in MDX Applications. Kevin S. Goff. Kevin S. Goff: 30 seconds of Shameless Promotion. Developer/architect since 1987 Microsoft SQL Server MVP 2010 (C# MVP 2005-2009) Columnist for CoDe Magazine 2004-2008, 2010 Wrote a book, collaborated on a 2 nd book

charles-houston
Download Presentation

Using OLAP in MDX Applications

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. Using OLAP in MDX Applications Kevin S. Goff

  2. Kevin S. Goff: 30 seconds of Shameless Promotion • Developer/architect since 1987 • Microsoft SQL Server MVP 2010 (C# MVP 2005-2009) • Columnist for CoDe Magazine 2004-2008, 2010 • Wrote a book, collaborated on a 2nd book • Currently writing a 3rd book on Business Intelligence with SQL Server • Frequent speaker for .NET/Database/SharePoint User Group/community events • SQL Server/Business Intelligence Trainer/Courseware Author for SetFocus, LLC • kgoff@kevinsgoff.net • www.KevinSGoff.Net (Go to downloads area) MDX in OLAP Applications

  3. The Agenda • SQL Server Reporting Services Examples: • Showing a 52 week moving average where the parameter for year excludes the first year • Implementing an MDX Rank across columns in a PIVOT Table • Sales for a Day and for the previous day (going back as far as 7 days) • Top N cities by Sales within Top Y Products by Sales • PerformancePoint Server • Using MDX snippets in a KPI dashboard • Using MDX in Analytic GRIDs and Analytic Charts for a % of Category Share • Excel • Adding MDX Code to OLAP Pivot Tables using a free OLAP PivotTable Extension add-on • Recommended Books/Links MDX in OLAP Applications

  4. Showing a 52 week moving average in SSRS MDX in OLAP Applications

  5. Showing a 52 week moving average in SSRS • Can use the OLAP query designer, but must add 2 calculated members to determine 12-month avg MDX in OLAP Applications

  6. Showing a 52 week moving average in SSRS 2 different ways to calculate 12 month average, using MDX LASTPERIODS function. One uses an AVG (that ignores NULLs) and a second that uses a SUM and divides by 12 MDX in OLAP Applications

  7. An MDX Rank across columns in a PIVOT Table MDX in OLAP Applications

  8. An MDX Rank across columns in a PIVOT Table MDX code for the report uses the ORDER and FILTER to Rank against a named set. Also uses EXISTS to rank each subcategory within a Country MDX in OLAP Applications

  9. An MDX Rank across columns in a PIVOT Table with set [OrderedCountriesSubCategories] as ORDER( filter( [Customer].[Country].Children * [Product].[SubCategory].Children, [Internet Sales Amount] <> null), [Internet Sales Amount], DESC) member [SalesRank] as RANK( ( [Customer].[Country].Currentmember, [Product].[SubCategory].CurrentMember), EXISTS( [OrderedCountriesSubCategories], [Customer].[Country].CurrentMember)) select { [Internet Sales Amount], [SalesRank] } on columns, [OrderedCountriesSubCategories] on rows from [Adventure Works] MDX in OLAP Applications

  10. Sales for a Day and for the previous day (going back as far as 7 days) This goes beyond just using PREVMEMBER to get sales for prior day Will go back as far as 7 days MDX in OLAP Applications

  11. Sales for a Day and for the previous day (going back as far as 7 days) Uses MDX query parameters and references them in the MDX code MDX in OLAP Applications

  12. Sales for a Day and for the previous day (going back as far as 7 days) WITH MEMBER [SalesPriorBusinessDay] as ([Internet Sales Amount], tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency' MEMBER [Measures].[LastSaleDate] AS tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1).Item(0).name select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on columns, order(filter( [Customer].[City].Children, [Internet Sales Amount] > 500), [Internet Sales Amount], desc) on rows from [Adventure Works] where strtomember( @DateSelection) MDX in OLAP Applications

  13. Top N cities by Sales within Top Y Products by Sales Report allows us to get the top Y selling products, and for each product, give us the top N cities Uses the MDX GENERATE function MDX in OLAP Applications

  14. Top N cities by Sales within Top Y Products by Sales MDX in OLAP Applications

  15. Top N cities by Sales within Top Y Products by Sales with set [TopNProducts] as topcount( [Product].[Product].Children, @TopNProductCount, [Internet Sales Amount]) set [TopCitiesWithinTopProducts] as GENERATE ( [TopNProducts], ( [Product].[Product].CurrentMember, TOPCOUNT( [Customer].[City].Children, @TopNCityCount, [Internet Sales Amount] ) ) ) member [CityRank] as RANK( ( [Product].[Product].CurrentMember, [Customer].[City].CurrentMember), exists( [TopCitiesWithinTopProducts], [Product].[Product].CurrentMember)) member [Product Rank] as RANK( [Product].[Product].CurrentMember, [TopNProducts]) select { [Internet Sales Amount], [Product Rank], [CityRank]} on columns, [TopCitiesWithinTopPRoducts] on rows from [Adventure Works] where strtoset( @FiscalYear) MDX in OLAP Applications

  16. Using MDX snippets in a KPI dashboard KPI Dashboard that receives a date as a parameter (could be year, could be quarter, etc.) and displays all the hierarchical children for that date selection MDX in OLAP Applications

  17. Using MDX snippets in a KPI dashboard In the Filter Link between the date dropdown and the KPI scorecard, we can build an MDX connection formula, to take the date selection and retrieve the hierarchical children MDX in OLAP Applications

  18. Using MDX in Analytic GRIDs and Analytic Charts for a % of Category Share Chart and report allow a user to select a product in the hierarchy. Chart shows monthly sales and also sales % of parent. Grid at bottom shows entire parent ancestry. MDX in OLAP Applications

  19. Using MDX in Analytic GRIDs and Analytic Charts for a % of Category Share Analytic grid takes product parameter, grabs siblings, and also grabs ascendants. Grid also uses Hierarchize to take all the UNIONED data and display in hierarchical order When we write custom MDX code in PPS, we must declare parameters from filters manually MDX in OLAP Applications

  20. Using MDX in Analytic GRIDs and Analytic Charts for a % of Category Share SELECT [Dim Date Calendar].[Month Key].[Month Key].ALLMEMBERS ON COLUMNS, hierarchize( union( ascendants( <<ProductParm>> ), <<ProductParm>>.Siblings ) ) ON ROWS FROM [Jewel Destiny2008R2] WHERE ( <<YearFilter>>, <<GeoFilter>>, [Measures].[Product Sales as % Parent] ) MDX in OLAP Applications

  21. Using MDX with Excel • Suppose, when browsing an OLAP cube using Excel, we want to incorporate an “on-the-fly” MDX calculation • We can use OLAP PivotTable Extensions (free utility) • After installing, we can right-click on a PivotTable column and add MDX formulas • http://olappivottableextend.codeplex.com/ (32-bit) • http://olappivottableextend.codeplex.com/releases/view/46306 (64-bit) MDX in OLAP Applications

  22. Using MDX with Excel ([Measures].[Internet Sales Amount], PARALLELPERIOD( [Date].[Fiscal].[Fiscal Year], 1 ) ), format_string = '$#,###.##‘ Note: these formulas only reside in the spreadsheet – they are not written back to the OLAP database MDX in OLAP Applications

  23. Recommended Books/Links • Microsoft SQL Server 2008 MDX Step by Step • http://www.amazon.com/Microsoft-SQL-Server-2008-Step/dp/0735626189/ref=sr_1_1?ie=UTF8&qid=1291449547&sr=8-1 • MDX Solutions: With Microsoft SQL Server Analysis Services 2005 • http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080/ref=sr_1_4?ie=UTF8&qid=1291449896&sr=8-4 • On my website (www.KevinSGoff.net), I’ve covered some MDX-related topics: • http://kevin_s_goff.typepad.com/kevin_s_goff_weblog/2010/11/11-07-2010-setfocus-bisql-masters-program-tip-ssrs-2008ssrs-2008-r2-olap-reports-and-overwriting-mdx-parameters.html • http://kevin_s_goff.typepad.com/kevin_s_goff_weblog/2010/01/1-17-2010---setfocus-bisql-masters-program-tip-when-prevmember-alone-isnt-enough-in-mdx.html • http://kevin_s_goff.typepad.com/kevin_s_goff_weblog/2009/12/12-21-2009-reason-1-of-a-list-of-thousands-of-reasons-why-mdx-is-important.html MDX in OLAP Applications

More Related