Join me on Tuesday, May 12 as I discuss consumption based architecture for BI Solutions with Pragmatic Works.
Tag: bi
Building Your Data Team’s Make Up and Culture
Join Adam Jorgensen and Pragmatic works to learn about Building Your Data Team’s Make Up and Culture on Tuesday, May 5.
Excel BI Tip #26: Using a Data Spreadsheet or Tab
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 and later. 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!
Data Sheet or Tab in Excel
With a lot of the dashboard designs in Excel I work on, we often use CUBE formulas and other calculations and functions to get the data ready for the presentation area. One of the key things we do is create a sheet in the workbook, or tab, that will allow you to hold this data. This allows us to refer to cells on the data tab in our visualizations without trying to support visualization techniques along with calculations.
The most common scenario is when I want to present numbers in the visualization that are not in a pivot chart or pivot table. By keeping this in the data tab I have maximum flexibility in the visualization.
Let’s look at the following example using Adventure Works data (from http://msftdbprodsamples.codeplex.com/). We will create the following “data box” visualization using a data tab.
First, get the data into data sheet using a pivot table. Once we have the data we want to present there, we flatten the pivot table (see Excel BI Tip #18 for details). Now we can refer to the fields we need using the data tab. In the following images you can see the data box referring to data on the data tab which uses the CUBE functions to get the data.
As you can see, this allows us to contain a lot of data that is used for processing without cluttering up the visualization.
Hiding the Data Sheet from Users
Using a data sheet also means we need to hide this sheet from our users. You can hide the sheet in Excel directly. This is most useful when the workbook will be shared as a workbook. However, if you deploy the workbook to SharePoint or Office 365, you can use the Internet Settings to only make ranges or sheets visible depending on your implementation. I prefer this process as it allows dashboard designers to easily access the data without needing to be concerned with hiding the data sheet once they are done. (Refer to Excel BI Tip #21 for more about using ranges.)
When used in SharePoint or Office 365, their is no impact to the visualizations which use the data sheet. While not visible or available to the user, the data sheet stills supports the visualization as expected. In scenarios I have delivered, this technique has allowed for extensive data manipulation and formatting to present data in meaningful ways.
Exploring Excel 2013 for BI Tip #16: Exposing “Values” from a Tabular Model
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.
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.
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.
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.
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.
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.
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
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 studied
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.
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.
Data on Wheels - Kristyna Ferris & Steve Hughes 