Dylan’s BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Supply Chain Questions

Posted by Dylan Wan on September 28, 2014

The key question are about inventory and orders.

  • What is our inventory status? by product and by location?
    • Do we have enough inventory on hand and in process to meet customer demand?
    • How can we maximize customer satisfaction while minimizing cost and size of inventory on hand? Both over stocking and under stocking are bad
    • Do we have enough finished goods to our backlog orders?  How much raw materials, work in process, and finished goods on hand?
  • How well is our order fulfillment process performing?
    • What are the top orders in backlog?  How can we prioritize strategic customers?
    • How can we ensure fulfillment on time?
    • Which products have the longest fulfillment cycles?
  • Where are the bottlenecks in order management process?
    • What is our order management cycle time? How long does it take for booking an order?
  • Do we know why customers cancel orders and return products?
    • How much revenue was lost due to order cancellations or returns

Typical dimensions: By Product, By Location, By Customer

Different roles: Director of Sales Operations, Inventory Managers, Order Management Managers

Integration with other systems:

  • Customers and pipeline information from a CRM application
  • Product information from ERP
  • Workforce information from Human Resource
    • Align the training plans to skill gaps in critical product area
      • Correlate staffing levels with order volume and product plan

Posted in BI Application | Tagged: | Leave a Comment »

Analytics 3.0

Posted by Dylan Wan on January 2, 2014

One of the books I like most about data warehousing is the book e-Data, written by Jill Dyche.  Here is a paper she co-authorized with Thomas H. Davenport about Big Data:

Big Data in Big Companies: Executive Summary

Thomas H. Davenport is the author of the book Competing on Analytics, which is also a book I own and found interesting.

I cannot agree more about these two points they mentioned in the above executive summary:

  • Overall, we found the expected co-existence; in not a single one of these large organizations was big data being managed separately from other types of data and analytics. The integration was in fact leading to a new management perspective on analytics, which we’ll call “Analytics 3.0.”
  • “It’s important to remember that the primary value from big data comes not from the data in its raw form, but from the processing and analysis of it and the insights, products, and services that emerge from analysis.”

I feel that what customers may really need is an integrated prepackaged BI apps that is built on an integrated BI platform and the associated tech stacks that can handle big data together with the other existing enterprise data.  Building an non-integrated big data data warehouse is time consuming and may not get a good balance of the cost and the benefit.  The non integrated big data data warehouse may work well for the specialized firms for special purposes.  However, when we think of leveraging the big data technology in the enterprise, integration with the existing data warehouse is the key for success.

 

Posted in Business Intelligence | 1 Comment »

Data Warehouse for Big Data: Scale-Up vs. Scale-Out

Posted by Dylan Wan on January 1, 2014

Found a very good paper: http://research.microsoft.com/pubs/204499/a20-appuswamy.pdf

This paper discuss if it is a right approach of using Hadoop as the analytics infrastructure.

It is hard to argue with the industry trend.  However, Hadoop is not new any more.  It is time for people to calm down and rethink about the real benefits.

 

Posted in Business Intelligence | Leave a Comment »

Technologies behind Oracle Transactional Business Intelligence (OTBI)

Posted by Dylan Wan on July 25, 2013

Oracle Transactional Business Intelligence (OTBI) is one of the business intelligence solutions provided as part of Fusion Applications.

To build a real-time BI, the major challenge is to make sure that it can perform and has no or minimum interfere to the core objective of the transactional application, the online processing.

This is the reason why we need Oracle Business Intelligence Applications (OBIA) for Fusion Applications.  The idea is to keep the minimal processing of detecting changes and capturing changes in the transactional system and leave everything else, such as, preparing and consolidating the data for reporting, to BI Applications.

Here are some of the technologies available to make OTBI possible:

1. SQL Trimming from ADF

ADF stands for Application Development Framework.  It is the application development framework used in developing Fusion Applications.  In general, it is a declarative metadata driven framework to let the application developers to define the data model, define the data access layer, define the UI rendering, put the validation logic and processing in the middle tier.

The underlying data model, in most of cases, is still the relational model defined in the Fusion Apps transactional database under the 3rd NF design.

The key enabling technologies provided from ADF to OTBI is the “Composite VO” or “Composite View Object”.  For me, it can generate the database SQL for us based on the metadata.  Unlike the database technology using the database view, ADF engine can look further down to the entity objects included in the view object and selectively choose which entities are needed in a given SQL.  If the view object includes two tables (EOs), one primary EO for data at the line level, and the other EO for getting the parent data, When the query (Composite VO) does not include any column from the parent EO, the SQL generated by ADF will not include the table in the join.

This is a superior technologies, comparing to the old technologies of building the business views.

If you are a Java programmer and would like to get the feeling about what Composite View Object looks like and how it works, here is a good blog post:

Do you know what is a Composite View Object?

2. BI Platform – ADFQuery to Composite VO

This enabling technology is provided by BI platform and available as a Java library. It adds a layer on top of the ADF composite VO.  Without writing the Java code, it generates the codes of creating the composite VO on the fly.  It allows us to query the data from the ADF engine by sending them a XML block called ADFQuery.

This doc shows some of the ADFQuery XML blocks.

http://docs.oracle.com/cd/E15586_01/fusionapps.1111/e20836/adf.htm#BIEMG3435

To see better examples, you can find them in NQQuery.log files.

It is a query language like SQL.  You have the section  for the column projection, the join criteria using view links, and the filter using view criteria.

Here are other enabling technologies behind OTBI.

3. ADFQuery generation from BI Server

4. SQL By Pass Database

5. Relational to Dimensional Mapping (Physical Layer to Business Model Layer)

6. SELECT Physical in initialization block

7. ADFQuery Initialization block

8. Physical Lookup function from BI platform

9. Logical Lookup function from BI platform

10. Data Security enabled at the VO layer via Fusion AppCore

11. Applcore Tree Flattening

12. Applcore Business Intelligence Column Flatten VO (BICVO)

13. BI Flexfield VO generator

14. BI Extender via Import Wizard

15. BI View Object created based on the BI EE Logical SQL (BIJDBC)

16. Effective Date VO with as of date filter

17. ADF Application Module to BI variable interface

and more…

Regardless, the goal of these technologies is to enable the users to get the real time data access to the Fusion Apps.  There is really little or no much we can do for providing the feature like data snapshot, pre-built aggregate, multiple currencies, data consolidation and conformance, cross subject area analysis, and the most important, the query performance with complexity logic to be available in a reasonable time without the interfere to the transactional system.

 

Posted in BI Application, Business Intelligence, OTBI | Leave a Comment »

Multiple Language Support in Oracle BI Applications (1)

Posted by Dylan Wan on June 27, 2013

In release 11.1.1.7.1, one of the major feature is to supported the translated data so you can have a global deployment of data warehouse.

In the past, before release 11.1.1.7.1, the BI platform and BI applications support the translated displayed prompts and labels, so the users that use different language can use the BI Apps and see the instruction and navigation and displayed labels in their own language.  However, when they are seeing the data stored in the data warehouse, they are seeing the data in one language only.  Even some of OLTP applications such as E-Business Suite, PeopleSoft, and Siebel Applications are all supported multiple languages in their database, BI Apps only extracted the data from one language that is specified in DAC as a global variable.

In release 11.1.1.7.1, BI Apps data warehouse model design officially deliver the true multiple language data warehouse.  The feature can enable the deploying organizations which have the users from different countries to share a global deployment,  They can enable multiple data warehouse languages as part of the Bi Apps configuration, and the ETL process can extract the language specific reference data from the OLTP applications.

When the different users from different countries login to the same application with the same role, even they have the same data access, they can see the data displayed in their own language.

 

 

Posted in Business Intelligence | Leave a Comment »

Using Load Plan for managing your ETL task in BI Apps 11.1.1.7.1 (1)

