1 / 10

MDX Scripting

Colorado PASSCamp 2006. MDX Scripting. Mike White Wall Street On Demand. Agenda. Quick Scripting Overview Project Context (You want to do what?) Code Samples Your Questions. Colorado PASSCamp 2006. Scripting Overview - Scope. Coordinate space Tuples (2004,Pear,Order Count) = 99

keefe
Download Presentation

MDX Scripting

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. Colorado PASSCamp 2006 MDX Scripting Mike White Wall Street On Demand

  2. Agenda • Quick Scripting Overview • Project Context (You want to do what?) • Code Samples • Your Questions Colorado PASSCamp 2006

  3. Scripting Overview - Scope • Coordinate space • Tuples • (2004,Pear,Order Count) = 99 • (2005) = 42 • Sets • (2003, {Apple, Orange}) = 11 • (Sales Amount, {Apple, Pear}) = 19.99 Colorado PASSCamp 2006

  4. Scripting Overview - Scope • Scope limits enclosed assignments to the subcube it describes Scope(2003, {Apple, Pear}); (Order Count) = 17; (Sales Amount) = 34.50; End Scope; • Examples use simplified syntax and numeric “formulas”, but most any functions work Colorado PASSCamp 2006

  5. Scripting Overview - Interface • Calculations Tab • Think of dropping formulas in Excel cells • Script vs. Form view • Metadata/Functions/Templates • Example – Simple calculation via form • What script does it generate? Colorado PASSCamp 2006

  6. Project Context • Began spring 2005, database work functionally complete at SQL Server 2005 RTM • Made lots false starts – hopefully interesting ones • Application tracks drug usage of a sampled population, extrapolates usage (and associated patterns) of US population Colorado PASSCamp 2006

  7. Project Context • Raw Data – as sampled. • Normalized Data –certain cases require factors to compensate for sampling error • Scaled Data – Factors scale data to US population based on incidence, sales, etc. Colorado PASSCamp 2006

  8. Sample 1 - Scaling • Review Cube Structure • How would you implement? • Where do you store the factors? • What does the calculation look like? • How could my first draft be improved? Colorado PASSCamp 2006

  9. Sample 2 - Normalizing • Applies to certain drugs and situations • Only drugs given by themselves • Ideas about how to incorporate complex join criteria in cube? Colorado PASSCamp 2006

  10. Lessons Learned • Look for ways to reduce the coordinate space for the problem you are addressing • Simplify calculations by adjusting dimensions when possible • Pre-calculate everything possible (additive measures) Colorado PASSCamp 2006

More Related