Book Review: Why You Should Read Expert Cube Development With SSAS 2012 Cover to Cover

June 15, 2014 Leave a comment

Expert Cube Development with SSAS 2012 Multidimensional Models was published earlier this year by Packt Publishing. It’s the second edition of the very successful book on SSAS cube development by three well-known industry leaders, Chris Webb, Alberto Ferrari and Marco Russo.

This book is not a tutorial book on using SSAS as a tool. It is more of a guided tour through the lifecycle of building an Analysis Services solution with an informed commentary telling you what to do, what not to do, and what to watch out for.

Reading this book cover to cover

If you are a SSAS cube developer, you would want to read this book cover to cover, no matter what level you are, with the exception of absolute beginners who do not understand basic Analysis Services concepts yet, such as what a cube and a dimension is.

I bought the first edition a few years ago, but didn’t read it cover to cover because at the time I didn’t find some of the topics relevant to my work. Earlier this year I bought the second edition and I found myself unable to put the book down. By the time I knew it, I had already read it cover to cover once, with pages of notes in Microsoft OneNote. Knowing that my cube development skills could have progressed much faster, I wish I had read the book a few years ago cover to cover.

So don’t repeat the same mistake I made. Whether you already have the first edition or just bought the new 2012 edition, go ahead and start reading it now.

What I enjoyed about the book

I don’t wish to spoil your fun with the book, so I’ll just gloss over a few key points about the book.

  • Beginner developers might think that cube development is all about how to use SSAS as yet another tool. This book will change your mind. The big chunk of Chapter 1 focused on the data modeling for Analysis Services. Then the book moved on to Chapter 2 to show you how to build basic dimensions and cubes. More complex dimension modeling is covered in Chapter 3. Data modeling for measures and measure groups is covered in Chapter 4. What I enjoyed the most is how the book presented the challenges we all encountered in our day-to-day work and provided the best practices in terms of data modeling in Analysis Services multidimensional model.
  • Microsoft Analysis Services is not a standalone technology, it’s part of a family of technologies and disciplines that all work together to make it possible for end-users to do interactive data analysis, reporting, and visualization. From a developer’s point of view, these technologies include the SQL Server engine, the Reporting Services, the Analysis Services, with the Integration Services in the middle as the glue. The disciplines include, but are not limited to, data warehouse data modeling, multidimensional modeling, and designing and implementation for performance and good user experience. I personally find that being able to fit all these techniques and disciplines together in the lifecycle of building an Analysis Services solution is not an easy task. Throughout the book the authors did a fantastic job of showing how each technique and discipline can fit seamlessly to build high performance cubes.
  • As a tool, Analysis Services is very easy to use; some might say too easy. Dimensions and cubes are built with various wizards with properties already being filled with default values. You can have a cube up and running in a matter of minutes. Some properties are for cube’s client tools to consume, but many of the properties are cube’s metadata and will end up having some impact on the cube processing performance, query performance, and/or storage engine performance. Assuming that your cube has started its life with a good design, then a good portion of a cube developer’s job is to understand what those impacts are and to make informed trade-off decisions. This book is a life-saving book that tells you what those properties mean, what to do with them, what not to do, and what to watch out for.
  • Bad cube query performance can be detrimental for your Analysis Services projects. The book has devoted an entire Chapter 8 to query performance tuning. The concept of query performance tuning is very familiar to SQL Server developers, but cube query performance tuning methodology has its own twist and turns, such as the Formula Engine vs. the Storage Engine, the partitions and aggregations, and tuning an algorithm in MDX. The book explains in detail what to do with each methodology and even the right tools and scripts to use to get the job done correctly.
  • I also like the many links in the book to other very detailed white papers, such as “The Analysis Services 2008 R2 Performance Guide”, and “The Many-to-Many Revolution”. Many blog posts are also included in the book, such as the blog posts from Mosha Pasumansky who was considered the most influential person in MDX.

No covering of SSAS Tabular models

As you may know, as of SQL Server 2012, there are two versions of Analysis Services: Multidimensional and Tabular. Although both of them are called Analysis Services and can be used for much the same purposes, the development experience for the two is completely different.

I have bought the first edition a few years ago. Although this is basically the same book as the first edition, I still went ahead and bought it because the 2012 edition has a new section that talks about the DAX query support in SSAS 2012 multidimensional model. Don’t get me wrong, this book only covers SSAS Multidimensional models. But it’s nice to have a new section on how SSAS 2012 multidimensional model supports not only MDX queries, but also DAX queries.

No substantial changes in this second edition

