Oracle BI EE 10.1.3.4.1 – Do we need measures in a Fact Table?
May 31st, 2010 by Venkatakrishnan J
I know this blog title can be classified as a deviation from my more conventional ones but i have been planning on writing this for a while now, so hopefully this should be of some interest to everyone. As we start working with BI EE more and more, we get to know what its good features are and along with it we get exposed to its not so good ones(as with any tool) more. Recently i came across a requirement where BI EE was being implemented on a Data Warehouse that was built almost 4 years ago. The Data Warehouse had been built based on Kimball’s model and it had been satisfying the requirements of the customer so far(excel & MSAS reporting) unless and until BI EE came in as potential replacement for MSAS. When the customer first told me this, i was kind of confused on why BI EE failed to meet their requirement considering the fact that it was a very simple data warehouse(standard set of dimensions and 3 to 4 fact tables). On further analysis, there were 2 main requirements that stood out to be showstoppers in using BI EE
1. Ability to use any dimension member(Across dimensions) as a measure and the ability to use any measure as a dimension attribute – This is one key requirement that cannot be solved easily by BI EE currently. The problem with Business Model and Mapping layer of BI EE is the fact that we have to identify measures. Though this will work in most cases but if we want to do true multi-dimensional reporting, then the tool should ideally provide us with an ability to even treat measures as dimensions. For example, consider the report shown below
This is a very simple report where Amount Sold and Quantity Sold are measures (both in the data warehouse as well as in BI EE). But what if the customer requirement is to create a report as shown below
In the above case, Amount Sold and Quantity Sold measures in the first report have become dimension attributes. Also, the Channel dimension attributes Direct and Indirect have become measures in the second report.
In my customer scenario, end-users required the ability to treat any dimension as measure and vice versa. So, clearly they wanted a multi-dimensional reporting capability using their existing Data Warehouse and using BI EE. In retrospect, i would say this is a very common requirement for users who are used to Multi-dimensional tools like Essbase, MSAS etc. In some cases, with BI EE 10g, where we need to treat dimensions as measures, we just use Pivot Tables. But in cases, where measures in BI EE need to be shown as dimension attributes, then the only way to solve such requirements is to use set operations like using UNION etc.If we use them in Answers, we will start facing all sorts of issues like losing drills, losing the ability to use is prompted filters etc. There are other methods as well like the one that i have discussed before here. But the problem is the fact that we cannot make any method (BI EE modeling methods)generic enough to solve all these requirements.
2. The second requirement was to provide drilling ability on measures. For example, consider the report below. Here the 2 measures Amount Sold and Quantity Sold have been grouped under SALES. Similarly there are another 2 measures, Unit Price and Unit Cost which have been grouped under COSTS.
Drilling on COSTS or SALES should give us the individual measures.
Though the above example demonstrates a single step Measure hierarchy, the actual requirement was to have a multi-level measure hierarchy.
Ideally, considering the requirement, this would have been solved by using Essbase & Smart View. But considering the fact that the customer had already bought BI EE, a solution was to be arrived at using BI EE 10g. There were 2 things that we knew & were sure of
1. The data warehouse was working well. The customer was open to tweaking it a bit further to enable this requirement within BI EE.
2. Essbase provides this capability and will quite easily solve the customer’s requirement.
We even thought of probably asking the customer to wait for BI EE 11g, but again the actual problem was in the fact that measures were actually required by BI EE which i don’t think will go away in the near future since the fundamental design of BI EE RPD revolved around the need to identify measures and dimensions(we weren’t sure of what changes in the RPD were coming in 11g and hence we had to find a solution in 10g itself). This is where we had to devise a DW model using the concepts of Essbase and then retrofit it back to the DW as well as BI EE.
I will illustrate what we did for the customer considering the SH schema of Oracle which is based on Kimball’s DW methodology.
As you see, we have 2 fact tables(of differing grain) and multiple dimension tables linking to the facts. Each fact table has a set of pre-defined measures as shown below
From an end-user perspective, each of the measures above are nothing but attributes of data. So, in effect if you model this in Essbase, what essbase will do is, it will maintain an internal single measure called (<Data>) and then everything else are nothing but attributes of this single measure(i.e dimensions. How these are internally stored within Essbase is determined by the 2 storage options BSO and ASO). So, to model this similar to Essbase, we start with separating out measures into its own dimension table called Measure as shown below
Then we create 2 new Materialized Views, Sales_Data & Costs_Data, on top the Sales and Costs fact tables as shown below
So the actual relationship diagram between the fact and dimension tables will look as shown below
So far so good. What this enables us is a fact that we can now sort of treat all measures as dimension attributes. But how do we now provide the ability to treat dimension attributes as measures.
This is done by applying Filter function on the DATA column. For example, to create a measure called Direct Sales we create a logical column and then apply a function as shown below
FILTER("SH - Multiple Measure Dimensions"."Sales - Facts".Sales
USING
"SH - Multiple Measure Dimensions".Channels."Channel Class" = 'Direct')
This way, we can create as many logical measure columns as possible depending on what we need to treat as measures in the presentation layer. The good part about this is, we can use an attribute as a measure and also as a dimension.
The idea is we build a model containing the following presentation layer
For every dimension table, there will be a corresponding Fact Measure table with measures required for reporting.
So, to achieve the first requirement where we need to convert the measures as dimension attributes, we choose the following columns in the report
If we look at the SQL of this report, you would notice that FILTER function converts it into relevant case when statements thereby providing us with the ability to do multi-dimensional analysis
select T14286.PROMO_NAME as c1,
T14409.MEASURE as c2,
sum(case when T14224.CHANNEL_CLASS = 'Direct' then T14394.DATA end ) as c3,
sum(case when T14224.CHANNEL_CLASS = 'Indirect' then T14394.DATA end ) as c4,
T14286.PROMO_ID as c5,
T14409.MEASURE_ID as c6
from
MEASURES T14409,
PROMOTIONS T14286,
CHANNELS T14224,
SALES_DATA T14394
where ( T14224.CHANNEL_ID = T14394.CHANNEL_ID and T14286.PROMO_ID = T14394.PROMO_ID
and T14394.MEASURE_ID = T14409.MEASURE_ID and (T14224.CHANNEL_CLASS in ('Direct', 'Indirect'))
and (T14409.MEASURE in ('Amount Sold', 'Quantity Sold', 'Unit Cost', 'Unit Price')) )
group by T14286.PROMO_ID, T14286.PROMO_NAME, T14409.MEASURE, T14409.MEASURE_ID
order by c1, c2
Similarly to solve the second requirement, we just need to pull the relevant measure attributes from the measure dimension and then use the DATA field for our measure
There are significant advantages of this method as this does not fix us to any specific dimension/measure. It provides an ability where we can swap measures and dimensions at will.
So the question to everyone reading this is, do we need measures at all in the Fact table(apart from the implicit Data Fact column)? Majority of the tools like BOBJ, Cognos etc all require measures to be assigned explicitly. How about forking out the measures into their own dimension and then treat everything else as normal? Are there any downsides to this approach? The features offered seem to outweigh the conventional modeling method. But i am aware that there could be potential problems with this as well. One problem which i could see is the performance. The size of the fact tables can grow quite quickly if there are a lot of measures. Also, the end-users might have to get used to this model. Is there anything else that you see could become potential problems in the future? If so, do put your thoughts in the comments section.
June 1st, 2010 at 9:10 pm
Hi Venkat, I’m impressed by this piece of OBIEE magic. But I do see two problems: when I look at the second report, I see no currency symbol in front of Amount Sold. There is a reason for this: formatting is done over the whole column.
So OBIEE thinks in columns and then see another problem: what about the aggregation rule? When I look at the fourth report, both measures use Sum as the aggregation rule. But what if I want Amount Sold and Average Order Size as measures? Then I need both aggregation rules (Sum and Avg) in the same column, which is not possible, as far as I know.
Keep posting this kind of stuff, very interesting!
Cheers,
Stijn
June 2nd, 2010 at 10:04 am
This looks like making OBIEE work like a Hyperion essbase cube where everything has a hierarchy
June 2nd, 2010 at 5:47 pm
Hi Venkat,
nice post and nice idea!
I have thought of something similar (though not tried yet), because in financial reporting an account dimension would come in very handy.
My problems are the same as Stijn’s. On top of that I see the general limitation that you cannot use calculated measures in the general model.
In my case I would need an account (i.e. measures) dimension and a kind of type dimension with types like MAGO, YTD, LYTD, variance etc.
If I understand your concept right, these measures could no longer be created in BI Server. Rather, they must be available in the DWH.
/Michael
June 2nd, 2010 at 5:57 pm
@Stijn/Michael- Appreciate your comments. For the measures that require custom aggregation like SUM, AVG etc, we will create them using FILTER expressions as shown above. So, in effect we dont lose any of the normal calculations that we do with BI EE. Same is the case with MAGO, YTD etc. The above model basically provides an ability to treat any member within any dimension as a measure as well as a dimension attribute. Do you still feel this could be an issue? I can upload the final RPD if you are interested to see.
@Sangram – Yes. I have mentioned that above. The idea is to retrofit the Essbase Model directly into the data warehouse.
June 4th, 2010 at 2:31 am
For the first scenario, I always create a logical dimension table and a logical fact table based on same physical fact table. Then use complex join to join the dimension table to fact table in the logical layer. Then OBIEE will just use one physical table in the query and group by the dimensions. I do that all the time since sometimes there is no corresponding dimension table and the client wants to simply see the value in fact table as a dimension.
June 4th, 2010 at 2:52 am
As to the second scenario, I would just create two separate facts and join them to their dimension tables. Name one fact as SALES and the other on in cost table as COSTS. Then in answers report, add dimensions and two fact values there. Then in pivot table, drag the measure labels to the row. Then create detailed report for COSTS and SALES separately with same dimensions prompted for values. Then in the first report, add corresponding report as the target for navigation on the two fact columns. So the client will be able to click on the measures and drill down to either cost report or sales report.
June 4th, 2010 at 7:50 am
@Jessie – The above is not a solution for a particular problem. This is a generic solution. For example, in the above examples, what if i want to treat Product dimension attributes as measures? How will you achieve that without making a change to the repository? Also, you are talking about creating multiple reports for drilling on measures. How many reports will we need to create if we most of our reports need the measures drill out of the box?
But yes, as i have mentioned above in a different blog entry, there are particular use cases that we can solve by modeling the RPD accordingly. The issues will come up when we try to generalize the particular case which is what the above model resolves.
June 4th, 2010 at 11:54 am
Hi Venkat,
We are already using this type of modeling (which we also immediately called “Essbase-style modeling”) for about 6 months period on one project. We are not being original, we’ve seen it first in Oracle BI Applications, immediately recognized the value of the concept and decided to implement it on our project. I must say it is rather strange how nobody pushed this issue before, because it is a revolution concept indeed.
On this particular project we have a small number of base (native) measures and a large number of derived measures with a tendency to grow. Derived (business) measures are typically calculated from a certain combination of several dimension members and from some descriptor column expressions. Customers are used to see these derived measures as individual BI items. Calculating these measures on-the-fly is unacceptable for performance reasons, and it would also require forcing joins between fact table and some dimensions. That is why derived measures are typically calculated in ETL.
In a traditional modeling, adding a new measure would require altering a fact table. In Essbase-style modeling we add one new row in a “measure dimension table” and many referenced rows in a fact table. Derived measures rows exist only for a subset of original transaction rows, depending on their definition. The model itself is a constant.
Our experience? No drawback so far. The only new thing customers will have to get used to is filtering a “dummy” result set in an ad-hoc report. If there is no filter condition on a measure dimension members, sometimes a report can display rows with null (or zero) values, and a user doesn’t expect them. A “sum(case when…)” aggregation does not filter rows, so if some other measure rows (not required by a report) exist, and required measures rows do not for a defined conditions, a dummy result set will be displayed. Adding a filter on a measure dimension members will remove a dummy result set and also boost query performance.
Some thoughts about the size of the fact table. This question is highly dependable on the typical type of reports – high or low aggregation? In our case, mostly highly aggregated reports are required. The number of transactions itself is huge and aggregated segments are a must. Almost every query is rewritten, MV query rewrite on “sum(case when…)” queries works like a dream. A separate set of AGO MVs also exist (because of poorly implemented built-in OBI AGO function which generates non-star query, confuses Oracle optimizer and disables partition pruning and query rewrites), so every trend analysis on every measure is extremely fast. Besides, all fact table rows belonging to one original transaction are inserted sequentially, with only a few different column values, so a block-level direct path table compression is very efficient.
Anyway, if there is a concern about fact table growth, a hybrid concept can be used. Base measures in one row, derived measures in extra rows. In our case, the derived measures definition is such that the most of the measures exclude each other and not many extra rows for an original transaction are generated.
Best regards,
Marino
Zagreb, Croatia
June 4th, 2010 at 1:59 pm
Venkat, sorry for not reading through and understanding your solutions. Please delete my posts. In one of the project I worked on, the client wanted us to replicate how SAP income statement looks in the exact format and order. But the table has flattened structure, meaning they used value fields for each line items. We had to convert the structure into longitudinal structure with line item as the dimension. But that caused another problem, the data volume increased a lot. Do you have better solution for this one? Thanks in advance for your help!
June 8th, 2010 at 1:43 am
Yes this model works quite well for a number of scenarios. I have used this quite successfully in a KPI model where certain employees are only involved in generating some of the measures. This model will automatically allow the reports to only show measures for the processes where they are involved.
June 17th, 2010 at 9:53 am
Hi Venkat, great idea!
We have considered this way of modelling fact tables in a project once but our motivations were different. In this project we did not know a priori which measures we had to store, all we knew was that there was fixed set of dimensions that could be related to most of the measures. That’s why we considered a generic approach like the one you describe. So, like Marino mentions, extensibility of the physical dwh model is an important pro for this approach, independent of OBIEE. All the front-end (OBIEE) related advantages you mention did not cross my mind before… so thanks for that :)
I agree with you that query performance is the most obvious problem, simply because this approach trades ‘column-space’ for ‘row-space’. Partitioning on MEASURE_ID will help I guess.
Regards,
Elio
June 19th, 2010 at 8:20 am
hi,i learnt essbase,can any one help because,what is retrining buffer size,and cube size,
June 19th, 2010 at 7:03 pm
This is a common requirement to treat facts as a dimensions and can be easily modeled by using Measure dimension. I don’t understand why this blogger is confusing people by putting artificial examples and limitations in the tools when he does not understand something
June 20th, 2010 at 2:42 am
@Rajeev – I am not sure whether you did go through the full article. Measure dimensions are pretty common for Essbase or any other OLAP tool which i have specified above. The point which i was emphasizing was for BI EE (which is a tool) that requires separate measures. Modeling Measure dimensions is not pretty straight forward within BI EE without tweaking the warehouse model. I guess i was pretty clear on the requirements and the examples as well. Also, i am not sure which part of the article is confusing. If you can be more specific, i will try to address them. But thanks for the comments.
-Venkat
June 27th, 2010 at 7:24 pm
Excellent Article!!!
August 19th, 2010 at 11:33 am
Great article. I appreciate it venkat