Posted by Dylan Wan on May 14, 2013

One of the major change introduced in BI Apps 11.1.1.7.1 is the way how we manage the ETL task sequence and trim the unnecessary tasks.

This functionality was accomplished earlier using DAC.  The problem we frequently faced was that the DAC repository and the INFA repository are maintained as two separate repositories.  We have to sync up the name of tasks exactly in order to use DAC to manage the task execution of the Informatica workflow tasks.

Load Plan and Load Plan Generator was designed for addressing this requirement.

Here is a good article that describes the story.

Load Plan Generator – An Inside Look

Posted in BI Application, Business Intelligence, DAC, Data Warehouse, ETL, Infomatica, OBIA, ODI, Oracle, Oracle Data Integrator | Tagged: , , , | Leave a Comment »

Incremental ETL in BI Apps 11.1.1.7.1 (1)

Posted by Dylan Wan on May 10, 2013

The data warehouse needs to be refreshed periodically to make sure that the data warehouse have the up to date information.  The Incremental Load is the regular ETL process running everyday.  The frequency can vary, but it is commonly seen as nightly job.  To ensure that the incremental ETL job can be finished within a limited time window, incremental ETL load typically identifies the changes happened in the source system and only deal with the delta to avoiding doing a Full ETL Load.

In BI Apps 11.1.1.7.1, if you are not leveraging the Golden Gate and the SDS (Source Dependent Data Store), the incremental ETL is accomplished by directly comparing the last update date captured in the source table with the last refreshed date.

BI Apps 11.1.1.7.1 ODI based ETL does not use separate ETL tasks for incremental load and full load.  The logic is determined by whether the data has been executed before.  In other word, it is based on whether the data has been refreshed.  This was a productivity gain for BI App developers and also make the support of incremental ETL becomes clear.  In the past, you may see the Incremental Load Informatica Workflow and Full Load.  Every time when there is a logic change, the workflows need to be modified.  It was a source of code bugs before.  By using a better ETL architecture, we eliminate the needs of maintaining two separate tasks.

Here is a high level summary of how the incremental ETL is accomplished in BI Apps 11.1.1.7.1 ETL:

1. Use the W_ETL_LOAD_DATES table for tracking all the ETL execution

2. Use the IS_INCREMENTAL Variable for holding and passing the state

3. Refresh Variable in a Package Step.  If there is an entry in the W_ETL_LOAD_DATES, perform an incremental ETL.

4. Use the RUN_INCREMENTAL to turn on and off the incremental filter

5. Optionally, use the ODI Variable Evaluate Step to branch the Full and Incremental ETL logic within a package

6. Table Maintenance, take the ETL Load Date information to determine the operations, including Drop Index, Truncate Table before Full Load, Rebuild the index after Load, and Analyze Table after Load, tec.

 

 

 

 

 

 

 

Posted in BI, BI Application, ETL, ODI, Oracle, Oracle Data Integrator | Tagged: , , , | Leave a Comment »

BI Apps 11.1.1.7.1 (BI Apps in ODI) is available in OTN

Posted by Dylan Wan on May 6, 2013

First Go to OTN

www.oracle.com/technetwork

Go to DOWNLOADS -> Middleware

You will see Business Intelligence Applications under Middleware

You see both 10g and 11g.  Click on Oracle Business Intelligence 11g downloads

You will see BI Apps 11.1.1.7.1 here:

Oracle Business Intelligence Applications (11.1.1.7.1)

 

Posted in Business Intelligence | Leave a Comment »

Benefits of using ODI as the ETL for BI Apps

Posted by Dylan Wan on May 2, 2013

Oracle Data Integrator is being used as the ETL tool for BI Apps. 

Here are some benefits:

1. Productivity

It tasks less efforts for developers to write a ETL map comparing  to other ETL tool.

2. Define ETL flow patterns in KM