Since there are no substantial changes in this second edition, it’s probably not worth buying a copy of the second edition if you already have a copy of the first edition. What is covered in the first edition should work perfectly fine in SSAS 2008 and 2012, and even in 2014. This is because Microsoft has not added anything that is substantially new to SSAS Multidimensional models since the 2008 version. But if you don’t have the 2008 edition, I’d recommend you to buy this new 2012 edition, even if you are still working on cubes in SSAS 2008.

Not a book for absolute beginners

If you still need to understand basic Analysis Services concepts, such as what a cube and a dimension is, then this book is not book for you. This book does not take the form of a basic tutorial either.

Authors’ personal experience and thoughts are invaluable

Chris Webb, Alberto Ferrari, and Marco Russo are well-known in the SSAS and MDX community. This is an invaluable book because it contains their personal experience and thoughts. I myself visit Microsoft books online (BOL) very often. But if a book is solely derived from BOL then it is not too useful for me, as I can read it in the BOL myself. I am putting this review on my blog, and also planning to put it out on Amazon and Barnes and Nobel, hoping that all cube developers will read the book cove to cover.

Packt Publishing

Packt Publishing is one of my favorite tech book publishers. Their books focus on practicality, recognizing that readers are ultimately concerned with getting the job done. They also offer a subscription service, which I personally also use. Good job for putting out “Expert Cube Development with SSAS 2012″!

 

MDX #41–Remove employees with less than $100,000 sales

May 30, 2014 Leave a comment

Table of contents

Need to remove employees who have zero sales amount
Create a calculated measure with the IIF() function
The NON EMPTY keyword will take care of the removal
Ordering results with both numeric and string expression
IIF() statement VS. Scope() statement in MDX Cookbook 2012

Need to remove employees who have zero sales amount

I recently helped a co-worker with a simple MDX query. All she wanted to do is to remove employees from the return set who has no sales or who has zero $ sales. She was using the NON EMPTY keyword on both the X and Y axis. That removed employees who have no sales, but the employees who have zero sales amount are still showing in the data set.

A quick and simple way to remove those employees with zero sales amount is to create a calculated measure in the query. This new calculated measure will simply use the IIF function to turn the sales amount into a NULL value, when the sales amount is zero. The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with zero sales amount.

Create a calculated measure with the IIF() function

Suppose that this is our initial MDX query, where all employees (at leaf level of the Employees hierarchy) who have sales are returned.

 

image

Now you would like to only see employees who have more than $100,000 sales. In the following modified MDX query, I added this calculated measure. 

[Measures].[Reseller Sales Amount > 100,000]

This measure is simply identical to [Measures].[Reseller Sales Amount], except that it is set by the IIF function to be NULL, when it is less than 100,000.

The NON EMPTY keyword will take care of the removal

The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with sales less than 100,000.

 

image

Ordering results with both numeric and string expression

To be sure that we get only the employee who have more than 100,00 sales amount, we can sort the results by the sales amount using the order() function with this numeric expression.

[Measures].[Reseller Sales Amount > 100,000]

 

image

 

If you insist to sort the results by the employees’ name, use the employee names as a string expression in the order() function.

[Measures].[Employee name]

 

image

IIF() statement VS. Scope() statement in MDX Cookbook 2012

In the book MDX Cookbook 2012, we have quite extensive examples on how to use the query-based IIF() statement, as well as the the cube-based Scope() statement.

You can check out the recipe Detecting a particular member of a hierarchy while you are using the book.

Categories: MDX Challenges Tags: , ,

SSRS #74 – 2008 Cascading bug when you have a middle parameter

May 9, 2014 2 comments

Some SSRS reports have a large number of parameters

I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.

Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.

A bug in SSRS 2008

Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.

clip_image002

I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.

So I had to say that this is a bug in SSRS 2008.

Remove the middle parameter to work around the bug

To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).

clip_image004

The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.

Longer version of the solution

The following is the longer version of the solution.

My Original Suggestion
  • Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
  • Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
  • The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
  • Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
  • Good news: this solution worked in SSRS 2012 in my test.

Here are two screen shots from our failed solution.

clip_image006

clip_image008

New Solution That Worked
  • Remove the middle parameter entirely.
  • Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
  • The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
  • Voila. It works.
  • Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.

Here are a few screen shots from our successful solution.

clip_image010

The nested IIF statement is now coded in the parameter expression.

clip_image012

DAX #2 – Installing AdventureWorks DW Tabular Model SQL Server 2012

April 30, 2014 Leave a comment

OK, first thing first. We all know the famous Adventure Works multidimensional sample project. To learn the tubular mode/DAX, we also have a sample tabular project.

Download sample database and projects from CodePlex

You can follow this link to CodePlex to download the following files: tinyurl/AdventureWorks2012.

  • AdventureWorksDW2012_Data.mdf
  • AdventureWorks DW Tabular Model SQL Server 2012.zip
  • AW Internet Sales Tabular Model.zip
  • AdventureWorks Multidimensional Models SQL Server 2012.zip (also download this multidimensional sample project while you are there for leaning SSAS/MDX, if you haven’t done so.)

