Thursday, July 30, 2015

OBIEE Visualization - D3 Charts Part 4 (Hexagonal KPI's)

Hi guys, I am back with new visualization in D3 and OBIEE, this time we have honeycomb pattern showcasing the KPI's that will change color according to some conditions. Given below is the final version of what we are going to do today

So let's begin with understanding what we are trying to achieve here. The above image shows the shipments of commodity types by region(dummy data - not to scale). This KPI's will change color depending upon the threshold bucket values setup in the predefined condition.
Note that the % is calculated according to the specific commodity shipment from the total shipments.


Only 2 columns, Name and Percentage respectively.


 Few Modifications:
  • Resouce Utilization and comparison from last year

  • Customer Complaints and their distribution

Read More »

Wednesday, March 11, 2015

OBIEE Best Practices (RPD->BMM)

1. Always run Global Consistency Check before releasing a repository:
  •  Whenever we make changes to a repository, always be sure to run Global Consistency Check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI server from loading the repository. Use the Consistency chec k manager to identify and debug check messages.
Note: Whenever you do Consistency check , Right Click the Changed Business Model Object and go for Check consistency rather than using the Global Consistency Check. This will minimize the time needed for Consistency Check.
2. Minimize the use of Snow-flakes. Always go for Star Schema’s:
  • Every logical fact table must join to atleast one logical dimension table.
  • Note that when the source is a fully de-normalized table or flat file, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables.
3. Create dimension hierarchies for every Dimension in the Business Model:
  •  Even if a meaningful hierarchy definition cannot be thought of, just create one with the Grand Total Level and Detail Level. For Dimension Hierarchies the ‘Number of Elements at this level’ should increase from 1 at grand Total to the corresponding distinct values at each level. This can be approximate values; need not be the exact ones. Define keys at each level of the Hierarchy.
4. Don’t keep unwanted physical columns in the Logical Layer:
5. Give Meaning ful Names to the Logical columns. Avoid assigning a logical column the same name as a logical table or Business Model Object:
6. Logical Fact & Dimension table columns:
  • Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users.
  • Bring only required columns into BMM layer for reporting.
  • Do not assign logical primary key for logical fact columns.
  • Create “dummy” measures to group facts.
7. Use Multi User Development Environment:
  • Use the Multi-User Development facility if there are multiple developers. Multiple developers to connect “online” to the same repository file and Making changes is not recommended.
  • Multi User Development allows user to define a series of projects with in the repository file, where each project is a subset of the entire repository. If developers want to make changes, they can check out a project to a local machine make and test the changes, and then check the modifications back into the master repository file.
8. Level-based Metrics:
  • When creating level-based measures, make sure that all appropriate fact sources map to the appropriate level in the hierarchy using aggregation content. You set up aggregation content in the levels tab of the Logical Column dialog for the measure. 
  • Note that this is different from the content tab of the Logical dialog for the measure. Note that this is different from the content tab of the logical table source dialog, which is used to specify the grain of the source tables to which it maps. You only need to set up aggregation content in the levels tab of the logical column dialog for level-based measures. For measures that are not level based, leave the logical level field bank.
9. Separate business model:
  • Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality.
10. Arrange the logical columns alphabetically if customer does not require any. This will save time when you revisit:
11. Fix the warnings if any, don’t ignore it:
12. Performance Tuning:
  •  Minimize the use of conditional checks and ‘CASE WHEN’ usage in the formula of Logical columns. This will affect performance. Instead make proper use of the where clause content filter of the LTS if the condition applies to all the columns/ measures in the logical table.
  • Make proper distinction between count and count distinct. If you are counting on a unique value column don’t use count distinct. This will affect performance.
  • Avoid dimensions in Fact tables and avoid measures in dimension tables.
Read More »

Friday, January 2, 2015

OBIEE Visualization - D3J Charts Part 3 (Advance)

Hey fellas, in this post, we will try to integrate D3 charts with obiee but this time the source will be the more realistic, we will try to graph EBS Vision BOM Data. The final tree will look something like this:

So, let's start by understanding the EBS table structure.Vision BOM contains three important tables namely:

So, using these tables we can able to find the Assembly Item and its components, thus can generate a hierarchy.
To model the BOM structure, we have created a view which represent the following columns that are deduced from these three tables.

ORDER_LEVEL: This represent the assembly_item order level(order 1 being the root)
ASSEMBLY_ITEM: Assembly Item name.
COMPONENT_ITEM: Immediate component name that follows its parent assembly item.
ASSEMBLY_DESCRIPTION: Assembly Item description
COMPONENT_ITEM_DESCRIPTION: Component Item Description.
PATH: Trace Path from root item to the current item.
ORGANIZATION_ID: Organization to which Item belongs.

Import this view into the rpd, create an alias, pull the view in BMM layer,create replica of it and make a logical join.Now, add this to presentation layer and host the rpd.

Now using the D3 charts described in part 1 and part 2 you can able to represent the data using narrative view. In this example, D3 chart is taken from here.

Comment below if you like the post.
Read More »