Microsoft SQL Server Analysis Services

Microsoft SQL Server Analysis Services (SSAS) can be used for work with OLAP cube which is one of the possibilities how to create UI to the Data Mart.

Microsoft SQL Server must be installed and running with the following minimum components:

• Database Engine
• SQL Server Agent
• Analysis Services deployed in Multidimensional and Data Mining mode
• Integration Services
• Workstation components (including SQL Server Management Studio)

For granting permissions to Analysis services see https://docs.microsoft.com/en-us/sql/analysis-services/instances/configure-service-accounts-analysis-services.

Creation of the SSAS database

1. Make sure to enable all necessary Windows services on the server where Analysis services run.
2. Run Microsoft SQL Server Management Studio and connect to the Analysis services.
3. Click New query in the menu on the top of the window.
   
4. Locate SafeQ6_CRS.xmla file inside the installation package under Complementary Solutions\Enterprise Reporting\.

5. Paste content of the SafeQ6_CRS.xmla file into the query window and click the Execute button in the menu on the top.
   

   

   
   
   

   Microsoft SQL Server 2022

   XML

   For the SSAS version 2022 you need to change connection string in data source. Please replace line in xmla file: 
   	<ConnectionString>Provider=SQLNCLI11.1;Data Source=127.0.0.1;Password=;User ID=;Initial Catalog=SQDB6</ConnectionString> 
   with the new connection string:
   	<ConnectionString>Provider=SQLOLEDB.1;Data Source=127.0.0.1;Password=;User ID=;Initial Catalog=SQDB6</ConnectionString>

6. In the messages at the bottom of the window you should see Execution complete message.
7. If you right click the databases in the left pane and click Refresh in the context menu you should see new SSAS database you created (e.g. SafeQOLAP).
   

   

Configuration of the data source

1. Expand the database of Analysis Services you created (e.g. SafeQOLAP) by double-click, then expand Data sources by double-click
   

   

   
   
2. Right-click SQDB6 data source and select Properties in the context menu, then click the line with Connection String.
   

   

   
   
3. Click the little "..." icon on the right side of the Connection string line to to re-define data source
   
   • In the Connection Manager window define the connection to YSoft SafeQ database (such as server name, authentication type, database name) and test if it works, then confirm by OK
     

   • 

4. If you used Windows Authentication in the previous step, click "..." next to Impersonation Info and define Windows account that has administrative rights for Analysis Services and also db_datareader or db_owner rights for SQDB6 database. If you used SQL Server Authentication, skip this step.
   

   

   
   

Scheduling of the DataMart_OLAP SSAS database processing

1. In the Microsoft SQL Server Management Studio connect to the Database engine.
2. Expand SQL Server Agent, right click Jobs folder and from the context menu select New job. Fill in the Name field of the new job.
   

   

   
   
   
3. In the left pane select Steps and click the New button.
   
   1. Fill in the Step name field.
   2. In the Type field, select SQL Server Analysis Services Command.
   3. In the server field, fill localhost.
   4. Locate the Process_DataMart_OLAP.xmla file in the installation package under Complementary Solutions\Enterprise Reporting\.
   5. Paste the content of the Process_DataMart_OLAP.xmla file into the Command field and click OK to close the window.
      

   

   
   
   
4. Select Schedules in the left pane of the New job window and click New...
   

• Configure the job scheduler to your liking.

• • 

    Please note that every time this job will be performed the data from YSoft SafeQ production database will be read and transferred over the network to the reporting server.

  • 

• Verify that Scheduled task finishes successfully (right-click the task → Start Job at Step... → wait for result). If an error is shown, add the account used to run "SQL Server Agent" service to your OLAP database:
  1. OLAP database → Roles → New Role
  2. tab General → Fill in any "Role name" and tick "Process database"
  3. tab Membership → add the account of "SQL Server Agent" service
  4. click OK

How to configure external access to the OLAP cubes

For more information see How to Configure External Access to the OLAP Cubes.