Abstraction for Organic BI
BI is a organic environment with interaction and dependencies throughout the solution. In a traditional development effort, ETL cannot be developed until the relational data marts are implemented, multi-dimensional data marts cannot be developed until ETL is working and the relational data mart is populated, and reporting cannot be implemented until the data marts are complete.
In a BI solution, this kind of waterfall implementation strategy is deadly. It greatly extends time to delivery and makes scheduling and coordinating the various development teams more complex. Through the use of strategic abstraction we can address these issues as well as create a much more nimble solution that will allow us to efficiently respond to changing business needs.
Named Queries and Named Calculations in the Analysis Services Data Source View allow us to model the future state of the relational data mart without waiting for the implementation of the data mart and the ETL needed to populate its tables. This allows us to quickly prototype an enhancement to an existing solution or an entirely new solution. Through a series of review and revise cycles with business users we are now able to provide our relational data modeling and ETL teams with very precise requirements often including the actual SQL statements and queries necessary to support our model.
As an extra bonus for using the Data Source View for strategic abstraction we will have created the data interface report writers need in order to begin developing reports. Even in cases where the actual data in our model is fabricated or is in a relatively uncleansed state – remember, no ETL yet – we will have created the proper schema against which report queries can be written. As the data improves, the reports will improve but no additional development will be required.
But what about reports which consume the relational data marts? And, what about Analysis Services which is also a consumer of those relational data marts? Consider living by this rule: no application, including Analysis Services, will access the relational data mart directly. All access will be abstracted through a view and stored procedure layer.
The creation of views for all relational data mart tables – even if these views are initially as simple as select * {table} – is a good way to protect your applications from the schema changes that will inevitably come in the future.
Consider also the use of Stored Procedures for reporting, particularly where a report consumes data across several relational tables or requires manipulation of the relational data set using business – not presentation – rules. This will allow you to keep your reporting application as thin as possible and give you a single point of maintenance for logical updates in the future.
In a BI solution, this kind of waterfall implementation strategy is deadly. It greatly extends time to delivery and makes scheduling and coordinating the various development teams more complex. Through the use of strategic abstraction we can address these issues as well as create a much more nimble solution that will allow us to efficiently respond to changing business needs.
Named Queries and Named Calculations in the Analysis Services Data Source View allow us to model the future state of the relational data mart without waiting for the implementation of the data mart and the ETL needed to populate its tables. This allows us to quickly prototype an enhancement to an existing solution or an entirely new solution. Through a series of review and revise cycles with business users we are now able to provide our relational data modeling and ETL teams with very precise requirements often including the actual SQL statements and queries necessary to support our model.
As an extra bonus for using the Data Source View for strategic abstraction we will have created the data interface report writers need in order to begin developing reports. Even in cases where the actual data in our model is fabricated or is in a relatively uncleansed state – remember, no ETL yet – we will have created the proper schema against which report queries can be written. As the data improves, the reports will improve but no additional development will be required.
But what about reports which consume the relational data marts? And, what about Analysis Services which is also a consumer of those relational data marts? Consider living by this rule: no application, including Analysis Services, will access the relational data mart directly. All access will be abstracted through a view and stored procedure layer.
The creation of views for all relational data mart tables – even if these views are initially as simple as select * {table} – is a good way to protect your applications from the schema changes that will inevitably come in the future.
Consider also the use of Stored Procedures for reporting, particularly where a report consumes data across several relational tables or requires manipulation of the relational data set using business – not presentation – rules. This will allow you to keep your reporting application as thin as possible and give you a single point of maintenance for logical updates in the future.
posted by sjreagin at
12:03 AM
28 Comments
