Tabular Model Relationships

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:

=CONCATENATE(ReportExtract[CalendarYear], ReportExtract[StoreType])

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:

=CONCATENATE(SalesProjections[Year], SalesProjections[StoreType])

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: