Assigning destination

[Former Member]

Hello, My client has a requirement of assigning new shipto country based on company, profit center and shipto country. so basically these are the three dimensions - Company, profitcenter, and shiptocountry. now for specific combinations, client wants to reassign in coming data to new shiptocountry. The user should be able to add to these combinations. In other words, they should be able to say, for company code - 100, profitcenter - pca2, shiptocountry - USA, change shiptocountry to Europe. they are using microsoft 7.5.

So here are my questions

1 - is data maanger only option available for user to go ahead and make this change?

2 - - where can I store all these rules?

[Former Member · Accepted solution]

Hi,

This kind of thing can be done using script logic. However, you will have to maintain all the combinations. Alternate is to use stored procedure as indicated by Leandro. However, you need to maintain all the combinations in the stored procedure as well.

Having said this, running the stored procedure will be faster than script logic. But you can use script logic also.

Hope this helps.

[Former Member · Accepted solution]

Hi,

As previously noted mentions there are few options to do this kind of work each having advantages and disadvantages. However, as per your requirement, it is not clear who would maintain the master list of assignments. It could be a fake dimension in which case end users can not access it, flat file in specific DM folder or the mappings could be stored direcly in the fact table and modified using spefic schedule.

Madis

[Former Member · Accepted solution]

Nilanjan, the trick is user has to be able to maintain all these rules, how about creating a dimension where user can maintain these rules(company,profit center, shipto, new shipto). then having a stored procedure run on the back end on the table?

thanks.

[Former Member · Accepted solution]

It's entirely possible to have a dummy dimension table in the system to maintain the rules. Your custom SP can then be pointed to appropriate mbr table to look for the rules.

In this case the only downside is that you need to have access to dimension maintenance in order to update the rules. Apart from that there's nothing that stops you.

[Former Member · Accepted solution]

OH Ok, so something like this should work?

Create procedure proc1

As

Insert into tblFAC2Reporting

(account,

Category,

Company,

Datasrc,

Destination,

Intco,

Profitcenter,

Rptcurrency,

Timeid,

Signeddata,

Source

)

Select account,Category,Company,Datasrc,d.newDest,Intco,Profitcenter,Rptcurrency,Timeid,Signeddata,Source

From tblFAC2Reporting R, dentalDestMstr D

Where r.profitcenter = d.profitctr

And r.company =d.company

And r.destination = d.originaldest

FAC2reporting is the app table.

[Former Member · Accepted solution]

Yes, in theory it should work but please bear in mind that:

1) select should include all partitions (FACT,FACTWB and FAC2)

2) it may be a good idea to use temporary table for intermediate results

3) inserting directly into FAC2 requires processing the partition

Madis

[Former Member · Accepted solution]

Thanks a lot Madis for your valued input.

Couple of more questions -

1 - the client would like to make this change retroactive to 01/01/2011. do you foresee any issues with that?

2 - if the client wants to see the records thats been changed(original ones), would that be possible?

Thanks.

[Former Member · Accepted solution]

Zack, I wouldn't expect any issues apart from the fact that any bespoke development is always a rist. So if every measure is taken and everything thoroughly tested I wouldn't worry too much.

Regarding logging the changes I'm not so sure. You may want to try data auditing but you need to test to see how this works out. Aside from that you can always amend logging functionality to your custom SP and report against that table.

Madis

[Former Member · Accepted solution]

Thanks a lot madis for your help, my manager didnt approve of this solution- he wnats to do this in Script logic only not stored procedure. Any suggestions?

[Former Member · Accepted solution]

Zack, I fully understand your boss' concern. Custom SP is not supported in any way whereas script logic is a standard feature. Aside from that I agree with Leandro that SP is far better manipulating SQL data. I would bet that script logic with similar functionality is several times slower that SP.

However, I think it might be possible to get results with script logic but beware of pulling must of the hair out and hearing complaints about the system being slow.

Madis

[Former Member · Accepted solution]

You are both right but I am new to this project, plus the boss has been there for many years. so i think i will have to try without stored procedure first. Madis - in earlier post you had mentioned this - Quote - " the mappings could be stored direcly in the fact table and modified using spefic schedule."

so are you recommending building another app? how would that work? signed data could only be numbers? please elaborate on that. thanks.

[Former Member · Accepted solution]

Zack, what I mean by storing the mapping in the fact table is based on the concept on how the consolidation works, specifically how the organizational master data is stored.

For example, you could maintain an input schedule which helps tou to store value "1" on all valid "target" intersection e.g. DimAX.DimBY.DimCZ = 1 whilst invalid target might be DimAZ.DimBX.DimCY = 0.

In this way all you need to do is use the intersections as factor in your logic e.g.

WHEN DimToAllocate

IS MemberToAllocate

REC(FACTOR=GET(DimA=X,DimB=Y,DimC=Z),DimA=X,DimB=Y,DimC=Z)

ENDWHEN

Obviously you need to develop your own method with fancy loops and everything but I hope you get the idea. This method at least seems to have few advantages over the others as the mappings are dynamic over time and can be easily maintained by the end users. You don not need another application, you could just as well use the current one. Just beware - it will be slower that methods strored directly in the DB.

Madis

Edited by: Madis Udam on May 20, 2011 8:00 AM

[Former Member]

Fire your manager?? hahah

I don't think you will be able to do all this using script logic, and even if you do, and use it on the default logic, it will definatily slow down your system..

Tell him that stored procedures has NO downsides, it's FASTER, EASIER to program and mantain and most important, EASY TO TEST.

Sorry, if i was rude, it's because i've been right where you are, i had to prove to my manager that he was wrong, and that stored procedure were gonna save us, and it DID =D

Best of luck

[Former Member]

And some further information to what i said..

In you SSIS package, make sure you link and ADMIN_OPTIMIZE component to you LOGIC_TASK, because when the proc runs and change something in the fact table, you will need to optimize to get the results showning on your reports.

If you give me your email i can send you the SSIS package ready, all you had to do is create the procedure the logic file and adit the MODFYSCRIPT variable to fit your needs.

Best

Leandro Brazil

[Former Member]

Is it possible to have the user maintain a dimension with all these rules(company, profit center, shiptocountry, new shiptocountry), then run stored procedure on the backend on the same table(each dimension is a sql table)? so basically with every data load, the stored procedure will look at this table with all these rules and reassign?

Edited by: Zack Thacker on May 17, 2011 5:57 AM

[Former Member]

I didn't fully understood your requirement.

But when th buseness rule is way too complicated, what we do here, since you r using the microsoft version..

Create a procedure in your database, and that you a sript *RUN_STORED_PROCEDURE combined with the data manager to run this proc and pass along the variables.

I hope this give a way of getting this done.

Best

Leandro Brasil