SSAS OLAP is returning wrong results

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:

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.

 

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

Now press the … button. AHA!  The partition was getting it’s data from the WRONG 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.

 

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