SQL Server Analysis Services 2012 Cube Development Cookbook -- Table of Contents -- SQL Server Analysis Services 2012 Cube Development Cookbook -- Credits -- About the Authors -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why Subscribe? -- Free Access for Packt account holders -- Instant Updates on New Packt Books -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code -- Errata -- Piracy -- Questions -- 1. Introduction to Multidimensional Data Model Design -- Introduction -- The business value of Business Intelligence -- Challenges and barriers of effective BI -- Overcoming BI challenges and barriers -- Choosing multidimensional or Tabular Models -- Star- or Snowflake-relational schema -- A sample scenario for choosing the Snowflake schema -- 2. Defining Analysis Services Dimensions -- Introduction -- Defining data sources -- How to do it... -- How it works... -- There's more... -- Defining data source views -- Getting started -- How to do it... -- Defining entity relationships in DSV -- How to do it... -- Extending data source views -- How to do it... -- Creating named calculations and queries -- How to do it... -- Creating simple dimensions -- Getting ready -- How to do it... -- How it works... -- Building dimension hierarchies -- How to do it... -- There's more -- Setting dimension properties -- Setting essential attribute properties -- How to do it... -- Browsing dimension data -- Getting ready -- How to do it... -- How it works... -- Sorting the attributes -- How to do it... -- Customizing advanced attribute properties -- How to do it... -- How it works... -- Creating parent-child dimensions -- How to do it... -- How it works... -- There's more...

Creating the date and time dimensions -- How to do it... -- There's more... -- 3. Creating Analysis Services Cubes -- Introduction -- Defining measure groups and measures -- Getting ready -- How to do it... -- Setting measure properties -- How to do it... -- There's more... -- Browsing the cube data -- How to do it... -- Dimension usage with measure group -- How to do it... -- Examining cube file structures -- How to do it... -- There's more... -- Partitioning strategies -- How to do it... -- Creating partitions through the wizard -- Creating partitions through AMO -- Partition storage mode options -- Defining partition slice -- How to do it... -- Merging partitions -- How to do it... -- Defining aggregation designs -- Using BIDS Helper for customizing aggregations -- How to do it... -- There's more... -- Distinct count measure groups -- How to do it... -- There's more... -- Enabling write-back feature -- How to do it... -- Deployment options -- How to do it... -- 4. Extending and Customizing Cubes -- Introduction -- Defining calculated measures -- Getting ready -- How to do it... -- Defining named sets -- How to do it... -- Defining drillthrough actions -- How to do it... -- How it works... -- Defining URL actions -- How to do it... -- Defining reporting actions -- How to do it... -- Defining key performance indicators -- How to do it... -- Defining perspectives -- How to do it... -- Defining translations -- How to do it... -- Defining measure expressions -- How to do it... -- 5. Optimizing Dimension and Cube Processing -- Introduction -- Understanding dimension processing options -- How it works... -- Learning about basic dimension processing -- Getting ready -- How to do it... -- How it works... -- Learning advanced dimension processing options -- How to do it... -- Using out-of-line bindings for dimension processing -- How to do it...

Dealing with partition processing options -- How to do it... -- How it works... -- Using SQL Server Integration Services to process Analysis Services objects -- How to do it... -- Monitoring and tuning processing performance -- How to do it... -- 6. MDX -- Introduction -- Returning data on the query axes -- Getting ready -- How to do it... -- Limiting the query output -- How to do it... -- Sorting the query output -- How to do it... -- Defining query level calculations and named sets -- How to do it... -- Navigating dimension hierarchies -- How to do it... -- Working with the Time dimensions -- How to do it... -- MDX script's functionality -- How to do it... -- Monitoring and tuning MDX queries -- How to do it... -- There's more... -- 7. Analysis Services Security -- Introduction -- Managing instance-level administrative security -- How to do it... -- Managing database-level security -- How to do it... -- Managing cube-level security -- How to do it... -- Managing dimension hierarchy-level security -- How to do it... -- How it works... -- Implementing dynamic dimension security -- How to do it... -- There's more... -- Implementing cell-level security -- How to do it... -- There's more... -- 8. Administering and Monitoring Analysis Services -- Introduction -- SSAS instance configuration options -- How to do it... -- How it works... -- There's more... -- Creating and dropping databases -- Getting ready -- How to do it... -- Monitoring SSAS instance using Activity Viewer -- How to do it... -- How it works... -- Monitoring SSAS instance using DMVs -- How to do it... -- How it works... -- There's more... -- Cancelling a session -- How to do it... -- Checking whether cubes are accessible -- How to do it... -- Checking SSAS object sizes programmatically -- How to do it... -- There's more... -- Scaling out SSAS solutions -- Backup and restore.

