Creating cube from three ODS!

[Former Member]

Hi Experts -

I have the following:

1. Billing item ODS

2. Billing condition ODS

3. CRM_CODT cube

I have created a cube from the Billing Item ODS. I basically need Condition Type char from Billing condition and Executing Service Employee char from CRM_CODT cube.

My first attempt is to add the Condition Type field in my Cube. I have added that. And in my update rules I have it mapped to the source ODS.

I'm a little confused with the mapping though. I need to have the keys mapped to each other, right? If so, I need to bring in the keys from ODS to the cube, right? Can I do that?

After that, how do I load the right data?

And after that I have to account for the Executing Service Employee from the CRM_CODT cube.

I need help with that mapping and also how to load the data? Do I need to write a update or a start routine?

I know I'm asking for a lot but thanks a lot in advance

Helpful answers will definitely get point!!!

[Former Member · Accepted solution]

Hello Syed,

Why don't you create a multiprovider all for this?

Sarhan.

[Former Member · Accepted solution]

I thought about it. My lead asked me to do this and I have been thinking about the MultiProvider cos it seems a little involved for me who doesnt have much modeling experience.

Any other ideas?

[Former Member · Accepted solution]

Beware of the multi provider which reflects an UNION of the database. So you can not filter any query with an information stored in a single provider of the multi.

If you want to join your data together before lyou oad the cube, you could use the APD Analysis Process Designer. Have a look to that, great tool!

Regards

[Former Member]

Hi Syed!!!

If you use MP, it would be easier to implement since you need only one char from each of the other cube. The design you are trying to implement won't work because you need to load KF and the KF will be attached to that particular line and you wouldn't get Condition type and Executing Service Employee for the main ODS.

What I would recommend it as long as you know the relationship between Billing Item to the other ods and cube. Create another ods which will be getting data from all the three providers and map properly, that way, you will have a single record.

Hope you understand.

thanks.

Wond

[Former Member]

Thanks for your suggestion.

Now my question is how you do the transformation? I think I'm going to have 3 update rules now, right? Or is there a way I can have only 1?

[Former Member]

It doesnt matter whether you have transformation or update rules - you will still need 3 in either one of them.

[Former Member]

Thanks for the replies.

I have created 1 ODS and brought in values from other 3 ODSs.

My ODS has billing item info. And then I added a Executing Service Employee from CRM_PROH ODS. The common field is an ID.

Now how do I specify in transformations that to pick only that Executing Service Employee where the IDs match?

[Former Member]

Hello!

I read the documentation for the char routine of transformation rules. By the way, we're using 7.0 thats why these are transformation rules.

In the documentation, it says I can create a rule as a reference and use this reference in another rule. But in the rules details screen, I couldnt see how to mark a rule as reference. According to the documentation, we should be able to do it.

Then again I'm not sure if this will solve my problem which is described in my previous message.

Please let me know if you have any questions. And thanks a bunch in advance for all your replies/ideas!