The reason why now we can take less effort is that the flow is now inside the knowledge module.  You pick which flow logic you want to use by picking which KM to use.  You configure the flow for your specific requirements by specifying the KM options. The rest is to define the source SQL and the mapping expression.  It pretty much that it is for any typical job.

In a prebuilt ETL adaptor solution, like what BI Apps has, we have a set of patterns.  Unless the specific task requirement is unique, you do not need to write any new KM and any complex logic.

3. Code Generation

ODI provides SDK.  Although the ETL programs themselves are declarative in the ODI repository, the ETL program can be generated by using the ODI SDK.  Not just the ETL flow logic is centralized inside KM, the ETL meta can also be generated.

4. User Defined Function

User Defined Function is one of the magic behind supporting different platforms.  You use the same function and do not need to worry about the different SQL function supported in different database platforms, ODI engine will compile and replace it with different implementation for you.

It is not a required feature for building a custom ETL.  It is a required feature for prebuilt ETL adaptors which need to support source apps, such as Siebel Apps, JDE Apps, PeopleSoft Apps in different database platforms.

5. Data Lineage

The data lineage within ETL tool becomes so easy.  You can easily identify which ETL task populates a given table column, and for a given source table column which ETL tasks are using it as the source.

Actually although it may not be recommended the ODI metadata repository can be queries directly from database. It makes ETL auditing becomes possible.  It is a productivity grain since you can spend less quality assurance time to detect issues and it is a quality gain as you get the ETL adaptor with high quality.

6. Performance

When the source data and target data are within the same database, the middle tier ETL engine becomes an overhead.  We can better leverage the power from the database for ETL transformation.

This is the benefit we expect from a E-L-T solution.

For cross database deliveries, many optimization options can be used.  A simple database link over different database instances can be used.  Also using Oracle Golden Gate to replicate the data is also possible.  With the above benefits of KM and UDF, the same ETL codes can be switched to run the extract process against the replicated data or run against the remote source apps database.

7. Load Plan

The ETL task dependencies and trimming can be accomplished via ODI Load Plan within the same ODI repository.  This architecture design eliminates the needs of duplicating the ETL task metadata to a separate task execution sequencing and management tool, like BI Apps Data Warehouse Administration Console.

You can define your ETL job, execute your ETL job, monitor your ETL jobs within a single tool, the same ODI studio.

(to be continued…)

Posted in Business Intelligence | Tagged: , , , , , , , , | Leave a Comment »

BI Application development team is hiring

Posted by Dylan Wan on February 26, 2013

BI Application development team is hiring

They are hiring a senior application engineer.  The job requires experienced ETL and BI developers with 3- 7 year experience.

 

Posted in Business Intelligence | Leave a Comment »

Why use GoldenGate in ETL?

Posted by Dylan Wan on February 15, 2013

The traditional way of doing ETL nightly is no longer satisfying the requirements.  In the past, we have the ETL “window”  to perform ETL.  The ETL processes typically run as daily or nightly ETL.  We can use the downtime to execute some processes in the OLTP systems to extract their data while we can still avoid the impacts to the transaction processing system.

Nowadays, there are many cases of no downtime and no ETL window.

GoldenGate provides the unique benefit of constantly feeding the data from the OLTP system.  The data are replicated spreading over the day without introducing much impact to the source system.

 

 

 

Posted in Business Intelligence | Tagged: , , | Leave a Comment »

Extending your BI using Fusion Application Composer

Posted by Dylan Wan on December 12, 2012

This is a very cool feature I have not seen for years.

We have heard the demands of customizing the reports from customers for years.  The deploying company almost always wants to create some custom reports.  One of the selling point of the Oracle BI platform and Oracle BI Apps has been how easy it is to extend the BI and how these extensions can be preserved during upgrade.

This Fusion Apps Application Composer is pushing this further.

What it is trying to do is to allow bring the tasks used to be done by “BI architect” to the hand of the BI user or departmental level of admin.  Also, it brings the extension to the world of the SaaS based deployment.

