cancel
Showing results for 
Search instead for 
Did you mean: 

updating data from ODS to InfoCube

Former Member
0 Kudos

Hello experts,

I am not sure if my approach works so let me elaborate:

ODS1 contains trips on participant level.

The data in ODS1 looks like this:

ODS1:

trip_id___participant_id___price_____trip date

__101_____1___________25.00_____20070112

__101_____27__________12.50_____20070112

__102_____21__________55.00_____20070124

__102_____7___________55.00_____20070124

__102_____15__________35.00_____20070124

Now I want to update the data to InfoCube IC_TP which looks like this:

IC_TP:

dimensions:

- time

- participant_id (has nav. attribute participant_type on which I want to report or do I have to select participant_type instead of participant_id as dimension???)

measures:

- price

- number of trips (according to the data in ODS1 it should be "2" for jannuary)

- number of participants (according to the data in ODS1 it should be "5" for jannuary)

is this updating approach possible?

thanx

hiza

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

The aproach is posible.

map date and participant_id directly in update rules, key figures as sum and number of participans always 1.

If participant_type is navigational attribute you can do reporting based on it.

Answers (7)

Answers (7)

stevenm_wilson
Active Participant
0 Kudos

If you use my solution, you should get everything you need. You will need to add the navigational attribute of part_type to the ODS. But you need to make sure that does not change independently of the transactional data. You will also have issues when counting trips, regardless of part_type.

As for the query solution, you can use query 1, to obtain all trip_id's or participants associated to a part_type, then, it will input those trips/parts, into query2 selection to get the number of participants/trips. But, you will run into issues with the part_type because this solution will only identify the number of participants in total, not by part_type. Make sense? I think the query solution with exception aggregation should work. If it doesn't, you may need to create two infocubes, to suffice each solution (number of trips/participants).

Cheers!

/smw

stevenm_wilson
Active Participant
0 Kudos

That is fine, just create update rules from the ODS (new) to an infocube.

/smw

Former Member
0 Kudos

Hello Steve,

I keep your suggestion in mind, but I would like to have a direkt update from ODS1 to Cube!

hiza

former_member188975
Active Contributor
0 Kudos

Hi Hiza,

You cannot avoid the update rules for updating ODS data to a cube. You will need to create the update rules and then only you can load the data.\

Hope this helps...

Former Member
0 Kudos

Hi Bhanu,

you are right.

Of course I need update rules! With "direkt" I mean update from my ODS1 direktly to my Cube without using another ODS!

Hiza

former_member188975
Active Contributor
0 Kudos

Okay...may be misread something in the thread

stevenm_wilson
Active Participant
0 Kudos

Sorry, ie issues.

Message was edited by:

Steve Wilson

stevenm_wilson
Active Participant
0 Kudos

Sorry, IE issues....

Message was edited by:

Steve Wilson

stevenm_wilson
Active Participant
0 Kudos

Create a variable in which you use a query against your first ODS to get the trip_ids and or dates via participant type, then use that variable to select the data from the second ODS. This can all be done at the query level of the second ODS.

/smw

Message was edited by:

Steve Wilson

Former Member
0 Kudos

Hello Steve,

I want to use the Cube for reporting not the ODS!

hiza

Former Member
0 Kudos

Hello Steve,

would you please elaborate your suggestion! The first part with the new ODS for Trips I understand, but what do you mean by variable?

hiza

Former Member
0 Kudos

Hi Hiza,

this is possible, but you need to add trip_id in your cube if you want to report on it...

Former Member
0 Kudos

@Oscar: what do yo mean by "number of participans always 1"? would be nice if you could give me some code example in abap!

@Steve: that was my first approach. Problem is that participants are turned into a measure so I cant report anymore one participant_type!

@Roberto: i don't need to report on trip_id since I have other characteristics like trip_destination etc. in each line of ODS1. I just leaft them out to make the example more simple.

hiza

Former Member
0 Kudos

in update rules set 1 as fix value in order to count number of participians

Former Member
0 Kudos

thanx, I'll try it out!

hiza

Former Member
0 Kudos

Hi Oscar,

how can I set 1 as fix value in update rules between ODS1 and Cube in order to count number of participians?

in update rules I have key figures-section, charchteristics-section and time-section:

- For participant_id i choose in charchteristics-section direkt update

- for date I choose also direkt update in time-section

- in key figures-section I choose sum for price and for number of participans I set abab-code in routine "RESULT =1."

But loading data into cube didn'work!!

I can only choose between summation and no updating key figures-section! There is no update mode for setting a constant like in update rules for ODS-Objects!

And how do I count number of trips?

hiza

Former Member
0 Kudos

if you set the key figure as sumation and fix value 1, for each record loaded in cube that field will have value 1 and it will be summed in cube.

what exactly problem do you have?

Former Member
0 Kudos

Hello,

the updating worked fine so far, but the number of trips is the same as the number of participants! How can I get the correct number of trips?

How can I solve this in a abap routine? There must be a way to count only differnent trip_ids.

thanx

hiza

Former Member
0 Kudos

Hi,

Try this:

For the KF Info object : number of trips,

Take Exception Aggregat = Last Value

Agg.referen.char = Trip_id

And

For the KF Info object : number of participants,

Take Exception Aggregat = Last Value

Agg.referen.char = participant_id

With rgds,

Anil Kumar Sharma .P

Message was edited by:

Anil Kumar Sharma

Former Member
0 Kudos

Hi Anil,

Aggregats are new to me. Where to take Exception Aggregates?

hiza

Former Member
0 Kudos

Hi,

You have take these prperties in the edit screen of the info objects. In the <i>Change</i> screeen , go to <i>Aggregation </i>Tab page. Here you can find these settings.

And try like this .

Hi,

Try this:

For the KF Info object : number of trips,

Take Exception Aggregat = <b><i>Counter(Values unequal to zeros)</i></b>

Agg.referen.char = Trip_id

And

For the KF Info object : number of participants,

Take Exception Aggregat = <b><i>Counter(Values unequal to zeros)</i></b>

Agg.referen.char = participant_id

With rgds,

Anil Kumar Sharma .P

Message was edited by:

Anil Kumar Sharma

With rgds,

Anil Kumar Sharma .P<b></b>

Message was edited by:

Anil Kumar Sharma

Former Member
0 Kudos

sorry Anil, it did't work!

values are never zero in the ODS if you have a look at my example!

I'm new to ABAP, but there must be a way in abab to compare the trip_id and count only if it changes.

hiza

Former Member
0 Kudos

if i understand...

Number of partipans is correct in cube it has to be greater than number of trips rigth?.

Surely it should be solved in BEX through aggregation exception.

stevenm_wilson
Active Participant
0 Kudos

My suggestion would be to create another layer of ODS objects.

1 new ODS objects.

(A)One with Trip ID, trip date and price, along with another KF, called Participant Counter. Make the counter and price "additive" in the ODS. For each record, increment the counter. (This gives you number of participants per trip) When you do a number of records, per date, you will get the number of trips. Both KF's would be available for this report.

Detail would need to remain in your current ODS for who the participants were...

trip_id___PartCounter___price_____trip date

__101_____2___________37.50_____20070112

__102_____3__________145.00_____20070124

you could do the same type scenerio with infocubes.

Hope this helps!

/smw