The Big Picture: What is in the Center of Your BI Wheel?

What purpose is driving your BI project?  (I am talking about true OLAP in this blog post, not OLTP.)  When you have an ETL or hardware choice to make, you make your decision based on what is best for what?  Please allow me to suggest to you that a BI project should have one (1) central purpose: reporting and analytics (R&A).  Period.  The end.

If this is true, (and I ask this with kindness), how then can hardware, network, your data model, data integration and data visualization choices be made without R&A clearly defined?

For instance, I have often thought often that the most critical wheel spoke of a successful BI implementation is the data model.  "Build it and they will come" is not a really good catch phrase for a BI project because you may end up building a football stadium when your users intended to play basketball.  You can retrofit your football field, but wouldn't it have been a lot better (and cheaper) if you had built a basketball court to start?

Possible indicators that a BI model was not written with R&A in mind:

1. There are composite keys used to join tables
   
2. My time dimension table (like DimDate or DimCalendar) does not have a smart PK (primary key) of YYYYMMDD.
3. 99.9% of the reports have to remember to filter "WHERE IsDeleted = False"
   
4. I have to UNION measures together from multiple FACT tables.
5. There is no single-view-per-table design methodology in my EDW whereby omitting a necessary level of protection between my EDW and my consuming applications
6. I have to replicate simple addition and/or subtraction calculations in my SSAS, Excel and SSRS DSVs (data source views)
7. When the majority of my R&A requirements are "current attribute", not "attribute value at time of fact", yet the FK (foreign keys) in my FACT table(s) all require me to join "WHERE fact.FK = dim.PK and fact.date between dim.EffDate and dim.ExpDate"
8. My dimension foreign key in my FACT table(s) does not allow me to join to my SCD (slowly changing dimension) table(s)  with

Let's talk about the infrastructure team for a minute.  When we told them we were putting up a SQL Server data warehouse, did we provide anticipated data size over the next 12 and 24 months, AND take the time to explain that SQL Server works best when a table to assigned to a filegroup that in turn is dedicated to physical resources, not virtual?  Did we draw out our BI blueprint showing separate DEV and PROD environments and sizing all requirements for reporting workloads taking into consideration time zones and fluctuating business hours?  I find that most infrastructure teams are eager to please, but it is my responsibility to look at the big picture and plan hardware and network requirements NOT FOR ETL alone, but ultimately for R&A.

Possible indicators that hardware was not specified with R&A in mind:

1. There are not separate DEV, QA prePROD and PROD environments
   
2. SSIS and SSAS are running on the same box 
   
3. There is only one SSRS server or SSRS is running on the SharePoint box
   
4. The PROD environment is virtualized
5. The BI team is expected to create an enterprise solution using SQL Server STD edition

I am sure you can come up with actual indicators for your own projects, but the point is this:  EVERYTHING we do in our BI projects should take into consideration R&A.  Pick a wheel spoke -- any wheel spoke -- when you draw your blueprint for that spoke, UNDERSTAND your complex business logic and KNOW your reporting requirements.

What do you think?  Here are a few talking points for your BI team:

1. Returning to the top of the post, how will you make hardware, network, data model, data integration and data visualization choices without R&A clearly defined?
2. Whiteboard your own BI wheel.  What are your wheel spokes?
3. Now put an inner tube on your wheel called "audit, balance and control".
4. Put a tire on your wheel called "performance expectations".
5. Last, shine up that wheel with "data governance".

How do all of your choices support R&A?

---