Book Review of Microsoft Tabular Modeling Cookbook

by Paul te Braak, from Packt Publishing
on Amazon

Good technology books usually come in one of two forms.  Some of the books on my shelves go deep technically, contain useful tidbits of code but read like the phonebook.  Just a few are interesting and insightful.  This book is a rare gem that does both.  Paul te Braak is well-known in the Business Intelligence community for his expertise and contributions and he delivers a unique guide that starts with the basics and proceeds to cover all of the essentials with depth and practical examples to solve some challenging business problems.

You might expect a book that introduces fundamental concepts to gloss-over advanced topics and avoid complex applications.  This book covers the essentials of data modeling and analysis with Excel and Power Pivot in language that is plain and easy to understand but it doesn’t stop with the basics.  It covers practical and useful applications without insulting the reader’s intelligence.  As an experienced data modeler, I found several useful techniques and new methods to use the tools and language.  Paul’s coverage of practical techniques spans the spectrum of business applications and product features.  This is a rare book that is not only a good tutorial with many hands-on examples that can be repeated by the reader but it’s also a great reference of useful techniques and code samples.

Highlights include:

  • The integration of Excel features such as ranges, tables, pivot tables and pivot charts with the additional features of Power Pivot and Power View that extend and enhance these capabilities
  • Examples and instructions are directed at Excel 2010 users and the author compares some the different features in Excel 2010 and Excel 2013.
  • Fundamentals of the DAX calculation language
  • Importing data as text, different date formats and implied data type columns
  • Beyond the basics, a schema.ini file is used to define column data types
  • Importing data from a database, working with database tables, views and queries, managing connections and challenges encountered running the table import wizard multiple times
  • Data feeds using OData and using a Reporting Services report to provide a data feed
  • Decisions a designer makes to enable the user’s experience when browsing a model.  This includes sorting values, navigating hierarchies that enable drill-down interaction. 
  • DAX “X” functions (SUMX, MINX, etc.) to perform row-level aggregation
  • Working with parent-child hierarchies using specialized DAX path functions. 
  • Advanced browsing features, adjusting pivot table options to optimize the user experience
  • Building and using KPIs and using alternate table relationships
  • Time calculations and date functions.  This chapter covers running totals and totals to date
  • Date part aggregate functions (MTD, YTD, etc.),
  • Essential data math and comparisons
  • LastYear and PriorPeriod functions, TotalYTD
  • Manufacturing calendar, working with “445” dates
  • Creating a dynamic relative time measure, using a shell dimension table
  • Using DatesBetween to show the average value for the past 10 days
  • Apply advanced modeling technique to bin, sort and rank values for reporting
  • Expand concepts introduced in chapter 3, using the DAX “X” functions to perform row iteration in advanced financial applications
  • Defining and working with many-to-many relationships.  This is often no trivial task to completely understand many-to-many relationship requirements and to apply a working solution that provides the intended results
  • Addressing inventory and stock-keeping challenges
  • Conditional aggregation at different levels
  • Budgeting and forecasting vs actuals
  • Programming Excel to enhance the users experience
  • Excel VBA event programming to respond to slicers
  • Using cube functions
  • Interacting with charts and slicers
  • Building solutions for the enterprise
  • Using the SSDS Tabular designer
  • Migrating Power Pivot models to Tabular server solutions
  • managing connections, implementing impersonation, managing security
  • Using roles and perspectives
  • Generating and using XMLA script
  • Defining and implementing role-based, dynamic row filtering
  • Performing currency conversion
  • Managing and optimizing  a Tabular solution
  • Deployment scenarios
  • Using SSDT to deploy and process models
  • Using the SSAS Deployment Wizard
  • Generating and using deployment scripts
  • Creating and managing partitions
  • Scheduling and executing processing tasks
  • Utilizing DirectQuery for real-time data results
  • Using Profiler to troubleshoot and optimizing a model
  • Querying a model using DAX
  • comparison of similar and different concepts in multidimensional and Tabular semantic models
  • Query with MDX
  • Query with DAX
  • DAX tools and debugging techniques
  • Using DAX query techniques to simulate SQL query operations
  • Column aliases, joins, filters, deriving tables
  • Samples and top ranked results
  • Using Power View to present results and visualize data
    • Essential design features
    • creating a table report
    • using a matrix to pivot results
    • time and data filters
    • advanced filters
    • creating charts
    • bar charts
    • stacked charts
    • cluster chart
    • using tiles to navigate sectioned results
    • using images
    • managing tables with default field sets
    • table behavior and cards
    • data categories and visual behaviors
Digg This

Mobile Reporting with the Microsoft Platform

What’s Microsoft’s strategy for reporting capabilities on mobile devices?

SQL Server 2012 has been "released to manufacturing".  For those of you who may find this phrase confusing it means that the product is completed and they’re burning the disks, stuffing the boxes, training the tech support engineers and getting ready to make it available to download around the beginning of April.

This new version brings a lot of new capabilities but one of the most visible will be a new generation of business intelligence reporting.  I’ve had visibility to these new features for about two years now and there is some exciting stuff coming.  Business Intelligence is a very competitive industry and there are several industry offerings.  Most of them are very vertical, proprietary and expensive to implement.  Even though Microsoft has been a little behind the curve, a lot of smart people are anxiously awaiting SQL Server 2012 because it offers what no other vendor can offer: a holistic platform with intuitive tools that people either already know how to use or will be able to pick up very quickly.

In a nutshell, here’s what you should know about the new Microsoft BI platform:

  • The Microsoft BI platform is an investment.  There’s some really good stuff coming in SQL Server that integrates with SharePoint 2010 Enterprise Edition but it’s just the first wave.  In this release we will see:
      • Power Pivot v2 with several design interface and usability enhancements.  The Power Pivot model designer is available as a free downloadable add-in for Excel 2010.  Power Pivot models are fully-integrated with SharePoint, and can be used as shared data sources for other tools that work with Analysis Services.
      • BI Semantic Tabular Models.  This is essentially Power Pivot for the enterprise.  Models are either imported from Excel Power Pivot workbooks or authored in the new Visual Studio 2010-based SQL Server Data Tools (replacement fro BIDS) that installs with SQL Server 2012.  Tabular models are deployed to a special instance of SQL Server Analysis Services on a database server. 

Roadmap for mobile reporting:   http://aka.ms/alyovw

Using SharePoint Insights on iPad / Safari:
http://technet.microsoft.com/en-au/library/hh697482.aspx

[tweetmeme only_single=”false”]