Where should I keep the business logic?
Posted by thomasivarssonmalmo on April 25, 2007
The SQL Server 2005 BI-platform includes the database, SSAS2005, SSRS2005, SSIS and Report builder.
Business logic is new columns in dimensions or new calculations in fact tables or cubes.
If we assume a data warehouse built with SSIS you might find that some important column is missing when you start building cubes(SSAS2005) or reports(SSRS2005). Maybe is something simple as a date format special for one country. In the real world you can find a lot of important columns missing because the ETL-people(SSIS) have missed some important business requirement.
Where will you correct this?
In SSAS2005 you have the data source view and the possibility to add columns(named calculations) that is not part of the data warehouse.
In SSRS2005 you can build data sets in reports that are adjusted for each individual report needs.
You can build report models on top of a cube(SSAS2005) or the data warehouse and add more business logic there.
And then three people meet, one with a SSRS2005 report(source: the data warehouse), one with an Excel report derived from a cube(SSAS2005) and the last with a report from report builder(built on a report model). In the meeting they will find out that the information is not the exactly the same even if it is from the same data warehouse with some "minor" adjustment in each application.
The answere? Keep as much as possible of the business logic in the data warehouse. Allow additional business logic in the cubes because MDX is superior than SQL to write time calculations(and a lot of other things). In the cubes never allow a developer to add columns in the data source view. Never allow the report side to add business logic.
That is what I have experienced. Never allow the business logic to spread and be fragmented into the report side. Else you will recreate the problem that you have tried to solve with a data warehouse and an UDM(cube).
Remember: "One version of the truth" and work against the demon of spreadsheet anarchy"