Getting to Know the BI Semantic Model

Level: Intermediate

March 28, 2013

Andrew BrustCEO and Founder

Blue Badge Insights

• CEO and Founder, Blue Badge Insights• Big Data blogger for ZDNet• Microsoft Regional Director, MVP• Co-chair VSLive! and 17 years as a speaker• Founder, Microsoft BI User Group of NYC

– http://www.msbinyc.com

• Co-moderator, NYC .NET Developers Group– http://www.nycdotnetdev.com

• “Redmond Review” columnist for Visual Studio Magazine and Redmond Developer News

• brustblog.com, Twitter: @andrewbrust

Meet Andrew

Andrew’s New Blog (bit.ly/bigondata)

Read all about it!

What is the BI Semantic Model

• It’s a relational model for analytical data• Similar to an OLAP cube

– Because it facilitates dimensional/drill-down analysis– (Some people would even consider BISM a cube)

• Similar to a data warehouse– Because it accommodates a star schema– Because it uses columnar technology used in many data

warehouse appliances

• But it’s different– Data is not stored dimensionally– Star schema not required– Can function as a model layer only, storing no physical

data

Agenda

• BISM Overview• BISM in Excel 2013• PowerPivot• SSAS Tabular• Power View

BISM OVERVIEW

Genesis

BISM

Competing

products (BO, TM1)

Report Builder

1.0 report models

Analysis Services

cubes

BISM CompanionsxVelocity in-memory technologies

• Manifested in storage engines for PowerPivot and SSAS Tabular mode

Data Access eXpressions (DAX)

• Analytical function library with Excel-like syntax• Successor to MultiDimensional Expressions (MDX)

PowerPivot

• For designing, storing and querying models

Power View

• For reporting on and visualizing BISM data

Excel 2013

• Which creates BISM models implictly

Motivations

• Self Service• In-memory performance• Agility• More business-friendly• More relational-friendly

Column-Oriented Stores• Imagine, instead of:

• You have:

• Perf: values you wish to aggregate are adjacent• Efficiency: great compression from identical or nearly-identical

values in proximity• Fast aggregation and high compression means huge volumes

of data can be stored and processed, in RAM

Employee ID Age Income

1 43 90000

2 38 100000

3 35 100000

Employee ID 1 2 3

Age 43 38 35

Income 90000 100000 100000

BISM IN EXCEL 2013

BISM Embedded in Excel 2013

• Importation of data into a sheet implicitly adds it to a columnar, in-memory BISM model– The model can then be queried via PivotTabes, charts

and Power View– “Import millions of rows from multiple data sources”– “Create relationships between data from different

sources, and between multiple tables in a PivotTable”– “Create implicit calculated fields”– “Manage data connections”

• This means most “spreadmarts” are now BISM models. This is huge.

Rules for Model Building

• Sometimes data is not immediately added– But building a PivotTable or Power View report on it

will cause it to be added

• Even a manually created Excel table may be added

• “Add to Data Model” button in PowerPivot tab of ribbon allows adding explicitly

• Using PowerPivot window unnecessary, but still required for more advanced modeling.– As long as you have Office ProPlus

Data Explorer

• Beta Excel add-in for importation of structured and unstructured data– Facebook– Hadoop/HDFS– Active Directory– SharePoint– Files in folder, XML– Improved Web/HTML

• Because of implicit model and linked tables, Data Explorer effectively opens up new data sources for the BISM

Embedded Models

POWERPIVOT

What is PowerPivot?

• Excel + Analysis Services + SharePoint• Mitigates many spreadmart pitfalls:

– Use Analysis Services (AS) as a hidden engineInstead of no engine

– Share via SharePoint, accessible by all AS clientsInstead of “deploying” via email

– Formal data refresh on serverSo data doesn’t get stale, and users don’t have to make effort at updating

– Allow IT to monitorSo it’s not all rogue

– Provide path to more rigorous implementationsCan be upsized to Analysis Services

How do you get PowerPivot?

• For Excel 2010:– Download the add-in from www.powerpivot.com– Get 32-bit or 64-bit version, depending on your

version of Office (not Windows)– Run the installer

• For Excel 2013:– Included in product, but only with:

Office Pro Plus retail product (under volume licensing)Office 365 Pro Plus or Enterprise E3/E4 subscriptions

– It’s still an add-in, installed but not activated by defaultSame goes for Excel version of Power View

Data Import

• Relational databases– SQL Server (including SQL Azure!), Access– Oracle, DB2, Sybase, Informix– Teradata– “Others” (OLE DB, including OLE DB provider for ODBC)

• OData feeds, incl. R2/2012 Reporting Services, Azure DataMarket, WCF Data Services (Astoria), SharePoint 2010+ lists, Visual Studio LightSwitch

• Excel via clipboard, linked tables• Filter, preview, friendly names for tables/columns

Calculated Columns and DAX• Formula-based columns may be created• Formula syntax is called DAX (Data Analysis

eXpressions).– Not to be confused with MDX or DMX. Or DACs.

