July 17, 2008 at 12:16 pm
I have a cube that is built from 1 fact table and 9 dimensions. In one intersection point, the cube data is double that of the fact table. I did some experiments and I think it is data that is "lost" in the cube. Here's how I did my experiment. The number in the fact table is 500. The cube has this for 1,000. I changed the fact table data to 499. I was then hoping to see 998 in the cube. The value in the cube is 999. So, that means that my number is 499 and somewhere in the cube is 500. I have done a full process and I cleared the cache. I didn't it via an XMLA and we also rebooted the MSAS server. I checked the fact and dimension tables for duplicates and we didn't find any.
How do I get rid of this orphaned data? Thank you.
We are using MSAS 2005 Enterprise
July 17, 2008 at 12:42 pm
A full process reguilds the entire cube structure, so no residual data could have remained.
It could be a coincidence that the duplication happens to be 500 records. It is most likely that you have a bad join to one or more of your dimension tables. Ensure your dimensions all have the correct number of records and that the field you have joined on is really unique.
July 17, 2008 at 12:46 pm
Thank you. I meant 500 as $500. It was not the row count. I have double checked the dimensions and fact tables and all the keys are unique. If one member was doubled, it would be doubled across the board anyway. That's the odd part. The doubling is also occuring at a leaf. There are no aggregations at that level. Its the lowest form of granularity.
July 17, 2008 at 12:52 pm
It is probably that you picked the wrong field in the dimension relationships in the cube. This is pretty easy to do, especially since the auto-join features in visual studio are a little off sometimes.
Using Management Studio, process the partition that is getting the duplication. The "Processing Progress" dialog will allow you to drill down to the SQL Query it is running. Right-click on the query and copy it into the clipboard. Then go run this in Enterprise Manager. This is probably where the duplication is happening. If you are getting your duplicates here, you will need to figure out which join it is and go into your dimension relationships in the cube and fix it.
July 17, 2008 at 2:28 pm
Thank you. I tried this but it never gave me a query. It was just saying that it was writing to the partition. I even ran profiler on it and it didn't show the query. I don't think it is a duplication in the dimension since it would be duplicates across the board on that dimension member. It isn't though.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply