Duplication of Amounts in report based on One-many joins

[Calator]

I need to write of report showing Fixed Asset Capital Cost and Depreciation over period from-to. In a simplified form, the views and universe design are as following:

Two tables(views):
1. Fixed Asset
Asset Number
Asset Description
Acquisition Date
Retire Date
Capital Cost ($)

2. Fixed Asset Depreciation
Asset Number
Period Number
Period Depreciation ($)

the tables join on Asset Number. There is a 1 to N cardinality from table 1 to table 2, as well as outer join because not all Fixed Assets will have depreciation recorded.

As assets come and go, when the report is run for period from/to, not all assets have depreciation records for each period in range.

Report prompts are Period Number From & Period Number To

The report needs to display Grand Totals for:
Total Capital Costs from all assets that were on books in period from/to
Total depreciation period from/to

Simplified Report Format:
Reporting period From/To
Asset Number | Description | Capital Cost | Depreciation
nnnnnnnnnn | xxxxxxx | 99999999 | 99999999
========================================================
Grand Totals: Count: 99999 | 99999999 | 99999999

Universe Objects for Capital Cost and Period Depreciation are defined as Measures.

I have defined a second object in the Universe for Capital Cost as a Dimension, [Capital Cost dim], which allows to report correct Capital Cost value against the fixed asset item in the detail row of the deport.

In the report Grand Total I need to display sum([Capital Cost dim]). The problem I have is that this Grand Total includes duplication of amounts for Capital Cost, when reported for Perid From <> Period To, and where more than one period depreciation is recorded for the asset.

I realise the reason for the problem is in the joining of the two tables as a 1-N and so the Capital Cost is duplicated. I was not able to find any work around. In other reporting tools I could solve the problem in the report design, by naming the break variable used to report Capital Cost against the asset, and then summing up from that variable to obtain the Grand Total. In BO & Webi this does not seem to be available, the variable definitions for sum([object]) are global and appear to work the same on all breaking levels up to grand total.

Your help is much appreciated.

 

[hilfy]

I would create a filter in the universe that does something like the following:

(Depreciation.period is null or 
Depreciation.period = (
  select max(getPeriod.period)
  from Depreciation getPeriod
  where getPeriod.AssetNumber = Depreciation.AssetNumber)

This will get you only the most recent depreciation value for any give asset. Then you can use the filter in your report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Calator]

Thanks, but I don't understand how to use the idea. The report needs to display the sum of all depreciation for all assets in periods from /to, as well as the sum of the capital cost for all assets (but only considered once).

 

[hilfy]

The only way I know of that you can do this with a universe is to create a filter in the universe like I described above. It sounds like you'll then need to have two merged queries - one that uses the filter to sum the capital cost and the other without this filter to pull all of the depreciation data. Merge on Asset Number.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Calator]

Our version of Webi for BO 6.5 can use several queries in a report, however the queries are NOT synchronised ie they can only be used as separate blocks in report. I think we cannot have one grand-total line showing objects form 2 queries, like:
[asset number] sum([capital cost]) sum ([period depreciation])
where [asset number] and sum([capital cost]) are obtained from query 1, while sum ([period depreciation]) is supplied by query 2!

Waht do you mean by 'merged queries'? A search on Webi doco did not return any matches. Is this a feature available in a later version of BO?

 

[hilfy]

I was assuming that you're on one of the BO XI versions where WebI will merge queries on a dimension.

I'm not sure how you would do this without that feature.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[blom0344]

AFAIK this was called 'synchronizing dataproviders' over 1 or more common dimensions. Worked in full client BO from the start, but probably not in WEBI 6.5.
However 6.5 designer supports view expressions (whole SQL logic; derived tables) additional to imported tables / views from the database.
Can't you build it this way?

Ties Blom

 

[Calator]

Re: blom0344 "view expressions (whole SQL logic; derived tables)" - Could you elaborate on that? I don't seem to think this can be solved in Universe Designer, while we don't know the range of periods that will be required, so cannot supply a total of all Depreciation and then join to Asset.
The report will receive form the Universe each row of period depreciation and the adding up happens in the report.

 

[blom0344]

It is hard to say without having example data, but the main problem is with having a measure in the dimension side of the relationship ( 1 : n ) This will cause 'overcounting' or better 'oversummarizing' cause the join is performed prior to aggregation. So ,for the measure on the dimension side the aggregate should not be sum but either avg or max in order to bring back Capital Cost just once for each Asset Number (regardless for the number of Depriciation records for each Asset Number)

Ties Blom

 

[Calator]

re: Blom
You correctly describe my problem. I would think this is a very generic problem that has an easy solution.
I tried 'max', wether at report level or at Universe level, and none appeared to work for me. In particular, none returned a correct amount in the reports's grand totals.
what appears to work is the following variable expression in the report, referring to Capital Cost defined as "dim" in the Universe. I saw it in another report, but don't understand the concept behind it. Maybe one of you gurus will care to explain how it works?

=Sum([var dim Cap Cost] In ([Asset Number]))

 

[blom0344]

It is a frequently occuring problem when dimension tables are storing facts as well. Fixed Asset Depreciation is acting as the fact against the Asset Number dimension.

If you roll up all fact data to the Asset Number and then join to the dimension you should not have this problem. However this will not work if you need to dynamically select a range from Fixed Asset Depreciation.

With 'old' data synchronization this was indeed possible. Two seperate queries where then synchronized locally on the client. (Still miss this as the most powerful feature ever)

Can't really explain why your solution works though. It's too long ago

Ties Blom