Just a short note about analysis services and its cache 🙂
One of the reasons for analysis services fast respond time is its cahce. But what if you want to test a query without using the cache? Well, just use this XMLA Script:
<DatabaseID>Your Database Goes here </DatabaseID>
<CubeID>And or cube id goes here</CubeID>
Also, if you do partial processing, the cache, can in some cases also be invalid. Therefore, after a partial processing, you also want to run the above XMLA script.
After spending time debugging my analysis services cube for the second time having this error, I guess it will be a good idea to blog about it, so I can remember it to another time 🙂
I got the above error after I had modified a dimension, so used it three fields for the key, instead of one. I made the relationship from my facts, based on the 3 keys, and then I deployed and processed the cube. And then I got the above error.
The reason for the error is, that I had the partitions for the fact table, based on some SQL sentences, and I had forgotten to modify these partitions after I had made the new relation.
So the conclusion: When you get the error “Invalid column name ‘x’.; 42S22” in analysis services, it is a problem related to the partitions design.
We are in a period in time where MS soon will be releasing a lot of (new) BI technologies, such as sharepoint 2010 which contains among other things: excel services, performance point services and hosting of powerpivot. I guess office 2010 will be released the same date as sharepoint 2010, and here we also have a lot of new stuff, especially in Excel (where we also have the power pivot plug-in). SQL server 2008 R2 will also soon be released.
Working as a consultant I have experienced some confusion about when to use what technology, both from customers and BI developers.
To clarify some of it, the powerpivot team has created a great blog post that compares analysis services to powerpivot, and discusses when to use what.
Read it here: http://blogs.msdn.com/powerpivot/archive/2010/03/12/comparing-analysis-services-and-powerpivot.aspx