Category Archives: Analysis services 2008

Analysis services -> Clear Cache

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:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <ClearCache>
 <Object>
 <DatabaseID>Your Database Goes here </DatabaseID>
 <CubeID>And or cube id goes here</CubeID>
 </Object>
 </ClearCache>
 </Batch>

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.

OLE DB error: OLE DB or ODBC error: Invalid column name ‘x’.; 42S22.

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.

Comparing Analysis Services and Power Pivot

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

Enjoy 🙂