Principles and Rules for Section → Data-Warehouse/Mart
|
|
| This page presents the list of field tips which are relevant to the current section. For the complete listing of Field Tips, please go to Field Tips Home Page |
|
To filter your listing for a specific section, please click the relevant hyperlink : |
| Execution Making-it-Happen -
Discipline of Execution → 'Executable' Strategy → Focus & Money-Machine → 'Value' management → 'Capabilities' management → Execution Intensity → Human Capital → Resilience & Flexibility → Anticipation & Intelligence → Execution Scorecard → Leadership & Work-Discipline → Entraprenurial Execution → |
| Enterprise Intelligence -
BI End-to-End → Data Quality → Data-Warehouse/Mart → Data Analysis/OLAP → Metadata Management → |
| Functional Management -
Customer Management → Sales & Distribution → |
| Vendor- IT Tool Domain -
Vendor-tool Evaluation → BI End User Tools → BI platform Tools → Data Management Tools → |
|
|
|
|
|
|
Build your internal capabilities for BI modeling and analysis
|
When it comes to developing an 'organizational brain' around BI, one should build internal capability to become technology and vendor-independent, without giving away the benefits of these two enabling entities.
|
|
| |
| |
|
Pre-designed BI frame-work and Models (LDMs) is double-edged sword
|
Pre-configured BI frame-works claim to be plug-and-play BI set-up with respect to a given industry and function. If it works for you, it can be a blessing, but if it does not, it can be much more pain, compared to doing it from scratch.
|
|
| |
| |
|
Articulate better for better BI decisioning
|
BI initiatives provide unique situations on decisioning. It is difficult to make a business customer to decide on which information he will and which he will not. This asks for better and clearer articulation to the business to enable faster decisioning.
|
|
| |
| |
|
Fix Business Intelligence at functional level first, before making it enterprise-level
|
There are many reasons on why one should let the data marts and BI evolve at functional level, before making BI an integrated enterprise platform. A big-bang data warehouse house is an impossible task and letting BI evolve at functional level will establish a solid base. However, one needs to have a robust and central governance to ensure that this evolution is happening in a consistent manner.
|
|
| |
| |
|
Big-Bang Enterprise Data Warehouse is a pipe-dream
|
A big-bang enterprise data warehouse is infeasible. One needs to have a phased strategy. The data warehouse at enterprise level has great benefits, and should be the point of arrival. However, there will be many milestones to cover before reaching the POA.
|
|
| |
| |
|
Using Synonyms and Views
|
Synonyms and Views create a user friendly layer for user access and querying. They also enable to make underlying database changes with minimum impact on user access interface.
|
|
| |
| |
|
Don't be guided only by the business requirements for your Business Intelligence
|
Business requirements around your BI initiative will be one of many other inputs and considerations, which will drive your BI model, design and architecture. Business information requirements keep on changing and by the time you deliver your Data Warehouse, chances are than a fair proportion of the information requirements might have changed.
|
|
| |
| |
|
For Core BI platform go for a single, established and robust player
|
Core Business Intelligence Platform includes Data Warehouse Server, Metadata management tool, ETL tool and OLAP server. These are the foundation/plumbing/infrastructure elements of your business intelligence. We recommend for you to go for a single and financially robust vendor for as many of these elements as possible.
|
|
| |
| |
|
Some considerations for Infrastructure in Data Warehouse
|
Data Warehouse infrastructure estimation is complex, as it is difficult to judge the use the Data Warehouse might be put to. Here are some considerations, which can help you to better estimate.
|
|
| |
| |
|
Keep Aggregates and Details data in different Fact tables
|
One needs to store data in most granular form in the data warehouse. However, in certain situation, you may need to supplement the same with summary data to enhance the performance for predictable queries. One should have the detailed and aggregate data stored in separate fact tables.
|
|
| |
| |
|
Handle Each Time-stamp in the Fact Table as a separate dimension
|
Typically there are multiple dates associated with a fact table. Place each data as a separate dimension.
|
|
| |
| |
|
New Data Standards- What about existing data and applications?
|
As an organization develops enterprise level data standards, it has to figure out the approach and plan for existing data and applications. One cannot have a big-bang conversion approach. Data Steward needs to drive a road-map, which focuses on the business case driven priorities, and also does a piggy-back on the mega IT initiatives.
|
|
| |
| |
|
How to integrate stand-alone BI environments- Gradual Approach
|
We recommend to go for phased approach for BI environment integration against a big-bang method. The reasons are lack of business and IT stamina, testing our assumptions, maintain stability and to develop our expertise. The phased approach should first go for integrating the plumbing work like ETL, followed by more front-end integration.
|
|
| |
| |
|
Master Data Management- Making a Right Start
|
Here is one brief de-mystifying and context-setting field-tip for master data management, which talks about the mind-set with which we should approach MDM. MDM is 75% definition+discipline and 25% MDM platforms and automation.
|
|
| |
| |
|
Field Tips Series- Streamlining & reducing cost of Business Intelligence- Evaluate Open Source
|
You can reduce your costs, by gradually testing and adopting Open-Source BI in a select set of areas. Open-source BI is gradually gaining attention. Open Source BI is essentially business intelligence software which is open-source. There is a difference within open source and commercial open source. In this page we are talking about commercial open source (don't even think of pure open source, if you are a medium level enterprise and above), which is not free of cost but of minimal cost, with adequate support and services infrastructure. Pentaho and Jasper are examples of commercial open source.
|
|
| |
| |
|
Field Tips Series- Streamlining & Cost-Reduction in Business Intelligence- Governance & Standards
|
Governance & Standards essentially points to the consistency and robustness of managing change in the BI environment. The key issue behind high cost of BI is varied IT platform, standards and processed, which have additional over-head and also make BI inefficient & defective. As an organization we can undergo different types of changes, which have to dealt by effectve governance and standards.
|
|
| |
| |
|
Field Tips Series- Streamlining & Cost-Reduction in Business Intelligence- Licensing & Maintenance Contracts
|
Most of the large organizations have multiple contracts and licensing regimes for same platforms. For example, you may find multiple licenses for business objects in different markets for the same company.
|
|
| |
| |
|
Field Tips Series- Streamlining & Cost-Reduction in Business Intelligence- Consolidate Data-Marts
|
Every organization has organically and chaotically evolved on Business Intelligence. This has led to plethora of IT platforms and business applications, with cost and inefficiency over-head. This page shares the issue of Data-Marts proliferation, solutions and practical scenario in implementation.
|
|
| |
| |
|
Avoid Pure MOLAP
|
Pure MOLAP limits your view and reporting only to the level of aggregates.
Today's business needs demand a drill down to the transaction level details.
Unless you have a significantly cheap and short term option to purchase, avoid
pure MOLAP.
|
|
| |
| |
|
Excel is the competition, which should be challenged
|
One of the most common questions you will phase is that "why can't we do the
same thing in excel? Just import the data and create the pivots and bingo..".
Be ready with the arguments of Data Integrity, Historical Data, Integration
across the system, common definitions etc..
|
|
| |
| |
|
Homework before interviews is must (Business Requirements Phase in Data Warehouse)
|
Most of the business themes, challenges, issues and information
requirements are available in the PPTs, Minutes of the meetings, business plan
submissions etc.
|
|
| |
| |
|
Add extra buffer for ETL phase
|
If you feel that you will take four weeks to complete ETL Design
to testing, add another four weeks to it. Chances are that you will find lot
of surprises.
|
|
| |
| |
|
Don't wait for universal models for Data Marting
|
While it is desirable to have the universal dimensions for
individual data marts, one should not let a data mart project to get delayed
even by a day for this purpose.
|
|
| |
| |
|
Go sequentially in performance management
|
Performance Management initiatives typically have 3 stages. Implementing these
stages in sequential manner improve the chances of results.
|
|
| |
| |
|
Don't rely too much on Meta Data Tools to enforce Business Intelligence
|
Meta Data Tools in today's world have great capabilities , however it
is safer to have the business rules enforced through the dimensional
models and databases within OLAP, Staging and Data Warehouse.
|
|
| |
| |
|
Dimension Attributes as NULL
|
You should generally not leave dimension attributes as NULL.
|
|
| |
| |
|
Handling of Null foreign Keys in fact tables
|
There are always situations where the key linking the fact table to a dimension
table is NULL (meaning it is not available or applicable).
|
|
| |
| |
|
Handle different units of measure in the same fact table
|
Create separate fields for each different unit of measurement,
either by writing the conversion factor or the converted value.
|
|
| |
| |
|
Recording events in multiple currencies
|
The approach is similar to the field tip "Managing time-stamps
across multiple timezones" . We should have one measure in the local currency
in which the transaction took place, and one in the standard currency to
which the multi-currency transactions roll-up to.
|
|
| |
| |
|
Managing time-stamps across multiple time-zones
|
When you are recording events across multiple time-zones, its better to record
both the local time as well as a common standard time (say GMT)
|
|
| |
| |
|
Do not separate the parent and child line item data
|
In cases like invoice, purchase orders and Job Card, there are header items
and detailed items. One should always combine both the levels into a single
fact table.
|
|
| |
| |
|
Handling Sparse Dimensional tables
|
In case you have a dimensional tables where significant proportion of instances
have majority of the fields as null data in the records, one should look at
creating a snowflake to save diskspace, incase the database system is a fixed
field records (where null fields also occupy disk space).
|
|
| |
| |
|
You may not be a able to have a perfect ETL
|
Due to various data cleansing and transformation activities done, the transformed
data and the data in the source systems may not match 100%.
|
|
| |
| |
|
Always Use Conformed Dimensions
|
Conformed Dimensions concept says that if you have a customer dimension, the
contents of the dimensional table, the name of the dimension and its attributes,
data structures, key assignment etc should be exactly the same. In other words,
there should not be two different customer dimensions in the same data warehouse.
|
|
| |
| |
|
Dimensional model has to be aligned to the Entity-Relationship
|
The cardinality of relationship between any two entities (refer entitiy
relationship model ) have to be reflected equally in a dimensional model or
else you will get an information management/business intelligence platform which
is inconsistent with the organization data model
|
|
| |
| |
|
Checksum Approach for identifying the changed records from source systems
|
With many records changing every day, it's a challenge for
an integration system to identify the changed records out of millions of records
existing in the source system.
|
|
| |
| |
|
Conformed dimensions are must for cross-drilling
|
If you want to have a good cross-drilling capability, its important to have
conformed dimensions across multiple fact tables.
|
|
| |
| |
|
Maintain a trail of the key dimensional elements from source system to loaded
|
Sometime due to specific query needs and for audit-ability of your information,
you would need to maintain a link between source to end information.
|
|
| |
| |
|
For a Data Warehouse/Data-Mart solution, analyze well, but be decisive
|
Data Warehouse is one solution which can be implemented in million different
ways. People have to maintain a right balance between analysis and time-bound
decisions.
|
|
| |
| |
|
Documenting your data-integration system
|
There is no data-integration system, which can fully self-document the data
integration flow and process.
|
|
| |
| |
|
Aggregation Queries on slowly changing Dimensions
|
There are various ways to have the aggregate queries on dimensional tables.
However, one needs to have some other tricks for slowly changing dimensions.
|
|
| |
| |
|
Business Intelligence competency groups should be well-linked with business
|
As business intelligence team grow in size and responsibility, it tend to become
a department within itself, with a group heavy weight "know it all" business
analysts, architects and data managers. Overtime they tend to loose touch with
business.
|
|
| |
| |
|
Data Marts should be ideally based upon a business process and not on a department.
|
Data-Marts as they start for a function or a department, can still be designed
on a process or a theme.
|
|
| |
| |
|
Dimensional models can be extensible and scalable
|
If there are new business requirements which need a change in your current
dimensional model, you may not have to build net set of schemas.
|
|
| |
| |
|
You cannot have a super-flexible Data warehouse
|
While storing data at the most granular level , makes a data-warehouse flexible
to unforeseen queries. However, one does need to keep the key purpose behind
the creation of a schema or a cube in OLAP in mind.
|
|
| |
| |
|
Keep the same names and definitions for all data elements
|
This tip is basically for not to cut corners, when you are
designing your dimensional model. Having some mapping tables to map inconsistent
names of same data elements, is an almost criminal short-cut to having same
names in the dimensional model design itself.
|
|
| |
| |
|
Data Normalization is not the best approach in Dimensional modeling
|
Just like transactional system, you have to be a cautious on to what extent
you want to normalize your dimensional tables in Data Warehouse.
|
|
| |
| |
|
Store as much detailed and granular data in data warehouse as possible
|
DW implementation is a big initiative. The use of data warehouse
is unpredictable and may not be limited to summary level queries and for data
analysis only.
|
|
| |
| |
|
Data Warehouse application is not limited to Analytics
|
Analytics is not the only use for the data warehouses.
Edited By: Sourabh Gupta
|
|
| |
| |