• DAX expressions are similar to Excel formulas– Work with tables and columns; similar to, but distinct from, worksheets

and their columns (and rows)

• =FUNC('table name'[column name])• =FUNCX('table name', <filter expression>)• FILTER(Resellers,[ProductLine] = "Mountain")• RELATED(Products[EnglishProductName])• DAX expressions can be heavily nested

PowerPivot Guidebook

Table tabs

View data in Excel

DAX formula bar

Relationship indicator

Import data fromalmost anywhere

Sort and filter

Calculatedcolumnentry

Modeling the Data KPIs

Calculations

Data and Diagram views

Calculationarea

Calculationformula

Sort one column by another

Diagram ViewDefault Aggregations Special Advanced Mode

Reporting

properties

Hierarchies

Hide specific

columns andtables

Createrelationshipsvisually

KPIs

Perspectives

Calculations

PowerPivot Client

Excel Services

• A component of SharePoint Server 2007+; requires Enterprise CAL

• Allows export of workbook, worksheet, or individual items to SharePoint report library– Works great for PivotTables and Charts!– Also for sheets with CUBExxx formulas or conditional

formatting-driven “scorecards”

• Content can be viewed in browser– Excel client not required– Drilldown interactivity maintained– Rendered in pure HTML and JavaScript– Parameterization supported

PowerPivot Server

• Publish to Excel Services• Viewing and interacting• Data Refresh• Treating as SSAS cube

– 2008 R2 version: URL to .xlsx as server name– 2012 version: use POWERPIVOT named instance and treat

just like SSASDb name is GUID-based; best to discover it

– Use Excel, Reporting Services as clientsAnd now Power View too…more later

The IT Dashboard

Increase IT efficiency:Familiar Technologies for Authoring, Sharing, Security, and ComplianceCustomizable IT DashboardVisualize usage with animated charts

Simplify management of SSBI content usingIT Operations Dashboard for SharePoint

PowerPivot Server

SSAS Tabular

Analysis Services Tabular Mode

• SSAS Tabular Mode is the enterprise/server implementation of PowerPivot

• You must have a dedicated Tabular mode SSAS instance

• Tabular SSAS projects: Anlaysis Services project tools gone PowerPivot– Implements equivalent tooling to PowerPivot Window– Can create an SSAS Tabular database project by

importing an Excel workbook with PowerPivot model

• SSAS Tabular models support partitions and roles

SSAS Tabular Project in SSDT-BI(i.e. Visual Studio)

SSAS Tabular projectmenus and toolbar

Calculations area and formula bar

Reporting properties in Properties window

DirectQuery Mode

• In DQ mode, model defines schema, but is not used for data

• Queries issued directly against source

• Similar to ROLAP storage for conventional cubes

• Combine with xVelocity Columnstore indexes for fast, real-time querying

SSAS Tabular Mode

POWER VIEW

What is Power View?

• Ad hoc reporting. Really!• Analysis, data exploration• Data Visualization• In Silverlight, in the browser, in SharePoint• And now in Excel 2013, too

– ProPlus edition required– Still Silverlight

• Is actually based on SSRS– Power View makes a special RDL file

• Competitive response– To QlikView, Tableau, Tibco Spotfire

Power View Data Sources

• Power View works only against BISM• DirectQuery mode supported, however• Support for SSAS Multidimensional cubes

coming• Gist is this: Power View emits DAX

expressions– PowerPivot and SSAS Tabular support DAX– SSAS Multidimensional will support DAX soon

Create a Power View Report

• In Excel 2013, click Power View button on Insert tab of ribbon– Make sure add-in is activated

• In SharePoint, click “Create Power View Report” button or option on workbook in PowerPivot Gallery

• For SSAS tabular model, create BISM data source, then click its “Create Power View Report” button or option– BISM data sources can point to PowerPivot workbooks

too, if you want.

• Can export to PowerPoint and view there

Power View!

Variety of visualizationsand data formats

Field list, as with PivotTables

Data regions pane,as with PivotTables

Maximize, Filter

Maximize chart

Filter area

Power View Basics

Scatter/Bubble Charts

• Allow for several measures• Features a “play” axis which can be

manipulated through a slider or animated• Excellent way to visualize trends over time

Advanced Properties

• Setting the representative column and image tells Power View how to summarize your data, and show stored images

• Other properties tell it about key attributes, default aggregations and more

• These are all BISM features; they are merely being exposed by PowerPivot and SSAS Tabular– They are the justification for the term “semantic”

Advanced Power View

Vocabulary--Multidimensional--

MOLAP

UDM

Cube

--BISM--

xVelocity Columnstore Technology

VertiPaq

BISM

IMBI

Tabular

Thank You!

• Email• andrew.brust@bluebadgeinsights.com

• Blog:• http://www.zdnet.com/blog/big-data

• Twitter• @andrewbrust on twitter