Not all data coming through to AS2000 cube based on view
I have a cube in development based on a view which has 4279 rows, but AS will only return a fact table row count of 4230.
There is also a private dimension (built from the view) which should have the same number of members as the number of rows in the view, and the member count is similarly askew.
I have checked every dimension for non-matching data and managed to account for 20 rows where we need to fix some fact data, but the rest just appears to be missing.
I can see the missing 29 rows in the view and I cannot see any problem with the data, but it just doesn't appear in the cube.
I have tried creating the cube with the view plus 1 measure and 1 dimension only, and encountered the same problem.
I tried it on our production server and again not all of the rows came through.
I have checked permissions on all of the tables involved in the view and can't think what to look at next.
I am baffled as I have never encountered a problem like this and wondered if anyone had any ideas as to what could be causing it.
A lot of the dimensions in the cube are private ones which are built directly from the view which is acting as the fact table, and this is the only real difference between this cube and the many others I have built, which have always used shared dimensons - can doing this cause difficulties; does anyone have any experience of this?
Thanks
Rachel
[1453 byte] By [
RachelJ] at [2008-1-2]
When you process cube, capture statement that is used to read data from SQL Server. You can capture it using SQL Profiler or if you process from Analysis Service Manager, one of the log lines will show statement. YOu can analyze that statement to see what joins are made and why records are missing.
Vidas Matelis
Thank you Vidas; I forgot to say that I have already captured the SQL - when I ran it against the database with a count(*) I got the correct amount of rows! Which is why I am really really puzzled - how can the query used to process the cube return the correct number of rows if AS cannot see some of them in the fact table/view?
Rachel
Rachel,
While processing this cube, does Analysis Services reports reading 4279 or 4230 rows? How do you get count 4230 ? From the measure that is count?
Vidas Matelis
If I force AS to count the fact table rows it gives 4230; the count measure gives the same. I don't think you can see how many rows are involved when the cube is processed can you (please correct me if I'm wrong there)?
That's why I took the SQL from the processing dialog box and ran it to check the rows it returned - which was the higher number.
Rachel,
In Analysis Manager if your right mouse click on cube and then choose option "Process" you will be asked for process method. When you click OK, it will start processing. For each partition it will show SELECT statement that was used, how long it took to process and how many rows were read. I wanted to make sure that this number was bigger one. By the way, I am assuming that you are using MOLAP storage.
If you are using ROLAP, then use profiler to capture query executed while forcing count of fact records from OLAP.
If you are using MOLAP and your process statement reports less than 4279 records, then there is a problem with joins, so review them - especially if you have any NULL values in any fact table fields.
If you are loading 4279 records, but OLAP query comes back with 4230 records, then make sure:
- All dimension have property "All Level" set to yes.
- All dimension have NO (!) default value specified. Default value will change your counts!
- In cube create new measure and choose aggregation function as count. Double check if value is correct.
If this does not work.
Copy database to another database. Start removing one by one dimension, reporcessing cube and check for number of records. YOu will hit dimension that causes this and that will help you find your problem.
Copy database to another database and in the cube choose "Tools"->"Optimize schema". After keys are changed, try to process this. If you will have any fact records that do not have matching dimension records, this will give you error. You'll know what dimensions you are missing from error.
Try this, if nothing works, let us know again.
Vidas Matelis
The partition processes 4221 records!
This is the weird thing; if I fish out the partition sql and run it it gives the correct number of rows. Also, one of the dimensions should have 3 members, #, Y and N and the SQL from the processing dialog box for that dimension (very simple, just SELECT DISTINCT dbo.vwCMS.drReporting FROM dbo.vwCMS) when run against the database gives that result; but in the cube, it produces only two members, Y and N.
Yes, I am using MOLAP, all dimensions have all level set to yes and no default member. There is already a count measure and this says 4221.
The database is very large with over 30 cubes so rather than copy it that is why I tried creating another cube from the same view with 1 (count) measure and 1 dimension (the one that should have the same number of rows as the fact table), but experienced the same problem.
I have tried optimising the schema already, and have checked for non matching fact table records (found 20).
I have finally tracked down the problem to a case statement in the where clause of the view, which of course AS will not handle and just treats as a straight join, thereby excluding rows. (I did not create the original cube!)
I can fix this by creating a proper table on which to base the cube.
Thanks for all your help
My previous post did identify part of my problem with the view, but I am still finding that, although the fact table row count is now correct, if I look at a measure based on a Count of the ID (unique column), I am still gettting incorrect amounts, missing about 50 rows.
I cannot see how this is possible as each row has a unique id.
When I look at this measure broken down by the claim reference (which may not be unique), most show correct counts of 1 or nmore, but the missing ones show NULL against this measure, although in the view they do have an ID.
If I convert the view into a table, with no change to the code at all, it all works perfectly.
Is there some bug involved with using views as fact tables on AS that anyone knows of? I am completley baffled!
Thanks