How to do it... -- How it works... -- Synchronizing databases -- How to do it... -- How it works... -- Detaching and attaching databases -- How to do it... -- How it works... -- 9. Using Tabular Models -- Introduction -- Creating a Tabular Model -- Getting ready -- How to do it… -- There's more… -- Working with data sources and loading data -- How to do it… -- There's more… -- Modeling the data -- Getting ready -- How to do it… -- How it works… -- There's more… -- See also -- Creating a hierarchy -- How to do it… -- There's more… -- Creating a calculated measure -- Getting ready -- How to do it… -- See also -- Creating a calculated column -- How to do it… -- There's more… -- See also -- Creating a KPI -- Getting ready -- How to do it… -- There's more… -- See also -- Analyzing your model in Excel -- How to do it… -- There's more… -- See also -- Deploying Tabular Models -- Getting ready -- How to do it… -- How it works… -- There's more… -- See also -- Scripting Tabular Models using XMLA -- Getting ready -- How to do it… -- There's more… -- Processing Tabular Models -- How to do it… -- How it works… -- There's more… -- See also -- Partitioning Tabular Models -- Getting ready -- How to do it… -- There's more… -- See also -- Implementing perspectives -- Getting ready -- How to do it… -- There's more… -- See also -- Implementing security in Tabular Models -- Getting ready -- How to do it… -- How it works… -- There's more… -- Automating Tabular Model processing -- Getting ready -- How to do it… -- There's more… -- See also -- 10. DAX Calculations and Queries -- Introduction -- Combining tables using calculated columns -- Getting ready -- How to do it… -- There's more… -- Adding a calculated column -- Getting ready -- How to do it… -- Creating measures -- Getting ready -- How to do it… -- Creating AutoSum measures -- Creating calculated measures.

Testing a Tabular Model in Excel -- How to do it… -- Understanding Row Context -- Understanding Filter Context -- Using the CALCULATE function -- How to do it… -- Test the Measures in Excel -- Working with Time Intelligence -- Querying a Tabular Model -- Getting ready -- How to do it… -- There's more… -- See also -- 11. Performance Tuning and Troubleshooting Tabular Models -- Introduction -- Understanding usability limits -- Getting ready -- How to do it… -- There's more… -- Optimizing and managing a model's design -- Managing memory usage -- Diagnosing performance issues -- Processing and query-related performance -- Using performance tools -- Task Manager and Resource Monitor -- Dynamic Management Views -- BISM server memory report -- Investigating query performance with SQL Server Profiler -- How to do it… -- There's more… -- See also -- A. Miscellaneous Analysis Services Topics -- Working with non-SQL Server data sources -- SSAS data structures -- Transaction isolation levels -- Processing performance issues -- Common yet confusing SSAS errors -- Binding is too small -- Attribute key cannot be found -- Undefined column name -- DSV does not contain definition for an xyz column -- Operation is cancelled -- File is corrupted -- Error encountered in the transport layer -- Internal error or unexpected exception -- Deadlock -- Dimension properties -- See also -- Performance considerations for many-to-many dimension relationships -- DirectQuery with Tabular Models -- Data sources -- Security -- Design limitations -- Client restrictions -- Index.