Exploring Excel 2013 for BI Tip #16: Exposing “Values” from a Tabular Model

19 06 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

From Power Pivot to SSAS Tabular

As companies move through the cycle of building Excel based solutions for business intelligence and analytics, they eventually end up with a SQL Server Analysis Services Tabular Model. The tabular model comes into play when you need more data in your model or want to support more granular security.

Up to this point, users have been happily using Power Pivot models in Excel to build their analysis solutions. However, once the model is deployed to tabular some functionality or interaction with the model changes in significant ways.

To summarize this point, power users or data modelers will create Power Pivot models in Excel. These models may or may not be deployed SharePoint, but they need to take them to the next level. You can migrate a Power Pivot model to tabular with ease by using the import option in SQL Server Data Tools.

image

Interacting with Power Pivot

I started by creating a simple Power Pivot model using Adventure Works DW data based on the Internet Sales fact table. I am using seven tables in my model as shown here.

image

I am not going to add any calculated measures to the model because Power Pivot allows me to use the data as it sets. Next we create a pivot table based on this model. I dropped the Fiscal Year onto rows and added OrderQuantity and ExtendedAmount to the values region. When OrderQuantity and ExtendedAmount are added to the pivot table, Excel defaults to a sum calculation when working with the data. Basically Excel creates the calculation for you based on what it knows about the data.

The point here is that I have data that can be used as values without doing any additional work with the model. I saved the workbook, closed Excel and moved on to the next step.

Interacting with Tabular

First we need to convert the Power Pivot model to a tabular model. Which is done by importing the model we just saved in SQL Server Data Tools. Once we have the project open, we need to deploy the model to a SSAS tabular instance so we can connect to it with Excel.

image

Now that it has been deployed to SSAS we can reopen our workbook and add a connection to the tabular model. In the field list we notice three differences now that the model is tabular.

1. The SUM symbol (sigma) is used to highlight values or measures that can be calculated.

2. The values we created in the Power Pivot model show up here.

3. In the Values section, “_No measures defined” is shown.

image

When working with multidimensional models, the Values section are represented the same. That makes sense as the connection that Excel is using is based on MDX not DAX. This significantly changes the user experience.

Let’s add a new measure to our Power Pivot model and try to do the same in the tabular model. We can still drop the DiscountAmount into the values section in our pivot table based on Power Pivot. However, when we try to do the same on tabular we get an error saying that we cannot add it to that area of the report.

image

In order for us to use DiscountAmount as a measure we will need to create an OLAP measure (See Excel Tip #8 for details) to use it in this Excel workbook or we will need to add it as a calculated measure in tabular and redeploy for it to be available.

What’s Happening

Because Excel treats a tabular model the same as a multidimensional model in SSAS you will need to add calculated measures for all measures you want to use as values in pivot tables in Excel. Multidimensional models are highly structured using the dimension and measure group techniques. While tabular “feels” like Power Pivot, to be used by Excel it needs to appear structured like multidimensional cubes.

Making this more interesting is that Excel uses MDX to communicate with tabular models, not DAX. As a result, we are able to use the OLAP tools in the PivotTable Tools ribbon.

image

This option is not available when working with Power Pivot models in Excel.

Impact to Users

Overall the impact to users, in particular power users and report builders, is that they have less “freedom” to design when using a tabular model. If they want to add more calculations, they need to be familiar with MDX. Furthermore, if they want the calculations to be generally available they need to work with IT to deploy updated models.

Hopefully we will see DAX supported interaction with SSAS in the future, but for the moment you need to understand how tabular and Power Pivot differ when using pivot tables in Excel.





Cultural Anthropology and Business Intelligence–A Tribute to Dr. Thomas C. Correll

29 01 2013

A tribute is an expression of gratitude or praise. Last year I started a series about individuals who have impacted my career. I do this as a tribute to my father-in-law, Ed Jankowski who passed away a few years ago. Check out my original post about him and his impact on me being in software development today.

Cultural Anthropology and Cross-Cultural Studies

Prior to working with data and software, my career choice was to be a missionary. Yes, that is correct. I am trained to work cross-culturally with churches and other Christian organizations. Along the way, I learned how to work with software and my career changed. However, as with many things in life, seemingly unrelated items impact real life. In my case, my final degree work was done in Cultural Anthropology. This tribute post calls out one of my professors who greatly impacted me personally and whose training has helped me with my work in BI solutions.

During my stint at Bethany College of Missions in Bloomington, Minnesota, I took a number of courses from Dr. Thomas Correll in the field of cultural anthropology. He was responsible for providing me with the bulk of my investigative and research skills for use in this field. In particular, I remember three techniques that we studiedimage in particular: Life History Study Method, Participant Observation, and Ethnographic Interviews. Two of these, Participant Observation and Ethnographic Interview are based on James Spradley’s work. Through these classes and supporting projects, I learned much in the way of research and analysis.

Dr. Correll, or Tom as he preferred in class, ingrained in me a great desire to research and do it well. Even now, I am proud of the work I did for those classes as they stretched me to learn and delve into areas I was not entirely comfortable with. I really believe that those research techniques have served me well when I work with customers on BI projects. Here is how I see the impact on my daily work from the training I received.

Life History Study Method. The overall goal of this study methodology is to understand the changes that mean something to individuals in their culture. In BI, this has made me understand or pursue those points when companies or departments are ready for their next growth area such as time to move from reports to dashboards. Each of these types of changes reflect a significant shift in how a company sees its data or how to better to use it in day to day situations.  Sounds a bit like the BI Maturity Model developed by TDWI.

image

Participant Observation. Can you say business analysis? This is the epitome of the initial step for a good BA. What do you see as the needs. Pay attention, observe, understand what is happening in the company. The skills I learned in this class helps me slow down and observe what is happening at a customer. This includes noticing who really impacts technology decisions in a company.  It is not always what it seems at first.

Ethnographic Interview. This is a particular process where I deep dive into what customers are trying to do. I learned how to drive into business needs using these interview techniques.  This research method uses similar techniques you might learn from Kimball.  The key is to ask questions that get answers you need whether you were looking for them or not.

Overall, I can say much of what I am able to accomplish today is a result of what I learned from Tom. He was truly passionate about doing research, doing it right, and understanding the results. Thanks Tom. What I learned from you has been instrumental in my career and in love for information and getting results.








Follow

Get every new post delivered to your Inbox.

Join 730 other followers

%d bloggers like this: