One Giant Model vs. Splitting Up into Multiple Models

One of my clients requested I build a data warehouse in SSAS Tabular contained in a single model. I felt like that might not be a good idea, but I needed supporting arguments. Fortunately, I came across this topic in social media. It was a question submitted to @GuyInACube who asked his guest, Marco Russo

“Is it OK to build one giant data model, or should you split it up into multiple models?”

@GuyInACube

Marco Russo answered quickly, without a pause, “No” [you should not make one big model].

His reasoning against one giant model is non-technical. Here is an example. The same entity, or measure, even, might have different names or different meanings by different departments in the same company. They might even need to be defined with different code!

That problem is exactly what I experienced when trying to develop within one large model. The DW contained HR data, sales and commissions data, revenue, service, and utilization facts.

Right away, I noticed a difference in the definitions of sales type and revenue type. The rules classifying sales revenue types and actual revenue types (in the general ledger) were different, but they asked for one model, but they wanted departments, when using the model, to see 100% agreement with their existing transactional reports.

Having your analytic data match transactional reports is necessary; I agree. If they don’t match, the users won’t have confidence in the data. I solved this problem the only way that I could solve it.

I created separate measures:

• Sales Revenue
• Company Revenue

I also created separate dimensions:

• Sales Revenue Type
• Company Revenue Type

I also created perspectives: Sales, Corporate, Fleet, HR. This helped to reduce the confusion, but there were a few users who preferred to access the entire model, and in my opinion, this caused problems with user-designed reports.

The best approach, however, is to create one giant SQL data warehouse out of which you publish multiple data marts, each data mart having its own SSAS Tabular Model.