I suggest that you unzip all the files onto a one level folder on your C drive, instead of the Windows built-in Downloads folder or the Documents folder. If you have seen the annoying “Path too long” error when you copy/past or unzip folders with very deep levels, you will appreciate my suggestion.

On Window 7 and 8, also make sure that unblock all the folders/files, and your user ID has full control on them.

Assume you have installed SQL Server 2012

I’ll assume that you have installed the following using the SQL Server Developer Edition or Enterprise Edition 2012:

  • SQL Server 2012 Database Engine (server instance): this is the relational database instance.
  • SQL Server 2012 Analysis Services – Multidimensional model (server instance): this is not required for learning DAX, but it can be extremely important to learn MDX and DAX side-by-side.
  • SQL Server 2012 Analysis Services – Tabular model (server instance)
  • SQL Server 2012 Management Studio (desktop tool): older version of SSMS does not support Analysis Services tubular model.
  • SQL Server Data Tools for Visual Studio 2012 (desktop tool): if you have Microsoft Visual Studio Professional 2012, you should get SSDT and also

To keep things simple, I have installed all three instances on my laptop. The database engine is the default instance, and both the multidimensional model and the tabular model are named instances. I have chosen to name them as:

  • localhost
  • localhost\MULTI2012
  • localhost\TABULAR2012

To make things easy for myself, I always add my own user ID as the administrator for each instance.

(Disclaimer: do not use this type of architecture on your production server.)

Also need PowerPivot

While you are at the installation, also make sure that you have PowerPivot. PowerPivot is the desktop tool for analyst for creating the tubular model, which is eventually consumed by Pivot Table for reporting.

You can run PowerPivot in either Excel 2010 or Excel 2013. If you have Excel 2013, you are all set because PowerPivot is included by default. If you are still running Excel 2010 like me, follow this link to download the free Power Pivot add-in.

www.powerpivot.com

Attach AdventureWorksDW2012_Data.mdf

A typical multidimensional model is built on top of a relational star schema data warehouse. To create a tabular model, we also need data source(s). The most frequently used data sources are the common relational database systems, text files, and excel workbooks. The Adventure Works DW 2012 is the relational star schema database the sample tabular model database uses.

This .mdf file you have just downloaded does not have the matching log file. You can use the following T-SQL command with the option of FOR ATTACH_REBUILD_LOG to attach the database to the SQL Server database instance.

CREATE DATABASE AdventureWorksDW2012EE
ON (FILENAME = N’C:\Adventure Works for SQL Server 2012\AdventureWorksDW2012_Data.mdf’)
FOR ATTACH_REBUILD_LOG;

I’ve named the new database AdventureWorksDW2012EE for Enterprise Edition. This database and the data will be the source of both of my tubular and multidimensional model.

I don’t need to worry about backing up the log for this database, so I also made sure that the Recovery Model is set to Simple.

To learn more about how to attach a database, please refer to this TechNet article, Attach a Database.

Create the Adventure Works DW Tabular Model Database

If you are familiar with Analysis Services, you’d know that Analysis Services database can only be created by deploying the correspondent SQL Server Data Tools project. Unlike the SQL Server relational database, you will not be able to attaché nor import an Analysis Services database.

Fortunately SSDT tabular project you have downloaded is ready for deployment. After you have successfully deployed a tubular model project, you should be able to see two tubular model databases from SSMS.

dax2 2 database - Copy - Copy

 

 

 

 

Once you open the tabular model sample project in SSDT, be sure to make the following adjustments to ensure a smooth deployment.

To begin, right-click on a SSDT 2012 solution, and go to Add – Existing project…. Then find the following sample tubular project you just downloaded.

AdventureWorks Tabular Model SQL 2012.smproj

1.      Test Workspace server connection

As soon as you attempt to open the existing tubular project, you will be asked to identify an Analysis Services instance to use for authoring tabular model projects. I entered localhost\TABULAR2012, and also click Test Connection. Any failure of the connection must be resolved before you can continue. Connection failures mostly indicate permission problems with your Analysis Services instance. To keep things easy on my own laptop, I’ve chosen to use my own user ID to start the Analysis Services tabular model instance, instead of the default service account (again not recommended for your-know-what).

dax2 AS instance - Copy - Copy

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.      Be sure to check the existing connection to the relational database

What I need to make sure is that the underneath relation database is pointing to my AdventureWorksDW2012EE database on localhost.

Go to menu MODEL – Existing Connections – Edit.

dax2 AS deploy - Copy - Copy

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After modify the database name, click Test Connection again to test it. At this point, I also want to change the credential for Impersonation to use my own user ID, instead of the service account.

