Download - Real-world BISM in SQL Server 2012 SSAS
![Page 1: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/1.jpg)
Real-World SQL Server 2012 Analysis Services BISM models
Lynn LangitLynn LangitData Wrangler
Level: Intermediate
![Page 2: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/2.jpg)
Agenda – About the BI Semantic ModelAgenda – About the BI Semantic Model
![Page 3: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/3.jpg)
Better BI => Better ReportingBetter BI => Better Reporting
![Page 4: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/4.jpg)
Better BI => New Storage/Query/DisplayBetter BI => New Storage/Query/Display
![Page 5: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/5.jpg)
What is the BISM?What is the BISM?
![Page 6: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/6.jpg)
SQL Server Analysis Services ModesSQL Server Analysis Services Modes
• UDM (Multidimensional / Data Mining)– Also called OLAP cubes
• BISM (Tabular)– Business Intelligence Semantic Model
• PowerPivot for SharePoint
![Page 7: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/7.jpg)
Why Use BISM?Why Use BISM?
• Quick and simple to set up (flexible model)– Fast – in memory by default w/Vertipaq
– Connects to many types of data - flexible
– Overcomes the limits of PowerPivot
– Granular (role-based) security model
• Integrates with PowerView (data source in SPS)
![Page 8: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/8.jpg)
How to get started with xVelocityHow to get started with xVelocity
![Page 9: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/9.jpg)
Data Sources for BISMData Sources for BISM
![Page 10: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/10.jpg)
External Data - MarketsExternal Data - Markets
• Windows Azure Marketplace• DataMarket.com• Factual.com• Freebase.com
![Page 11: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/11.jpg)
When to use the BISM?When to use the BISM?
![Page 12: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/12.jpg)
How to Create / View models for BISM?How to Create / View models for BISM?
• Design in SSDT (or import from PowerPivot)• Open in Excel, SharePoint or PowerView• Can be used as data sources for SSRS in
SharePoint….
![Page 13: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/13.jpg)
Demo Demo Creating a Model
in Power Pivot
![Page 14: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/14.jpg)
Parts of BISM Parts of BISM
• Tabular Model Project in SSDT• New or Import• BISM – new or from PowerPivot via Excel
• Workspace Database (in memory by default)• Views in BISM
• Data & Calculation• Diagram• Toolbars—create KPI, etc…• Perspectives are supported as well
![Page 15: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/15.jpg)
Demo Demo Enhancing the model
![Page 16: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/16.jpg)
Enhancing ModelsEnhancing Models
• Sort or Filter Table data• Add Relationships
(diagram view)• Add Hierarchies (diagram
view)• Adding Calculated
Columns via DAX• Adding Measures and
KPIs via DAX• Improving Usability for
clients
![Page 17: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/17.jpg)
Diagram View of BISM in SSDTDiagram View of BISM in SSDT
• Manage Relationships• Create Hierarchies
![Page 18: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/18.jpg)
What is DAX - Data Analysis ExpressionsWhat is DAX - Data Analysis Expressions
• Data-expression Query Language (Excel-like simplified version of MDX
• Over 170 functions (64 functions for aggregation)• Ability to reference data in other tables in the model
(sumx, averagex, related [many-to-one], relatedtable [one-to-many] etc…)
![Page 19: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/19.jpg)
When to Use DAX with BISM modelingWhen to Use DAX with BISM modeling
![Page 20: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/20.jpg)
How to use DAX with BISM modelingHow to use DAX with BISM modeling
• Define formula & Add to model• DAX query language & query plans in 2012
– Uses ‘Evaluate’
• Samples– = SalesTable [SalesTotalColumn] – SalesTable [SalesCostColumn]
– = FORMAT (SalesTable [SalesDate], “MMM”)
– = RELATED (DimCustomerSales[SalesAmount]) – RELATED (DimCustomerCosts[SalesCost])
Relationship must be defined between tables when using RELATED
![Page 21: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/21.jpg)
Enhancing the Model – Advanced ViewEnhancing the Model – Advanced View
![Page 22: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/22.jpg)
Enhancing the Model – Advanced View 2Enhancing the Model – Advanced View 2
• Set the Table Behavior– Row ID
– Uniqueness
– Default Label
– Default Image
![Page 23: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/23.jpg)
Tips for creating usable modelsTips for creating usable models
• Create Hierarchies• Hide unneeded columns from client tools• Rename columns to natural language• Add relations or Add perspectives• Format data in the model, i.e. percentages…• Use DAX to remove nulls / errors• Use a TimeTable
– Can use ‘mark as date table’
– Cam create a separate date table
![Page 24: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/24.jpg)
Complex DAX exampleComplex DAX example
![Page 25: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/25.jpg)
Understanding DAX in ProfilerUnderstanding DAX in Profiler
![Page 26: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/26.jpg)
Demo Demo Adding DAX
to a BISM model
![Page 27: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/27.jpg)
Creating BI projects using SSDTCreating BI projects using SSDT
![Page 28: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/28.jpg)
BISM Models and ExcelBISM Models and Excel
• Can be created in Excel (and imported into SSDT)• Can be viewed in Excel (PivotTables) while modeling
in SSDT
![Page 29: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/29.jpg)
Demo Demo Creating a BISM Model for
SSAS using
SQL Server Data Tools
(SSDT)
![Page 30: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/30.jpg)
BISM Normalizer = DiffTool for BISMBISM Normalizer = DiffTool for BISM
3rd party tool, from CodePlex - here
![Page 31: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/31.jpg)
Adding Security RolesAdding Security Roles
• Map groups to BISM roles
![Page 32: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/32.jpg)
Understanding BISM structuresUnderstanding BISM structures
![Page 33: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/33.jpg)
Deployment of BISM in SSDT to SSASDeployment of BISM in SSDT to SSAS
• Query Mode default is ‘In-Memory’• Default Cube Name is Model.bim
![Page 34: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/34.jpg)
About Query ModesAbout Query Modes
• InMemory is the default• Query Mode (SSDT) / Direct Query Mode (SSMS)• Direct Query mode can use Columnstore indexes• Direct Query mode supports only a single data source• Not all DAX functions work in Direct Query mode
![Page 35: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/35.jpg)
SSAS Server Settings for Tabular ModeSSAS Server Settings for Tabular Mode
![Page 36: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/36.jpg)
Demo Demo Deploying a BISM
Model to SSAS
using SSDT
![Page 37: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/37.jpg)
Connecting to a deployed BISM modelConnecting to a deployed BISM model
• Administration in SSMS
• View in Client– Excel / PowerPivot
– SharePoint PowerView
– Other clients
![Page 38: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/38.jpg)
Connect in SSMSConnect in SSMS
![Page 39: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/39.jpg)
DAX or MDX?DAX or MDX?
• MDX issuing clients (Excel) can – talk to both multidimensional models and tabular models running in
VertiPaq (in-memory) mode
• MDX cannot – be used to talk to DirectQuery models
• DAX issuing clients (Power View) can – talk to tabular models (both kinds - VertiPaq and DirectQuery)
• DAX cannot – be used to talk to multidimensional models. This functionality is on the
roadmap, but will not be present at RTM
![Page 40: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/40.jpg)
Updating the dataUpdating the data
• Can update subsets using partitions• Can monitor update process via Profiler
![Page 41: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/41.jpg)
Monitoring Resource UsageMonitoring Resource Usage
• Can use Profiler• Extended Events for monitoring• Can use DMVs• SSAS Operations Guide
![Page 42: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/42.jpg)
Tips, Tricks & GotchasTips, Tricks & Gotchas
• Installation• SSAS in either Tabular –or- UDM mode
• Needs prerequisites
• Development• One BISM model per project
• Prefer DAX, not MDX
• Try it out in Excel while developing in SSDT
• Deployment• Use partitions for granular updating
• Monitor via Profiler – plan for up to ½ data size in server memory
![Page 43: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/43.jpg)
Other ToolsOther Tools
• Cmdlets on CodePlex – here• Klout case study whitepaper - here• DAX studio/ intellisense - here
![Page 44: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/44.jpg)
What about the UDM?What about the UDM?
• UDM / Data Mining is fully supported in SSAS• Must be installed in this mode
– Mutually exclusive to Tabular mode
• But, should you use it anymore?
![Page 45: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/45.jpg)
![Page 46: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/46.jpg)
ResourcesResources
• SSAS Team Blog – here• About DAX – MVP blog – here• SQL 2012 tutorial - here• Codeplex samples – here • Klout case study whitepaper - here
![Page 47: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/47.jpg)
www.TeachingKidsProgramming.orwww.TeachingKidsProgramming.orgg
• Free Courseware ( • Do a Recipe Teach a Kid (Ages 10 ++)• Java or Microsoft SmallBasic
• recipes)
![Page 48: Real-world BISM in SQL Server 2012 SSAS](https://reader035.vdocuments.us/reader035/viewer/2022062510/54b4c2c94a795970178b45fc/html5/thumbnails/48.jpg)
Toward Data Craftsmanship…Toward Data Craftsmanship…