1 / 18

Enriching BI Semantic Models using Data Analysis Expressions (DAX) DBI408

Enriching BI Semantic Models using Data Analysis Expressions (DAX) DBI408. Ashvini Sharma & Kasper de Jonge Program Managers Analysis Services. Agenda. PowerPivot DAX Calculations DAX Queries. DAX in Denali. demo. Calculating Rank. List of Values. Value to rank. Rank. 6. 2.

hosanna
Download Presentation

Enriching BI Semantic Models using Data Analysis Expressions (DAX) DBI408

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. Enriching BI Semantic Models using Data Analysis Expressions (DAX)DBI408 Ashvini Sharma & Kasper de Jonge Program Managers Analysis Services

  2. Agenda • PowerPivot • DAX Calculations • DAX Queries

  3. DAX in Denali demo

  4. Calculating Rank List of Values Value to rank Rank 6 2

  5. Calculating Rank List of Values Value to rank Rank Rank Profit for current country

  6. Calculating Rank List of Values RANKX( TABLE • ALL(Countries), [Profit], … EXPRESSION

  7. Calculating Rank List of Values Value to rank Rank Rank [Profit]

  8. Recap • Tables & Relationships • Calculated Columns • “Automatic” measures • Filter Context & custom measures • Overriding the filter context • Rank, Time Intelligence, Multiple relationships, Parent Child

  9. DAX for the BI Pro • Introduced with PowerPivot for Excel • Will be supported for Tabular projects in Denali • Use DAX in row level security including dynamic security • USERNAME() function in DAX

  10. BI Semantic Model: Architecture Third-party applications Reporting Services SharePoint Insights Excel PowerPivot BI Semantic Model Multi- dimensional Data model Tabular Business logic and queries MDX DAX Direct Query Data access ROLAP VertiPaq MOLAP Databases LOB Applications Files OData Feeds Cloud Services

  11. Querying the BI Semantic Model with DAX • DAX query results are always a table (rowset) • SQL 11 adds DAX functions that return tables • Although tabular, syntax is different than SQL

  12. DAX Queries demo

  13. What’s new in Denali • SWITCH • TOPN • Statistical functions: STDEV & VAR • HASONEVALUE

  14. In Review: DAX (for Denali) • Low barrier to entry • Scales to complex analytical needs • SQL 11 simplifies formula authoring • DAX as a Query language

  15. Required Slide Speakers, please list the Breakout Sessions, Interactive Discussions, Labs, Demo Stations and Certification Exam that relate to your session. Also indicate when they can find you staffing in the TLC. Related Content • DBI206 | What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot • DBI208 | Abundantly "Crescent": Demos Galore DBI274-HOL | Loading and Preparing Data in the Microsoft PowerPivot for Excel 2010 Client • DBI373-HOL | Defining DAX Calculations with Microsoft PowerPivot for Excel 2010 • Demo station 1 – Microsoft Business Intelligence Managed Self-Service BI • http://blogs.msdn.com/powerpivot

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

  17. © 2011 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