The center of the BI platform is to have the metadata defined to model the data and make it possible to generate the SQL behind the scene to generate the queries.  In the past, we had to use the BI Admin Tool to modify the metadata.  The BI metadata maintenance requires a specialized tool, the BI admin tool The Fusion Apps Composer now supports a specific pattern of extension – creating the custom subject area, to use a web based tool, without opening the BI Admin.

The tool actually extends the BI Apps OOTB repository file (rpd) in real time.

You can see a list of Fusion Apps business objects defined in ADF VOs and you build the custom subject area by choosing which objects to be the primary object for your fact, and which related objects as the sources for your dimensions.  After completing a guided web based “Wizard”, it creates the physical layer objects, logical objects, and presentation layer objects automatically!  Within an hour, you are creating a new subject area.

It won’t be able to replace all the advanced features available from the window based admin client tool.  However, it can satisfy a set of SaaS customer’s reporting requirements.

In my view, it is still not well documented – Customizing Objects and Customizing Analytics.  It seems a hidden powerful feature from BI content extension perspectives.

This feature is currently only available for Fusion CRM only.  Not sure if there is any plan to make it available from other pillars.  It is worth to watch how it evolves.

Posted in BI, BI Application, Business Intelligence, OBIEE, Oracle, Oracle BI Suite EE, OTBI | Tagged: , , , | 2 Comments »

ODI Shortcut

Posted by Dylan Wan on May 25, 2012

ODI provides the shortcut feature since the 11.1.1.6.0 release.  Shortcuts look like symbolic links in Linux file system and shortcuts in windows. You keep a reference to an object that exists in another folder.

The symbolic link feature has been used widely in prepackaged apps development to handle multiple releases of the software product internally. Typically multiple top folders are created, one for each release.

Many codes can be shared from an old release to its next release.  Instead of copying the codes, a symbolic link can be created in the folder created for the new release to point to the code in the folder created for the old release.

ODI shortcut feature was created for a similar purpose.

ODI allow you to create shortcuts for these objects under the ODI Project:

  • Interface
  • Package
  • Procedure

Shortcuts are for the codes that the developers created and modified.  It try to maximize the code reuse.  You create a code in a folder, and reference it in another folder, after you change the code in the original folder, the reference in the other folder also get the change.  You do not need to make the same change over again for each release.

I also notice that the scenarios are not shortcutable.  The scenarios (package scenario and interface scenario) are executable objects.

This dsign means that the scenarios should be regenerated after the shortcuts are created.  It also means that the scenario should be regenerated after the code in the base folder is modified.  It is kind of strange, but it is a design to help you to maximize the code reuse.  Each interface or a package is tie to an ODI model and model could be source apps version specific.  You want your code to run against a different model even the code is a shortcut.

Folders are also not shortcutable. ODI Folders are not regular folders like the way how we think in the file system directory.  Each folder carries a property called “Release Tag”.  The release tag can help redirecting the model to be used in generating the scenarios.

 

Posted in Data Warehouse, ODI, Oracle, Oracle Data Integrator, OWB, Sunopsis | Tagged: | Leave a Comment »

Use ODI SDK in Groovy

Posted by Dylan Wan on May 12, 2012

ODI 11.1.1.5 includes a built-in Groovy editor in the ODI studio.

If you run the groovy script within ODI studio, it automatically run against the ODI repository you are connecting to.

You can do a lot of the tasks that you do in ODI studio in ODI SDK.

I tried to use ODI SDK to create a project, create a folder, create a subfolder, create a short to an interface, create a variable, and update a variable.

All of these scripts are directly created in the Groovy Editor in the ODI studio.  I know Java but I have never used Groovy.  The learning curve is almost none.  If you know basic Java programming, using Groovy to update ODI repository should be easy for you.

Posted in Business Intelligence | Tagged: , , | Leave a Comment »

About Big Data (2): SQL and No SQL

Posted by Dylan Wan on April 14, 2012

This is a good article written by Alan Gates, Pig architect at Yahoo!

