using olap in mdx applications n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Using OLAP in MDX Applications PowerPoint Presentation
Download Presentation
Using OLAP in MDX Applications

Loading in 2 Seconds...

play fullscreen
1 / 23

Using OLAP in MDX Applications - PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Using OLAP in MDX Applications' - charles-houston


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
kevin s goff 30 seconds of shameless promotion
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

the agenda
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

showing a 52 week moving average in ssrs1
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

showing a 52 week moving average in ssrs2
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

an mdx rank across columns in a pivot table1
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

an mdx rank across columns in a pivot table2
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

sales for a day and for the previous day going back as far as 7 days
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

sales for a day and for the previous day going back as far as 7 days1
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

sales for a day and for the previous day going back as far as 7 days2
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

top n cities by sales within top y products by sales
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

top n cities by sales within top y products by sales2
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

using mdx snippets in a kpi dashboard
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

using mdx snippets in a kpi dashboard1
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

using mdx in analytic grids and analytic charts for a of category share
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

using mdx in analytic grids and analytic charts for a of category share1
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

using mdx in analytic grids and analytic charts for a of category share2
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

using mdx with excel
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

using mdx with excel1
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

recommended books links
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