dax2 Impersonation - Copy - Copy

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Be sure to modify the tabular project property page

I also need to make sure I have the right Analysis Services instance for the deployment.

Go to the menu PROJECT – Properties…. I modified the Server name to be localhost\TABULAR2012, and accepted the default database name “AdventureWorks Tabular Model SQL 2012”.

dax2 proj property - Copy

 

 

 

 

 

 

 

 

 

 

 

4. Process a table

Assume that you have successfully opened the sample project, and both the connections to the relational database and the tabular database are correct. Now you should have all the dimension tables and fact tables imported into the project. Before you deploy the project, try to process one table. With one table highlighted in the model, go to MODEL à Process à Process Table. All is good, the processing will complete without error.

Processing data means getting the latest data from external sources. The test is to make sure that your source connection works as planned.

A bit more information about processing data. When authoring your model project in SSDT, you can only manually initiate the process actions. After a model has been deployed, process operations can be performed by using SQL Server Management Studio or scheduled by using a script.

Now deploy the tabular project by right-clicking on the project, and choosing Deploy. If all is good, you should see this dialog when the deployment is complete.

dax2 deploy success

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Validate the deployment in SSMS

Now go to SSMS, you should see two tabular model databases as shown in the screenshot in the previous section.

Let’s open up a MDX query window by right-clicking on AdventureWorks Tabular Model SQL 2012, and choose Mew Query – MDX.

dax2 Query

 

 

 

 

 

 

The DAX function EVALUATE is kind of like the SELECT * FROM command in T-SQL. It can be used to get the data in result set from a table.

Try this and you will see all the customers returned:

evaluate Customer

If you are familiar with MDX, the following MDX query should also work.

select
{} on 0,
[Customer].[First Name].[First Name].members on 1
from
[Internet Operation]

Now that the installation of the sample Adventure Works tabular model database is done, you are all set to learn DAX.

SQL #53 – Keyboard shortcut Alt + F1 works now in SSMS 2012

April 30, 2014 Leave a comment

Keyboard shortcut Alt + F1 works now

SSMS has a rich set of keyboard shortcuts that works by default. But in SSMS 2008 I had one complain about the default Alt+F1 shortcut. The Alt+F1 shortcut is assigned to sp_help by default.  It worked for tables, sort of. If you are a good designer and had followed the best practice of putting your tables in the appropriate schemas, using the Alt+F1 shortcut is quite a pain. You will need to wrap your schemanized table as the following for the shortcut to work.

[HR.Empoyees]

Check out this older blog.

SQL #49 – Retrieve table structure with keyboard shortcut in SQL Management Studio (SSMS)

Not any more in SSMS 2012. The fix is finally done. Either of the following will work by default. The old way of wrapping with [ ] will no longer work.

  • [HR].[Empoyees]
  • HR.Empoyees

This shortcut is equivalent to execute the sp_help procedure.

sp_help ‘[HR].[Empoyees]‘

Assign keyboard shortcut to sp_helptext

The system procedure sp_helptext is a great helper for developers who work on SQL codes day in and out. I used to have Ctrl+F1 mapped to it. Unfortunately, by default, in SSMS 2012, the shortcut Ctrl+F1 is now mapped to Display SQL Server Books Online.

SQL Server Management Studio Keyboard Shortcuts

There are people who tried to re-map it. But it just seems a bit too much to re-map it. So I decided to map the Ctrl+3 key to sp_helptext.

Highlight both of the following text and press Ctrl+3, you will get the procedure definition.

  • [sp_helpfile]
  • sp_helpfile

 

It is equivalent to executing sp_helptext.

sp_helptext ‘[sys].[sp_helpfile]‘

DAX #1 – Learning DAX

April 25, 2014 3 comments

I recently attended a virtual training of Tabular and Power Pivot for Developers taught by Kathy Vick at Pragmatic Works. Kathy had a comment (also an inside joke for developers I think) that for those who already have multidimensional cube and MDX skills, learning about the tabular (and PowerPivot, DAX) model doesn’t make any sense.

Putting the inside joke aside, I think the comment did raise a question in my mind. Why do I need to learn the tabular model? After some thinking, I came up with this answer, to learn DAX.

After 4 days training, and many slides and many demos, I realized that in the back of mind, what I really wanted to learn is another query language, DAX (Data Analysis Expressions).

Yes, this is on top of two query languages, SQL and MDX, most SQL Server developers are already familiar with.

A simple Googling and Bing, the search results are quite interesting.

  • SQL Server 2012 SQL language: over 15 million results.
  • SSAS MDX language: adequately 163 thousand results
  • SSAS DAX language: barely 52 thousand results.
Follow

Get every new post delivered to your Inbox.

Join 168 other followers

%d bloggers like this: