July 7, 2008 at 3:48 am
Hello
Iam a newbie and have been tasked with solving a problem in Analysis services.
Iam trying to calculate a classic like 4 like solution in the retail industry where owners would like to gauge the sales performance of a specific costcenter over time. If the no of costcenters stayed static in a year it would be a easy calculation but i have to take into account that more costcentre codes are added. This has been done and is stored in a second table with a flag that indicates 0 for dont take into that days sales account and 1 for do take that days sales into account.
Day1, Day2, Day3, Day4
23 , 15 , 4 , 7 - Sales- From Product_sales_Fact
0 , 0, 0 , 1 - Flag- From 2nd facttable
I have 1 fact table called Product_Sales_fact which contains the following fields:
I have a fact table that contains the following data:
CostcentreCode
Yearcode
Daycode
Gross_amnt(Sales)
I then have another fact table? that contains the following fields:
CostcentreCode
Yearcode
Daycode
lvl_flag
Ofcourse i have a time dimension with Yearcode and Daycode that both fact tables can link to.
Can someone give me some guidance here - i would really love to here some suggestions on possible solutions.
July 7, 2008 at 4:09 am
Hi,
Could you explain what you are doing with that 2nd fact table? I am not sure if I got it correctly - why should you have a flag to indicate a specific cost centre has a day's sales or not? Can't it be obtained from the fact table itself?
Regards,
Neel
Warm Regards,
Neel aka Vijay.
July 7, 2008 at 4:24 am
Neelakandan V (7/7/2008)
Hi,Could you explain what you are doing with that 2nd fact table? I am not sure if I got it correctly - why should you have a flag to indicate a specific cost centre has a day's sales or not? Can't it be obtained from the fact table itself?
Regards,
Neel
HI Neelakandan
Remeber that i dont have to use the 2nd table as a fact ... This is just my thinking - Unfortunately the flag is not in the same fact table and this is what i have to work with. The 2nd fact table indicates whether the specific cost centre code sale on that day should be aggragated or not. 1 for yes and 0 for no. Hope this helps...
July 7, 2008 at 4:37 am
Hi,
When you load the fact table, can't you filter out those records which should not be aggregated? Updating the fact table or joining with another table to check some conditions could be a costly solution and is not recommended.
If I were you, I would:
1. Handle this filtering at ETL layer and load the fact table with only the records that count
OR
2. If the above is not possible (for whatever reasons), have a dimension like 'Counted' for that flag with values "Yes" and "No". Disable the "All" member and make "Yes" as default member. If possible, hide the dimension so that the user cannot choose the "No" option at all. Of course, in this design, you can have a single fact table with 'flag' added to the your original fact table.
Hope this helps...
Warm Regards,
Neel aka Vijay.
July 8, 2008 at 9:32 am
I don't know why you don't combine the tables. They seem to have the same level of granularity, which is the key factor in determining the structure of your fact tables. I would recommend you relook this. You can google "level of granularity" and Ralph Kimball does a great job of explaining this in his books.
July 8, 2008 at 9:54 am
RonKyle (7/8/2008)
I don't know why you don't combine the tables. They seem to have the same level of granularity, which is the key factor in determining the structure of your fact tables. I would recommend you relook this. You can google "level of granularity" and Ralph Kimball does a great job of explaining this in his books.
Hi Ron
The reason why we want to keep it as 2 fact tables are simply because of administration as we feel its easier to change the "flags" if a certain date should be aggregated or not - as business users might change their mind - otherwise a small change would mean reprocessing the cube. Ive also tried to set the lvl_flag aggregate function property to lastnonempty but still no luck. I know that the simple solution lies in solving this in the ETL lvl but my brief is that this must work with 2 fact tables. Do you have any other suggestions that might help?
July 8, 2008 at 11:05 am
Sorry, I don't. I had thought that you could try to create a view and then add that to the data view, but I don't know how you get around the reprocessing issue in any case. If there's a change to the data, the cube is going to have to be reprocessed unless you're going to access the data through SQL. So I don't see how you save administrative overhead this way. Because it's complicating what would otherwise be simple (as you've already indicated) it seems to me it's complicating the administrative overhead.
We'll just wait and see if someone else has run into this. Seems I remember something similar not too long ago. You could try to find that thread and see if a solution was offered.
July 8, 2008 at 11:14 am
RonKyle (7/8/2008)
Sorry, I don't. I had thought that you could try to create a view and then add that to the data view, but I don't know how you get around the reprocessing issue in any case. If there's a change to the data, the cube is going to have to be reprocessed unless you're going to access the data through SQL. So I don't see how you save administrative overhead this way. Because it's complicating what would otherwise be simple (as you've already indicated) it seems to me it's complicating the administrative overhead.We'll just wait and see if someone else has run into this. Seems I remember something similar not too long ago. You could try to find that thread and see if a solution was offered.
Can you offer your thoughts on creating a view? We just want to reprocess 1 smaller partition (with the flags) rather than 1 BIG partition.
July 8, 2008 at 11:21 am
I can tell you what I was thinking you might do, but if it doesn't work it doesn't work.
Given your restrictions, one way I might try to crack this is to create a view which is a combination of the two tables, add the view to the Data Source view and then try creating and compiling a cube based on that. I'm not optimistic about this solution because if the cube is based on the view (and I don't know that that will work) and you change the data in an underlying table, I don't see how you avoid reprocessing the cube again anyway. But if nothing else occurs to you, you could try that, if only to remove it as a solution.
If that works, please let me know. I don't know that I'd ever use it, but you never know.
July 10, 2008 at 1:57 am
Hi,
I think your solution lies in the reason why people would want to 'take into account' or 'not take into account' certain costcenters sales. Go back to the definition, why this functionality what do we want to know ?
If dont go 'back to the roots' will go round in circles because there are multiple technical solutions but all are dependant on what the information need is and means.
Cheers,
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply