Dynamics Community Forum Thread Details

Help with OLAP cubes for RMS 2.0

I've been trying to create cubes using Analysis Services 2005 from HQ database. We have around 50 stores in the database. I've produced a simple cube that works more or less, but since we starting using RMS a couple years ago, a couple of stores have gone out of business and believe the store record was deleted without the history, and a bunch of categories were all redone at one time, so some of those are missing. Also it seems like a real pain with relationships, I can't find a "best way" to do it. I've tried making transaction & transaction entry both fact tables, combining the two into a vew for one fact table, the same for trying item and itemdynamic separately and also as a combined view for an Item dimension. Same for Departments and Categories. No matter what I do, it doesn't seem to like my muti-column keys like TransactionId + StoreId for transactions, CashierId + StoreID for cashier, and ItemId + StoreID from Items.

1. Well for the first problem the best I can do is when processing the cube is have it set unmatched keys to unknown I guess, or is there something else I can do? I do plan to archive off the orphaned records to separate database soon.

2. What is the best way to relate the tables if you have experience with SSAS & RMS. I have a datawarehouse for Dynamics GP that was created using the cube setup program that comes with GP. Some SSIS packages are run that feed the data warehouse (a normal relational database combining all the companies). The data warehouse tables are copies of views from the company databases. The SSIS package also then populates the cubes from the datawarehouse. Is that how I should set up RMS, is that necessary for me to "flatten" the data so it can more easily be imported to a cube?

Any information at all would be great. I'm really experienced at programming and relational databases, but very ignorant about data warehousing and olap cubes (but learning rapidly).

Thanks,

Scott Emick

Quick Links

Leaderboard