Comparing Pig Latin and SQL for Constructing Data Processing Pipelines

He compares Pig Latin, a query language for Hive with SQL, the query language for relational database. He gave a good example that helps those who know SQL to understand the differences.

The query language is not difficult to write.  The key point is that it lets the programmer to control the execution plan.  SQL on the other is a higher level abstraction that hides the execution plan from users.  It does not means that we do not really need to be worried about execution plan.  We do.  It is a key for performance tuning.

A good practice for SQL developers is to get the explain plan and to see how Oracle plans to execute the query and optionally use hint and index to control the execution.

Comparing to this, the approach of directly telling the system what to do may seem easier.

But it means that the database system to handle big data does less things, not smarter.

Posted in Big Data, Data Warehouse | Tagged: , , | 1 Comment »

Tutorial: Create ODI Repositories

Posted by Dylan Wan on April 14, 2012

This step is not required if you are working on an existing repository.  If you are doing a new development project, or just want to get some experiences on using ODI, you may need to create ODI repositories yourselves.

A typical ODI deployment includes two repositories: Master Repository and Work Repository.

ODI provides two different tools to create them.

Here is a good tutorial page available from oracle.com:

http://st-curriculum.oracle.com/obe/fmw/odi/odi_11g/ODImaster_work_repos/ODImaster_work_repos.htm

The tool does not create database users.  Here is my script for creating the database users:

create tablespace ODI_MREP
datafile 'C:\APP\DWAN\ORADATA\DWANDB\ODI_MREP.DBF'
size 100M autoextend on Next 100M Maxsize unlimited;


create tablespace ODI_WREP
datafile 'C:\APP\DWAN\ORADATA\DWANDB\ODI_WREP.DBF'
size 200M autoextend on Next 100M Maxsize unlimited;create user ODI_MREP
identified by ODI_MREP
default tablespace ODI_MREP
temporary tablespace TEMP
quota unlimited on ODI_MREP;

create user ODI_WREP
identified by ODI_WREP
default tablespace ODI_WREP
temporary tablespace TEMP
quota unlimited on ODI_WREP;

grant connect, resource, SELECT_CATALOG_ROLE  to ODI_MREP, ODI_WREP;

If you are using RCU, you will not be able to create Work Repository and Master Repository separately.  In my opinion, RCU is a poor tool and lacks many basic features. I will stay away from it.

Posted in Business Intelligence, ODI, Oracle Data Integrator | Leave a Comment »

ODI Topology

Posted by Dylan Wan on April 13, 2012

ODI has a very clever design to isolate the deployment objects from design objects.

The designer only interface to deployment information (topology) via logical schema.  The project objects only indirectly get the dependency via model.  The “context” can determine the physical resources and allow you switch among the development, testing and production env.

From prebuilt package apps perspective, the codes are shipped under a set of logical schema.    The logical schema represents the assumption about the various source and target systems.

The concept of “context” really enables the support of unlimited multiple source instances using a single set of codes.

Posted in BI, Business Intelligence, ETL, ODI, Oracle Data Integrator | Tagged: , | Leave a Comment »

About Big Data (1)

Posted by Dylan Wan on March 7, 2012

Recently I read several articles and books about big data.

I found that many use a very funny definition to define big data.

Big data is the data that you typically cannot handle in the database.  It is bigger than the size of the data you have.

What?

It is a joke I told my daughter during the dinner.  Someone said that they are selling a very good car.  You asked them:  How good is it?  They said that their car can take more people, run faster, much more comfortable, provide better safety, and cheaper.  When you ask them about more details, they keep saying that it will better than what you have. Will you buy it.  She felt that the sale person is a liar.

I do believe that the big data problem does exist today, but it is a special kind of data and requires some special way to handle.

It is not everything.  It may require a new way that does not exist before.  It may be also likely to require some ways that have been there for some time, but we just did not pay attention to it.

Posted in Big Data, Data Warehouse | Tagged: | Leave a Comment »

