How to query data from SQL Server Analysis Services (SSAS) OLAP ?
Article ID: KB0077861
Updated On:
| Products | Versions |
|---|---|
| Spotfire Statistica | 13.0 and higher |
Description
How to query data (with MDX statement) from SQL Server Analysis Services (SSAS) OLAP into Statistica? For the purpose of this article, Statistica Enterprise Manager is used. Similar connectivity can be established via File >> External Data >> Query >> Create within Statistica
PREREQUISITES:
1.In addition to the Statistica installation, Microsoft® Analysis Services OLE DB Provider for Microsoft SQL Server must be downloaded and installed
2. In context of using Statistica Enterprise Manager, the user should have permissions to create a database connection - Either System Admin (SADM) or Database Admin (EXTDB_ADM) and Data Admin (DADM)
3. Details of existing SQL Server Analysis Service Server, database and user credentials. Windows credentials of the user should be recognized as a login by the SQL Server.
PREREQUISITES:
1.In addition to the Statistica installation, Microsoft® Analysis Services OLE DB Provider for Microsoft SQL Server must be downloaded and installed
2. In context of using Statistica Enterprise Manager, the user should have permissions to create a database connection - Either System Admin (SADM) or Database Admin (EXTDB_ADM) and Data Admin (DADM)
3. Details of existing SQL Server Analysis Service Server, database and user credentials. Windows credentials of the user should be recognized as a login by the SQL Server.
Issue/Introduction
How to query data from SQL Server Analysis Services (SSAS) OLAP ?
Resolution
1. Launch Statistica/ Statistica Enterprise manager from the start menu. If using Statistica, select File >> External Data >> Query >> Create . If using Enterprise manager, right click on on Database connections and select "New OLEDB Database Connection"
2. In the Data Link Properties dialog that launches, choose Microsoft OLEDB Provider for Analysis x.0 . If this provider does not appear in the list of providers, MSOLAP provider is likely not installed- contact your system administrator. Click Next. Enter credentials to connect to SQL Server Analysis Service Server
3. Name the connection appropriately, define Access Permissions in Enterprise Manager and then click on commit on the top left hand corner of Enterprise manager. You may optionally Test Connection to ensure the connection string works. Enter a reason for change if prompted by Audit Log.
4. Expand System View in Enterprise Manager, right click on a folder and click on New Data Configuration .Choose the database connection defined in Step 3 for connection from the drop down list. Name the Data configuration appropriately and then click on New button near Queries. If you have an MDX query, you may paste it in the SQL Statement window. If you need to formulate the MDX query, click on SQL Wizard button.
5. From View menu, select Cube/MDX as shown below. Define the MDX query by adding the appropriate dimension and measures in the Row,Column and Slicer. See if the Preview Data tab shows the data as you would want it returned. Optionally, you may review the MDX Statement by clicking on the MDX Statement tab.
6. Click on the green triangular arrow after the MDX statement is defined. Commit the change in Enterprise manager by clicking the commit button in the top left hand corner of Enterprise manager.
7.Explore the data configuration to bring the data into a Statistica spreadsheet for further analysis.
2. In the Data Link Properties dialog that launches, choose Microsoft OLEDB Provider for Analysis x.0 . If this provider does not appear in the list of providers, MSOLAP provider is likely not installed- contact your system administrator. Click Next. Enter credentials to connect to SQL Server Analysis Service Server
3. Name the connection appropriately, define Access Permissions in Enterprise Manager and then click on commit on the top left hand corner of Enterprise manager. You may optionally Test Connection to ensure the connection string works. Enter a reason for change if prompted by Audit Log.
4. Expand System View in Enterprise Manager, right click on a folder and click on New Data Configuration .Choose the database connection defined in Step 3 for connection from the drop down list. Name the Data configuration appropriately and then click on New button near Queries. If you have an MDX query, you may paste it in the SQL Statement window. If you need to formulate the MDX query, click on SQL Wizard button.
5. From View menu, select Cube/MDX as shown below. Define the MDX query by adding the appropriate dimension and measures in the Row,Column and Slicer. See if the Preview Data tab shows the data as you would want it returned. Optionally, you may review the MDX Statement by clicking on the MDX Statement tab.
6. Click on the green triangular arrow after the MDX statement is defined. Commit the change in Enterprise manager by clicking the commit button in the top left hand corner of Enterprise manager.
7.Explore the data configuration to bring the data into a Statistica spreadsheet for further analysis.
Feedback
Yes
No
Powered by
