Using Power View reports with Multidimensional modelsSivakumar Harinath Program Manager

DBI-B321

Demos

DemosDemos

Demos Demos

Demos Demos

Demos Demos Demos

AgendaOverviewMeasure groupsDimensions Calculated MembersImagesPerspective & TranslationPinned FiltersQ&A

Overview

Internet Explorer

Analysis Services

BI Semantic ModelTabular

SharePoint(2010 or 2013)

Reporting Services

Power ViewAnalysis Services

BI Semantic ModelMultidimensional

SQL Server Data Tools

SQL Server Data Tools

1Tabular

Metadata

2

DAX

Queries3Tabular Metadata

5

DAX Queries

6

4

BI Semantic Model

Data model

Business logic

Data access

BI Semantic Model: Architecture

ROLAP MOLAP VertiPaq DirectQuery

MDX DAX

Multidimensional

Tabular

Third-partyapplications

Reporting Services

(Power View)Excel PowerPivot

Databases LOB Applications Files OData Feeds Cloud Services

SharePointInsights

Queries MDX MDX/DAX

BI Semantic Model

Data model

Business logic

Data access

BI Semantic Model: Architecture

ROLAP MOLAP VertiPaq DirectQuery

MDX DAX

Multidimensional

Tabular

Third-partyapplications

Reporting Services

(Power View)Excel PowerPivot

Databases LOB Applications Files OData Feeds Cloud Services

SharePointInsights

Queries MDX/DAX MDX/DAX

BISM-MD Object Tabular ObjectCube Model

Cube Dimension Table

Attributes (Key(s), Name) Columns

Measure Group Table

Measure Measure

Measure without MeasureGroup Within Table called “Measures”

MeasuregroupCube Dimension relationship

Relationship

Perspective Perspective

KPI KPI

User/Parent-Child Hierarchies Hierarchies

Multidimensional-Tabular Mapping

Query Execution Architecture

Client Application

Analysis Services

Query Parser

Query Processor

sFE Caches

MDX Formula Engine

Storage Engine

sSE Caches Partition

Data QuerySE Evaluation Engine

Cube

Tabular Metadata

Metadata

Layer DAX Query Processor

Multi-dimension

al Metadata

1

DISCOVER_CSDL_METADATA 2

3

DAX Query 1

2

Dimension Queries

(Table Query)

MDX

Measure

45

6

7

9

8

3

Measure Groups and Measures

Siva Harinath

Dimension

Siva Harinath

Types of Calculated MembersChildren of All with a single real memberCalculated members on User Hierarchies

Additional constraintsSingle real member when dimension has more than one attributeAttribute cannot be key unless it’s the only attributeNot a parent-child attribute

Calculated Members

Calculated Member

Siva Harinath

Images

Siva Harinath

Perspectives &Translations

Siva Harinath

Power View enhancementReplicates and links a specific filter across all views in a reportChange one = change allCan be added or modified via the filter paneCan be added via special “rf” URL parameter

Parameter Syntax http://sharepoint/...<reportURL>...?rc:ViewMode=Presentation &rf=[Company].[Company ID] eq ‘MSFT’

Pinned Filters

Pinned Filter

Siva Harinath

MICROSOFT CONFIDENTIAL – INTERNAL ONLY

How multidimensional models are exposed in Power ViewCreated Power View reports against multidimensional models (a.k.a. cubes)Available in SQL Server 2012 SP1 CU4

– Released in May 2013– Download and use it!!!

Summary

Do I need to upgrade SSAS and SSRS?Yes

What MD features are not supported?Named SetsActionsCell security permission applied to connected user

What editions is this available?Enterprise and Business Intelligence Editions.

Can Power View in Excel 2013 connect to cubes?

Enabling this is top priority. Currently we do not have updates on timeframe.

Common Q & A

Related contentDBI-B209 Dynamite Data Visualization with Power View in Microsoft ExcelDBI-B331 Developing and Managing a BI Semantic Tabular Model in Microsoft SQL Server 2012 Analysis ServicesDBI-B315 Querying in DAX

Find Me Later At Ask the experts or the SQL Server booth

DBI-H204 Optimizing a Microsoft SQL Server 2012 Tabular BI Semantic ModelDBI-H205 Developing a Microsoft SQL Server 2012 Tabular BI Semantic Model Using SQL Server Data Tools

msdn

Resources for Developers

http://microsoft.com/msdn

Learning

Microsoft Certification & Training Resources

www.microsoft.com/learning

TechNet

Resources

Sessions on Demand

http://channel9.msdn.com/Events/TechEd

Resources for IT Professionals

http://microsoft.com/technet

Evaluate this session

Scan this QR code to evaluate this session.

© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows 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.