Dimension Hierarchy Support in Data Warehouse(2)

Posted by Dylan Wan on December 8, 2011

I am describing the column flattening table in this post.

The column flattening table is a typical data warehouse modeling pattern for addressing the dimension hierarchy requirements described in my last post Dimension Hierarchy Support in Data Warehouse (1).

This technique can be considered as a denormalization of the parent information into the child records.

Here are some of the rules we followed to populate in a column flattening data warehouse table:

1. Each record is representing a node that can have transaction data associated with it.

If this the product category hierarchy, each record is created for a product category.

If this is the employee reporting hierarchy, each record is an employee.

The surrogate key of this table will be the primary that can be joined from the fact tables.

The fact data can thus be associated with each of the nodes.

Typically, the ancestors also have separate rows.  The reason is that the ancestors can have fact data associated with them.  If the ancestors never have data directly associated with them, there is no need to create separate records for the ancestors.

2. Parent / Ancestor columns, one set of such columns for each level

I call these “level attribute columns”.

If this is the employee reporting hierarchy table, the employee name will be a good level attribute.  If this table is created for product category hierarchy, the name of the product category will be a good level attribute.

The level attribute is the column that you would like to show on the report and would like to drill from it. When we drill down from one level to the next level, you would like to see the attribute shown in the next level.

Typically they are identifying attributes.  They are unique or almost unique so you can use it to distinguish one from the other.  For example, the employee name in the employee table is one of such attribute.  Employee Number, if you are frequently use it directly, could be a candidate.  The gender of the employee will not be considered as an identifying  attribute.  You can show them as level attribute but it will not be a very good attribute for the users to drill down.

You do not want to capture too many level attributes.  The width of the table has significant impacts on the query performance.  The level attribute columns times the number of levels you support plus other system columns and base level attribute will be the  number of column supported in the column flattening table.

I feel that more than six columns should not be allowed.

3. The number of the levels

Column flattening table are denormalizing the parent attributes.  It will have the limitation on how many columns can be supported as part of the table design.  The number of levels supported is really depending on the given dimension.  It is a case by case decision depending on the nature of the entity.

It is also related to the usability of the hierarchy.  Unlimited number of levels is not reasonable since you are not able to use it properly.  When you use the dimension hierarchy, you are adding one level at a time while drilling.  How many column can be shown on the screen?  Unless the UI design allow you to view each level relatively, the parent / ancestor will be on your screen.

The number of levels will increase the overhead in the BI query as well as in the data warehouse ETL process.  It will be unwise to create a huge numbers of level to the column flattening table.  It is unnecessary cost to those people who do not have such big number of levels.

4. Populate the level columns from the top to the bottom

The level attribute columns are populated from top to the bottom.

This is related to how the table is being by the BI tool.

As mentioned in my last post, the assumption is that the user would like to view from the top or higher level and drill down to see more details.  The data populated in the top column are the top most node in your hierarchy.  The second level from the top is populated in the 2nd column from the top column.

This rule is best described using examples.  Let’s say that you have an hierarchy with four nodes:

A is a parent of B and B is a parent of C.  B is also a parent of C.

You will have four records:

A

B

C

D

In all four records, the top will be populated with A

A: A

B: A

C: A

D: A

In all four records, the 2nd level from the top will be populated with B except the top record.

A: A > A

B: A > B

C: A > B

D: A > B

When we reach the next level, some data warehouse design, such as Oracle BI Apps, follows a padding process. The idea is that you can see the data at any level.  The data should still have proper break down.

The leave node will be repeated.  So you can see the next level as

A: A > A> A

B: A > B > B

C: A > B > C

D: A > B > D

Although the hierarchy only have 3 level, but data warehouse table design may have more level columns that can support the hierarchy with the deeper hierarchy.  The next level column will be populated as:

A: A > A > A > A

B: A > B > B > B

C: A > B > C > C

D: A > B > D > D

 

Posted in Business Intelligence | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 37 other followers