Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
No data in OLAP
- Thread starter mmattson
- Start date
I could be wrong, but whenever I have more records in my fact table than my cube, it's usually because the values in my dimensions do not match some/many values in the corresponding field in the fact table.
This is from my experience, and there may be a faster/better way to do this, but to troubleshoot it, I process the cube, and when it gets to generating the SQL Statement, I double-click the little yellow icon with "SQL" written inside. Copy the select statement and in Enterprise Manager, paste it into a view. This is so you can see the statement visually. Keep the joins etc in the select statement, but replace the columns in the select part of the statement with count(*).
Run this statement to see the count of records that match this query (in your case, it's probably 0).
One by one, remove each dimension from the view and run the statement again. Hopefully, the count should start increasing.
I've never had the problem with having no records in a cube, it's usually just 1 dimension being out. To fix it, i do a left outer join, selecting all records in the fact table where there is no corresponding value in the dimension table. That's how I find out what values are missing from the dimension. If i add these values to the dimension and reprocess the cube, then my fact table and OLAP cube seems to balance.
This is from my experience, and there may be a faster/better way to do this, but to troubleshoot it, I process the cube, and when it gets to generating the SQL Statement, I double-click the little yellow icon with "SQL" written inside. Copy the select statement and in Enterprise Manager, paste it into a view. This is so you can see the statement visually. Keep the joins etc in the select statement, but replace the columns in the select part of the statement with count(*).
Run this statement to see the count of records that match this query (in your case, it's probably 0).
One by one, remove each dimension from the view and run the statement again. Hopefully, the count should start increasing.
I've never had the problem with having no records in a cube, it's usually just 1 dimension being out. To fix it, i do a left outer join, selecting all records in the fact table where there is no corresponding value in the dimension table. That's how I find out what values are missing from the dimension. If i add these values to the dimension and reprocess the cube, then my fact table and OLAP cube seems to balance.
- Thread starter
- #3
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question