Datawarehouse Design, A Tool Independent Pragmatic Approach
I always asked myself during my consulting career what would be the best steps to Design a Datawarehouse & after musing for a while I came up with this couple a years ago which I had in my personal diary which am going to digitize today.
I always ask myself what can BA’s & Power users gain from a data warehouse ?
- Firstly it can enhance business productivity as we can see the organization’s information quickly & efficiently using a unified repository tool.
- It may provide us information to win over competitors by adjusting our prices & improving our performance.
- The data warehouse improves the CRM because it consistently provides business information across all business group hierarchies, departments & domains to make informed decisions possible.
Implementing a decision support system is like building a magnificent skyscraper which requires strategies of an Architect, Owner & Implementer / contractor.
Anonymous
To build an efficient data warehouse following 4 strategic perception should be combined together
- Datawarehouse Strategy It’s a process of identifying the fact tables, Dimension tables & ETL push-pull strategy, It represents the aggregates to be calculated during the ETL, database indexing strategy, database partition, DW refresh strategy & more.
- The Power User Strategy This is a view from the eyes of the BA, Power users & Customers on what information should be included in the warehouse for the current & future business requirements
- Sourcing the Data This is a strategic view of the Data Modeler he decides on how to gather & model te silo’s of data using specific data modeling techniques & tools like OBIEE, Cognos & Microstrategy
- The End User Perspective is to view & relate the data to business query
So the Datawarehouse design can be attained by a top down approach a bottom up approach or a combo,].
In the Top down approach the technology or DW tool is mature in the market and this approach starts with an over – all Inception analysis & planning phase where problem definition is apt.
In the Bottom up approach starts out with POC’s & experiments
In the Combo approach we can use the strategic planning of the top down approach with the rapid implementation technique of bottom up to increase ROI.
Datawarehouse Design Process:
- The first decision is to choose a Subject Area or a business process to model, This Subject area is a functionally related Dimensions & Facts together examples are Orders, Invoices, Shipments, Inventory, LOB Performance & more.
- The second decision is to choose a Fact Table Granularity because te DW front end testing is done in at the lowest granular level for example transactional details of a Customer on a monthly snpshot or a quarterly snapshot.
- The third step is to Choose functionally related Dimensions that would eventually become a part of the Fact.
- Te fourth Step is to Choose the Meters & Metrics, A meter can be defined as a collection of functionally dependent metrics.
Implementation Steps for a DW / DSS:
- Inception Analysis & Problem Definition.
- Requirements Study.
- DSS / Warehouse Design which comprises of Data Modeling, Database Modeling, Meta Data Repository Modeling, ETL Strategy & more.
- Integrating the Data & Regression Testing.
- Deployment, Training & Maintenance.
http://www.google.com/#hl=en&q=obiee+best+practices&aq=0&oq=obiee+best+&aqi=g3&fp=KxYPMM6r3XA
