Design Advice

[former_member209895]

Hi Experts,

      I'm wondering about a design requirement here, any suggestions and all suggestions are helpful. I apologize for the length of the question, as I couldn't find a better way to make it any shorter.

I was handed over an excel sheet with a lot of formulae and asked to create a BW report. I've successfully got all the required data into BW infocube and now to implement record based excel formula I'm struggling.

For example, one such key figure which I need to achieve is ORDER COUNT :

Maintenance Item         Call Number    Order Count          

090                                             10                     0                      

100                                             20                     1

100                                             30                     1

100                                             30                     0

200                                             40                     1

Here Order count is being calculated using the below logic in excel:

If maintenance item of the present row  = maintenance item of the previous row.

     If call number of the present row = call number of the previous row.

           Order Count = 0.

     Else.

           Order Count = 1.

     Endif.

Else

     order count = 1

Endif.

Based on the above results I need to create another report where I've to summarize the order count for every workcenter across every month, which looks something like below

                              01.2013               02.2013               03.2013               04.2013     ........

Work Center       Order count        Order count          Order Count       Order Count

    WC1                       25                          36                          12                       10

    WC2                        33                          42                         55                         16.....

So if I set up a document count on Maintenance item, I get only the unique maintenance item count in the report output but will miss on the counts of Call number being different within the maintenance item.

I've created a workbook to calculate the order count, using excel formula, but a workbook cannot be a data provider to the report where I need the display as total number of order counts.

I've thought of implementing a self load on the target infocube and implementing the logic of order count and other keyfigures in transformation and loading the target everyday, but the data is huge and everyday full load to run through will have a big performance impact. And full load is a must to compare each maintenance item to the previously loaded one.

I'm not sure if I can make use of APDs here, if yes then kindly guide me on the best approach.

In short: I need formula to be applied on the result set and then this result set be an input to the next query.

Can this be achieved in the BEx level only, and then use that report result in turn as an input to the next report.

I understand the way SCN works so will be generous in rewarding points for the helpful answers .

Regards,

  Manohar. D

[Former Member · Accepted solution]

Hello,

If suppose, we have a query with maintenance item in rows. Now if you put a count (exception aggregation) on unique call numbers, you will get total order count across every maintenance item. Let us consider this as KF1.

Now abstract it one more level and you get order count at Work center level. Remove maintenance item from rows, put work center in rows. Introduce a formula (let's call KF2). In this formula, put KF1 in and in exception aggregation, say total and in reference character put maintenance item. You get the Order count at Work center level. 😉

Regards,
Arunan C

[former_member209895 · Accepted solution]

Hi Arunan,

       You make it sound easy . Will try this first and get back. Thanks for the valuable suggestion.

Regards,

  Manohar. D

[former_member209895 · Accepted solution]

Hi Arunan and Yasmeen,

        Thank you, the exception aggregation worked like a charm. Now I'll have to explore if it'll do the same when used in my report with workcenter. Appreciate the help.

Regards,

  Manohar. D

[former_member182516 · Accepted solution]

Thanks Aruna,Yasemin and Umashakar --- Good discussion ...

Got to know more on exception aggregation functionality.

[former_member209895]

Hi Yasmeen and Arunan,

      Thanks for your suggestions, could you please suggest on how to implement the same. I tried going through some documents related to exception aggregation, and they all were based on an existing key figure, whereas in my case I do not have a key figure ORDER COUNT in my infocube, I just want to calculate it as a new formula and I tried using the COUNT function. How should I create the new formula in the first place and In exception aggregation what should be my choice - Exception if more than one value ?

Regards,

  Manohar. D

[yasemin_kilinc]

Hi Manohar,

Simply create a formula and as a formul put 1 in it. In the aggregation tab select exception aggregation as count and reference characteristic as callnumber.

Regards

Yasemin..

[umashankar_poojar]

Thanks Yasemin, Arunan for letting me know the other way of solving.

Actually we used extensive cell level logic due to complex calculation at designer, so first thought on cell level calculation.

Cheers!

Umashankar

[yasemin_kilinc]

Hi Manohar,

if call number is a unique number then you can define exception aggregation on call number. If not, then you can define maintenance item as compounding for call number and again count call numbers by exception aggregation. In the same query you can add workcenters, when you navigate it, it would calculate the numbers...

Hope it gives an idea.

Yasemin...

[umashankar_poojar]

Hi Manohar,

      It's very interesting thread and problem, as you already thought of data modeling level since it is not feasible.

• If reports are having fixed rows like (Maintenance Item 100 to 200 & WorkCenters WC1 to WC10). Just a thought, you can go for cell level definition (using if else logic at each cell level). But very tedious job and limitation of drilling down options on the report.

hope you already thought of this!

Thanks,

Umashankar

[former_member209895]

Thanks for the excellent advice Umashankar. I had thought of cell definition to achieve the row wise calculation. There are around 5 key figures with such record wise calculations and moreover I'm very weak when it comes to cell definition, which was why I was avoiding it. Nevertheless I'll search for some docs, one of you good contributors would've put up something on cell definition, and try it.

But lets assume I achieve it through cell definition and live with the limitations on drilldown, can I then use this report result as data provider for the next one where I just need the total order count against workcenters ?

Regards,

  Manohar. D

[umashankar_poojar]

But again using a cell definition, I assume group of Maintenance Items are called as Work Center. Using this you can add up Order count but

• Getting month based data will be difficult.
• Both calculations i.e first and second report should be in same query.

Note: There is a limitation of cells in a query, check that also.

Conclusion from me is with all these limitations, it can be achieved.

Thanks,

Umashankar

[former_member209895]

Hi Umashankar,

     Will keep all the suggested points in mind and get back to you after I've carried out a sample development.

I was just thinking about another approach. What if I create a new infocube, which gets loaded parallel to the existing infocube, but this new infocube holds only Maintenance Items, Call Numbers and Period (Mostly planned date as to when and where the maintenance of these maintenance items takes place) a Characteristics (under individual dimensions, so line items) and in the key figure I keep only ORDER COUNT. Now this will act like an aggregate, holding only a part of the data, less data. What if I now implement a self load with logic to count the orders. Then finally create a multiprovider involving this cube with the original one with just planned date as common.. Do you think this will work or will the ORDER COUNT be wrong due to unavailability of all the other characteristics?

Regards,

  Manohar. D

[umashankar_poojar]

yes, you can do that. But everytime you will have to go for full load and re-iterate your logic for complete set of data, correct me if I'm wrong.

Also as you pointed out, due to unavailability of other characteristics there might be unseen issues.

Just try to implement report cell level for small set of data, then go for modeling. Later you can compare pros and cons then put across to user.

Thanks,

Umashankar