An OVER clause in a view to create an autoincrement column produces random results in SSAS cube if used as key in a Fact Dimension
A view is created on a large fact table in a SQL Server 2008 R2. In the code for creating the view, an OVER clause is used to create an auto increment number on the fly. Any SQL run in management studio works fine on this view. But when I try to build an Analysis Services Project in Visual Studio 2008 and try to create a FACT Dimension on this view(using this auto increment column as the key), random calculations appear when I browse the cube ( although It does not throw any error). Now if I simply materialise this view into a table and use this table instead of the view in the DSV of the cube, the problem disappears. It seems as if Analysis Services Engine does not like OVER clause.
Check the queries which Analysis services is sending to the database engine. By default, Analysis services is processing dimensions per attribute and for this purpose it is sending multiple queries with DISTING keywords etc which limits the number of rows returned etc. Then if yoou have the OVER clause in the view, it can produce different results for different dimensions/attributes etc. The query being sent by SSAS also depends on attribute relation shpis and other things in the model. If you want to use the OVER clause in a view which is being used by analysis services, you have to be carefull and you have to know exactly what you are doing.