May 18, 2009 at 4:33 am
I've posted this before in another thread, then withdrew the question thinking I knew the answer. But apparently I don't because I can't get the cube to look right...
I have two tables. A general summary table and a detail table. Both tables have been pre-aggregated as part of our month end process. They both contain a PayeeID and two other relevant entity IDs, a BeginDate / EndDate, Counts of specific statuses and SUMS of money. The summary table is aggregated on the Seller level while the detail table is aggregated on the Order level.
And that's the simple part.
The tables also have a ReportType# which indicates what record is for what report. There are 4 reports and therefore four entries for each month. The reports simply grab the records in the table corresponding with their ReportType#.
Now I'm told I have to cube these tables. When I let the Wizard do it, the summary table becomes the fact table and the detail table becomes the SOLE dimension table. I tried adding a named data view for the time dimension, using Month() and Year() functions on the EndDate of both tables, but SSAS is telling me it can't understand the relationship between this NDV and the other two tables. Plus, I can't seem to break out other dimension tables from these two tables or add new measure groups because these two tables are "already used in other measure groups".
I'm getting confused and frustrated. Everything I'm reading about SSAS teaches one to use tables that aren't pre-aggregated or create a new database from scratch. It seems that I would have to de-aggregate (is that even a word) the data before I re-aggregate it in the cube. I don't want to create a whole new database for this cube since the tables already exist in a current database. Plus undoing the data and redoing it would probably cause errors.
Any advice, thoughts, weblinks?
Thanks in advance!
May 18, 2009 at 4:47 am
I think you'll have to stop using the wizard!
Create your own data source view (DSV), you can create named queries in place of tables and generate the source for the dimensions based on that, obviously ignoring any aggregated values.
You can then use the cube building wizard once the DSV has been satisfactorily created
Aggregates in a data warehouse are definitely a no-no
Just had a second read - are you missing the actual detail values?
May 18, 2009 at 5:07 am
I'm not missing detail values so much as the details are even aggregated. I know that sounds odd, but that's the way it is. Say you have an order with two returns and three sales. The sales are all aggregated into one SalesTotal and the returns are all aggregated into one ReturnsTotal. Then I have a ReturnCount of 2 and a SalesCount of 3.
Roll that up to the Seller level and you have my summary table.
And this isn't a datawarehouse. This is an OLTP system that has two pre-aggregated tables that someone wants me to throw into a cube now.
When you say "obviously ignoring any aggregated values", how am I supposed to do that when the totals already are aggregated? That's the part that I don't get. Like I said, if I "undo" the aggregation, chances are I'll get the totals wrong when re-adding them.
May 18, 2009 at 5:55 am
Brandie Tarvin (5/18/2009)
When you say "obviously ignoring any aggregated values", how am I supposed to do that when the totals already are aggregated? That's the part that I don't get. Like I said, if I "undo" the aggregation, chances are I'll get the totals wrong when re-adding them.
Sorry about that, I thought you might have details + aggregated values like this:
invoice number | invoice line number| line amount | invoice amount
0001..............| 01.......................| 13.23....... | 26.00
0001..............| 02.......................|12.77.........| 26.00
You'll have to live with the aggregated values which will remove the line level of granularity at fact level
So in your data source, create fact tables using named queries at the invoice level, then dimensions which link to it.
It should give you the appearance of a data warehouse star schema without actually having the data warehouse
If you could post the description of the tables you're using I might be able to help more
May 19, 2009 at 4:53 am
Ah. Okay. I think I'm getting it. But here's a sanitized version of the main summary (Fact) table:
Create MyTable (ReportType tinyint, BeginDate datetime, EndDate datetime, PayType varchar(20), BatchNumber int, PayeeID int, PayeeName varchar(40), SellerID int, SellerName varchar(40), SellerGroupID int, RegionSalesDirector varchar(10), SalesCount int, ReturnCount int, HeldSalesCount int, RejectedSalesCount int, Commissions money, NetTotals money, GrossTotals money, PayeeFee money, Taxes money, CommissionsOnHold money, PayeeFeeOnHold money, NetTotalsOnHold money, GrossTotalsOnHold money)
There are other columns and I did change the name of several columns, but that's the general idea for the summary table. Payee is not necessarily the same as Seller. They can be the same or the Payee can be the person who sold to the Seller. The middleman, so to speak.
The detail table is more like:
Create MyDetailTable (ReportType tinyint, BeginDate datetime, EndDate datetime, BatchNumber int, PayeeID int, SellerID int, RegionSalesDirector varchar(10), OrderID int, ActiveOrderCounter tinyint, HeldOrderCounter tinyint, RejectOrderCounter tinyint, CancelOrderCounter tinyint, ProductID int, ProductCode varchar(10), RegionSalesCode varchar(10), SalesAmt money, HeldAmt money, RejectAmt money, CancelAmt money)
The more I look at the detail table, the more I think I'm wrong on the aggregation of it because it has ProductID and ProductCode in there, which is very singular. So, Detail is not aggregated, but the Summary table is.
Fields in common between the two tables are the ReportType, BeginDate, EndDate, PayeeID, SellerID, RegionSalesDirector, and BatchNumber. I originally had joined the two tables on all these fields in my first attempt to set up the cube, but then couldn't use any of the fields as dimensions. Or at least, couldn't figure out how to group by Seller / Payee / RegionSalesDirector, etc.
Any additional advice is greatly appreciated. Thank you for your help so far.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply