Aggregate Fact Tables ? 1

[OLAPer]

Hi, this is my first ever thread.....<br><br>I'm probably going about this in the wrong way, your assistance will be greatly appreciated, let's see if I can explain :<br><br>I am currently in the process of developing a Performance Monitoring/reporting ROLAP solution.&nbsp;&nbsp;I have a number of facts that are constant over time, an example is Tolerance Level, Available Score and Debits per Failure.<br><br>These facts are constant and don't change, however the reports need to be shown by time.&nbsp;&nbsp;I was thinking that I would be required to add the time dimension to the following table :<br><br>QuestionID ; ToleranceLevel; AvailableScore; DebitsPerFailure.<br><br>Adding the Time dimension would I think be sufficient to have the figures represented by time, but there is another spanner.<br><br>The figures are &quot;inventory&quot; type, what I mean is that the aggregate of the weeks elements is not the sum, it is the same figure.&nbsp;&nbsp;So what I have decided to do is create a set of aggregate tables.&nbsp;&nbsp;One for week, month and year.&nbsp;&nbsp;I realise that a constraint enforced solution might be better, but the advantages I can see only refer to the ETL processes, I only need to create these tables once and once only as the facts will not, can not change.<br><br>My question is (eventually) is this the best/only way of handling this situation.&nbsp;&nbsp;The ROLAP product I am using is Media 4 (Speedware).&nbsp;&nbsp;What are the alternatives to this, as the problem is that as I move along the time dimension I am having to move from cube to cube.<br><br>Please feel free to e-mail me at :<br><br><A HREF="mailto:imran.hussain@sodexho.co.uk">imran.hussain@sodexho.co.uk</A><br><br>Any assistance would be greatly appreciated.

 

[jnicho02]

Do you have other facts (measures) that are addable over time? The facts you mention are non-additive. In the product that you are using is there any way to flag them as such... it may refer to them as 'attributes'.<br><br>It sounds a bit painful to have seperate cubes each time. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br>

 

[OLAPer]

Yeah<br><br>Painful, YES !<br><br>I have facts that are addable....these are the &quot;actuals&quot;.&nbsp;&nbsp;I don't believe that the product has anything that allows the developer to flag these as such.<br><br>What I need to be able to do is take the actuals and perform calculations to identify the penalties that are incurred due to failures in performance, taking into account the tolerance level. Example : Tolerance Level of 3, failures 4, debits per failure 25, hence debits ( 4 - 3 )* 25.<br><br>Thanks

 

[OLAPer]

Still no help.....<br><br>Can anyone advise on how best to approach this problem ?<br><br>Please !<br><br>thanks

 

[WHArchitect]

The data elements you mention, QuestionID, ToleranceLevel, AvailableScore, DebitsPerFailure, sound more like dimensions than facts. Are you trying to count something, like failed accounts for instance, and report those counts by these dimensions? If so, each instance of a QuestionID, for instance, becomes a row in the &quot;Question Dimension&quot;. You might arrange such items as DebitsPerFailure into significant value ranges, each of which becomes a row in the &quot;DebitsPerFailure Dimension&quot;. And so on. The actual count might be derived, at the most granular from a &quot;factless&quot; fact table. In this example, such a table would consist of a row for each account that went into default, or each account currently in collection, or some such thing. In this example, instead of adding amounts, you are counting rows in the fact table. This type of data is certainly time variant. You can set the granularity of the time dimension at any level that is significant. From here you can establish aggregations as necessary to accomplish performance goals without sacrificing the detail.

 

[OLAPer]

Thanks for the reply, however<br><br>An example of what I am trying to do is that a survey is undertaken, and let's say in that survey there are 3 questions.&nbsp;&nbsp;The survey relates to, the clenliness of a hotel room.&nbsp;&nbsp;The Hotel Manager has agreed that there are a number of times that a failure is acceptable (Tolerance Level) this remains the same throughout time, let's say 2.&nbsp;&nbsp;There is an Available score for each question, e.g. 100.&nbsp;&nbsp;However, for each failure above the Tolerance Level there is a deduction of 20 points.<br><br>The survey is carried out each day.&nbsp;&nbsp;At week level the tolerance level is still only 2 in this example, as it is at month and year, or am I missing something here ?&nbsp;&nbsp;I miss the point of these being Dimensions, please explain in more detail ?<br><br>With the above example I have had to create a Fact table for week, month and year containing the aggregate data for each of the questions.....is there a better way of doing this, sorry if it sounds stupid !!!!

 

[WHArchitect]

In your example, are you counting inspections per room? Inspections per hotel? Let's assume the latter. Let's say that your fact table contains one row per hotel inspected per day. Say a given row points to the hotel (&quot;Hotel Dimension&quot to which it belongs and the day (&quot;Time Dimension&quot on which the inspection occurred. <br><br>You mentioned the number of times a failure occurred. The number of failures in a week? In a Month? For all time? Let us say that it is the number of failures per week that is expressed by the failure level. That would make a failure level of 1 equal to 1 failure in a calendar week, 2 equal to two failures in a week, and so forth. If there are 7 inspections in a week, there are eight failure levels. Levels 0 and 1 are in the &quot;acceptable&quot; range. Levels 2 - 7 are in the &quot;unacceptable&quot; range.<br><br>Now we revise our star schema to include a failure level dimension. In this table there are eight rows, one for each failure level. Let's make this a hierarchy. Let's say that we have a &quot;failure level range dimension&quot; this table has two rows; one for &quot;acceptable&quot; and one for &quot;unacceptable&quot;. The rows representing 0 and 1 in the &quot;failure level dimension&quot; point to the &quot;acceptable&quot; row in the &quot;failure level range dimension&quot;. the rows for levels 2 through 7 in the &quot;failure level dimension&quot; point to the &quot;unacceptable&quot; row in the &quot;failure level range dimension&quot;. <br><br>Now we come to the fact table. remember that we have a row for each hotel for each daily inspection. We might want to revisit that. Either we keep this granularity and have each row point ti the failure level for that hotel as of that day within that week, or maybe I might want to change the granularity, having one row per week per hotel, each pointing to that hotel's failure level at the end of the week. <br><br>Now I would still be able to get a count of how many hotels I have at each failure level in each week and I can report on the total for month end by taking the total for the last week in the month, the year by taking the totals for the last week in the year. Also, I can roll my totals for any given period up to &quot;acceptable&quot; or &quot;unacceptable&quot;.<br><br>Do you get the idea?

 

[rhoesche]

You need to find out if your tool has &quot;time series&quot; functionality. Both Essbase and OLAP Services have this functionality. What these functions allow you to do is to create a first, last or average or sum value on the time dimension. Using last would result in the following. Jan 10, Feb 15, Mar 7 Qtr1 = 7. Using first for the same data would result in Qtr1 = 10. Most OLAP tools should support6 this.<br>The First, last Avg tag is usually associated with individual measures so you can have different time series for each measure. <br>You need to define you requirements then pick a tool that does what you want.

 

[OLAPer]

I resolved the problem !!!! <br><br>It was probably my description of the issues I was having, that caused all my problems....<br><br>The way I resloved the issues was to create a separate aggregation table that held precalculated data with regards to tolerance level and weightings.<br><br>When I report these to the user, the tool moves it focus from the core detail fact table to the Aggregation table, and conducts further calculations on this data.<br><br>It works !!!!!<br><br>Thanks for all your help, it took several sleepless nights !<br><br>Regards !<br><br>