Dimension Level Secuirty(User Level Security) based on 2 dimension

In SSAS cube, sometimes we will get the request to allow user to see only his/her data . So we will create a user dimension and link it with facts. We will do necessary changes in the role dimension level security. We are refering this as user level security.

What if i need to allow the user data is not linked with fact, like i want to allow user to see the country level data that was assigned to him? 
We will create many-to-many relationship with fact table and user dimension by reference table that can have both user key and country key

What if i need to allow user based on 2 dimensions, like i want to allow user to only sales department data for the specific country?
As of now, you may not seen any sample like this in internet. I had this scenario recently. I have to allow user to see some airline information and I have to limit him to see only specific ratio for those airlines. Both Airline and Ratio are different dimensions. Below is the design. I want to allow user to see only {Airline 1, Ratio 2} and {Airline 2, Ratio 1}. How can you limit the user ?

 I tried to implement the default way of Dimension Level Security.

I had hope that this will work fine and processed my cube. I see the real problem after completed processing. It show {Airlines 1, Ratio 1}, {Airline 2, Ratio 2}, {Airline 2, Ratio 1}, {Airline 2, Ratio 2}. It allows user to see the result where he didnt have access.

Problem here is SSAS allows user to see Ratio 1 and Ratio 2 by my first configuration and Airline 1 and Airline 2 by my second configuration. I cant able to combine both the query in single place, as both are different dimension and cant able to access.

 So after my research i added one more configuration in Cell data.

This check the user access cell by cell and shows null value {Airlines 1, Ratio 1} and{Airline 2, Ratio 2}
which i dont have access. Still i can see those combinations in my result set, but i cant able to see the values.


I am working on hiding those combinations too in the result set. Once i reach that i will update you.
Note: I am trying to access this cube in Excel.

Pivot Viewer with SSAS

Pivot Viewer is a new tool in MS BI. It was proposed to use with large amount of data. Microsoft samples state that it can be used with SSRS to show to details from SSAS cube.


Silverlight based tool will work with SharePoint 2010 and render the details in high graphical.


Download : http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=d31f609d-a353-41ad-a1a4-f81456e3a6c4
Demo : http://www.microsoft.com/showcase/en/us/details/cc397c17-fe21-4dc0-a408-249dbb3299ae

Measure value shows in negative in SSAS

In SSAS, If you have measure type as int, in some cases you can see cube aggregated results in negative. 


Source:
This is because of the limitation of data type.Integer will support -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). If the aggregated data was falls out of this range, then it shows wrong data or data in negative values.


Solution:
To resolve this issue, select the SSAS Cube measure in the BIDS and change the measure DataType property and Source DataType property to BigInt. Reprocess the cube and check the result.

SSAS dimension process

In SSAS(SQL Server Analysis Service), big dimensions like dimensions related to daily sales or ticket booking will have millions of rows. Thousands of row can be inserted in the dimension on daily process. Processing these type of dimensions on daily basis will take long time.

Microsoft, some place refer to use ProcessAdd to process the dimension by XMLA code. But it was not supported by tool process, it was not recommended by microsoft.

ProcessAdd - This wont handle changes in the existing dimension data like updation, deletion. It can handle new inserted data only.

XMLA code segment to process dimension by ProcessAdd is not clear. We can do ProcessAdd by two types:
1) Push method
2) Pull Method

Push Method:
New data needs to be added will be hard coded and make the XMLA to process the dimension. Some samples are available online
Negative - XMLA handling is tough,
Positive - Dimension process will be faster

Pull Method:
We need the pull the data added newly. This is simple and XMLA handling is very easy when compare to push method. Samples available in net wont work normally.
Negative - Process performance is slow when compare to push method
Positive - XMLA is very easy to handle


Sample:
Create the dimension with source or view and process the dimension and cube. Right click on cube, generate XMLA alter script. We need to use the dimension attribute XMLA script to process the dimension by ProcessAdd.

Sampe XMLA code  to process the dimension by ProcessAdd Pull method.