I spent last week at the PASS Summit in Seattle and it was an experience worth writing about. I presented two session which I’ll also talk about, but I want to cover the greater event first. This conference brings people together better than any conference I’ve attended, and it isn’t even close. A great effort is made by the Summit organizers, volunteers, and attendees to keep you busy either attending sessions or meeting with people. I participated by being a first-time attendee mentor and heading up a birds of a feather table at Friday’s lunch, but there were so many that I couldn’t be part of them all. Yet it seems I spent the whole week, outside of sessions, talking to old friends and meeting new people from 8 AM until 10 PM every day. The talk ranged from mentoring people new to the SQL Server world to discussing deep technical issues, new business opportunities, and ideas for SQL Saturdays and user’s groups. This is the true value of the Summit that no other conference provides. When you combine the networking at the Summit with all of the associated local and regional events such as user’s groups and SQL Saturdays, you really get to know people in the community in a personal way.
I had the privilege of presenting two sessions at the Summit this year. I presented Real-time Data Warehouse and Reporting Solutions on Friday and to my surprise, the room was so full that the moderator had to close the door 10 minutes before the scheduled start time. There were even people sitting on the floor! The attendance and the participation was great to see. There was great interaction with the audience during the session and 30 minutes of follow up questions afterwards. The demo for this session is really difficult to get working during the presentation, but I’m happy to say that I got it running!
My other session was Thursday morning titled Data Modeling Best Practices for Enterprise Tabular Models. The session also had a full house, although it wasn’t so full that the moderator needed to close the doors. There was also a lot of interaction during this session and questions afterwards. My only regret about this presentation is that one of my DAX queries didn’t work. I had a cheat sheet so I could copy and paste longer code and avoid typing in front of the audience, but even this failed. I must have made a stray keystroke into the cheat sheet when I was reviewing the presentation beforehand in the speaker room. I reviewed it later and it turned out I had removed a parentheses from the middle of the DAX code. It’s really difficult to debug code during a session and after a couple of quick attempts and suggestions from the audience I decided to move on.
I should have looked on my own blog! The correct code was right there since I had blogged about the topic a week earlier, but I felt compelled to move on and not hold up the session any longer since the point I wanted to make with the failing DAX code wasn’t foundational to the presentation. However, it’s always disappointing when something like this happens because I spent so much time preparing. It makes me think I should prepare my demos using techniques from the Food channel, where I have a version of it already baked so I can go to it just in case it fails during the presentation. I’ll blog about the failed code later this week since it reinforces some of the points I was making regarding the BI Architect’s decision-making process for Enterprise Tabular Models..
On Friday I blogged about how to create a composite key to establish a relationship between two tables imported into a Tabular Model from different data sources. My example used a report extract table containing sales data in a relational database and an Excel spreadsheet with sales projections. After making the relationship, the first thing you’ll want to do with it is to compare the actual sales data with the sales projections to see if you are meeting sales targets. Since projections were made by store and year, you’re going to want to compare them to the total sales by store and year.
Sales Difference Calculation
To make the comparison you have to write DAX code that sums the actual sales data and then subtracts the projection to determine by how much the actuals exceeded or under delivered against the projection. To do this we’ll create a new column in the SalesProjections table and call it SalesProjDiff. The column is defined using this DAX code:
=SUMX(FILTER(ReportExtract, ReportExtract[YearStoreType] = SalesProjections[YearStoreType]), ReportExtract[SalesAmount]) – SalesProjections[ProjectedSales]
This new column uses the SUMX function to sum sales by year and store type and then subtracts the sales projection for the same year and store. To ensure that the correct sum is compared with the right projection value, make sure the filter uses the composite key you created that defined the relationship between the two tables that we called YearStoreType in each of the tables. When this is done you now have a column called SalesProjDiff that users can access in client visualization and reporting tools. This allows them to see how sales have done versus projections in the past, and also to measure how current sales are tracking against future projections.
I’ll cover more data modeling scenarios like this one using Tabular Models in my session at the PASS Summit titled Data Modeling Best Practices for Enterprise Tabular Models at 10:15 AM on Thursday, November 8.
One of the best features of Tabular Models in SQL Server 2012 is that they can include data from multiple sources such as relational databases, cubes, Excel files, or just about anything else you can think of. The logical data model of the underlying data is not dictated by this new version of Analysis Services, thereby allowing a Tabular Model to be developed with practically any existing data that’s available to you.
Faster Ramp Up
This makes it easier to get started with Tabular, especially if you’re working with a single data source that has a consistent data model. But what about multiple data sources that are modeled differently? For example, when most of the detail data you need is in a report extract table, but some budget data is in an Excel file. To handle this properly and to avoid having a collection of disparate data in the Model, the data modeling process needs to be done after the import takes place and one of the ways to do this is to create the right relationships among the various entities.
Here’s the preexisting Report Extract table you’re working with. Notice that it contains sales data by store, product, brand and date.
One of the people in the finance department has this spreadsheet, which has sales projections by store type and year for the past, present, and future. You want report developers who use your Tabular Model to compare the actual to projected sales.
The two sets of data aren’t related so you can’t do the actual vs projected comparisons you need to do. One problem is that there isn’t a unique key in either table that you can use to create a relationship between the two tables. To solve this problem you’ll need to create a composite key from two columns in each table that will be used as a unique key in one of the tables. This will allow you to create the relationship.
Add a column called YearStoreType to the Report Extract table that’s the concatenation of the year and store type columns:
Add another column, this time to the Sales Projection table, call it YearStoreType also (although the column names don’t have to match to be related), and define it this way:
Now create the relationship between the two tables, using the Sales Projections table as the lookup table.
Add measures like sums, average and any other calculations you need to have in your model. When you deploy it and load it as a pivot table in Excel, you’ll be able to easily drag and drop the sales projections and compare them with the actual sales data for each year and store type. This will show you why Tabular Models, which do not require source data to be modeled in any particular form, forces you to define the data model within the Tabular Model itself so it to makes sense to report developers and Excel users.
To learn more about data modeling in Tabular Models, attend my session at the PASS Summit titled Data Modeling Best Practices for Enterprise Tabular Models at 10:15 AM on Thursday, November 8.