SharePoint 2013 – Creating a data source to an SQL Server Analysis Services (SSAS) OLAP Cube
Below are the details when using SharePoint 2013 when you want to collect to an SQL Server Analysis Services (SSAS) OLAP cube, instead of only being able to use the SSAS Tabular.
· In our example we are going to connect to our Adventure Works DW 2012 SSAS
1. Go into your SharePoint 2013 website and then click on Files, then New Document and then select Report Data Source.
2. This will then open the Data Source Properties window where you will configure it with the following explained below.
a. Where it says Name put in a name so that you know it is your connection file.
c. Next where it says Data Source Type click on the drop down and select the following:
e. Now where it says connection string below is the syntax that you have to use with an explanation below:
Data Source=Server\InstanceName;initial catalog=AdventureWorksDW2012;cube=’Adventure Works’
i. From the syntax above the part highlighted in RED is to specify the SSAS OLAP Name and if required instance name.
1. As with our example it was called:
ii. The next section highlighted in GREEN is your SSAS OLAP Cube database name.
1. As with our example is:
a. initial catalog=AdventureWorksDW2012
iii. The final part which you have to specify is the actual cube name, which is highlighted in PURPLE
1. As with our example it is called:
a. ;cube=’Adventure Works’
f. In the next section which is the Credentials this is what will be used when running the data source.
i. What we have setup in our environment is we have a static domain account where the password does not change. This is so that we can then use this account and grant it permissions to the required cubes as well as roles.
ii. NOTE: We also did this because it is a lot simpler to configure the security to our SSAS OLAP cube via the standard roles.
iii. So with our example we select Stored Credentials and put in our User Name and Password:
2. NOTE: We selected Use as Windows credentials so that it knows it is a Windows Domain account.
iv. Then click on Test Connection to make sure the connection to the cube is correct as well as the credentials.
3. Then click Ok to create the Report Data Source.
4. NOTE: If you are concerned about security for people using this data source to see specific data you can do one of two things:
a. ONE: Change the above credentials to Windows Authentication.
b. TWO: You can also limit who has access to the actual Report Data Source, Power View Report as well as the Folder that stores all the information and set unique permissions.
5. Now as you can see below we created a quick and easy Power View Report on our SSAS OLAP Instance.