Like the title says; my SSAS OLAP cube (SQL 2008R2) was giving me wrong results when selecting a combination of a measure and a fact. I was struggling for days with this issue. I called old colleagues, reading a lot of articles online but no solution yet.
The strange thing was that my SQL query against the views I used in SSAS was giving me the RIGHT result. When browsing the cube, it gives me the wrong result. Things I tried:

  • Delete the cube from the server, deploy with a new database
  • Kill all dimensions and only use the single two that gives me the wrong result
  • Delete all roles and use a single admin role
  • Create a new cube with just one fact one two dimensions: WORKS

Now I knew that it was a setting in my cube. It was hard to find what was wrong.

I tried to delete all objects, one by one. I started with one of my Datasources. I had two data sources, one to production and one to test. Because I switched to TEST last week, I tried to delete the data source to Production. This gave me a strange error:

ssas_datasource_error

AHA! This message was ringing a bell. Why did I get this error? I already switched my data source view to the TEST connection. Hmmmmmm. There was something wrong with the partition. I am using partitions to split up my large fact tables to smaller year-partitions for performance. I am using queries to this this, other than table bind partitions.

ssas_partitions

When you highlight the partition and press F4 (properties), you can press the “Source” button in the properties window.

ssas_partition_properties

Now press the … button. AHA!  The partition was getting it’s data from the WRONG datasource!

ssas_partition_datasource

So what does this mean? Well, it means that it’s getting it’s data from my production server. That’s pretty strange because my data source view is pointing to my TEST data source.

Lesson learned: when changing data source views (From Test to PRD or visa versa), also change the partitions that are using query binds! When you don’t change the partition, it’s mixing op it’s data between both servers. You only have to do this when you restrict partitions by a query. Normally, a partition is default table bind.

partition_table_bind

Hope this helps other people having the same issue with wrong cube data.

Frequently asked questions

Why does my SSAS cube return different numbers than my SQL query?

One cause is a partition that reads from the wrong data source. In this case the partition used a query bind that still pointed to the production server, while the data source view pointed to test. The cube then mixed data from both servers.

How do I check which data source an SSAS partition uses?

Select the partition and press F4 for the properties. Click the Source button, then the ... button. The dialog shows the data source the partition reads from.

What should I change in the partitions when I switch an SSAS data source view from test to production?

Change every partition that uses a query bind to the new data source. Partitions with the default table bind do not need this. Skip it and the cube mixes data from both servers.

How can I find a data source problem in an SSAS cube?

Try to delete the data source. If a partition still uses it, SSAS shows an error. That error pointed to the partition with the wrong data source in this case.

What is a query bound partition in SSAS?

It is a partition that gets its rows from a query instead of a whole table. You can use it to split a large fact table into year partitions for performance. The default is a table bind.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.