After a long long struggle finally me and my team was able to run the Analysis Services reports on SQL Server 2008. We had a big trouble while installing the Analysis Extensions of MS Dynamics 2009 on SQL Server 2008. Moreover, 64-bit added to more troubles.
While installing Analysis Extensions on a machine running SQL Server 2008, as soon as you check the checkbox for Analysis Extensions you would encounter an error SQL Server 2005 Analysis SP2 or higher required to continue the install.
There are quite a few discussions going around in Microsoft and other forums relating to this issue. We came up with a workaround which might not suit practically all the scenarios discussed in these forums.
First things first, we were not able to install Analysis Extensions on our server. The obvious question which came to our minds was :
- What is that the installation does?
- What are the impact areas of the Analysis Extensions setup?
- Will the Administration->Setup->Business Analysis->OLAP Administration setup forms work without the installation?
In the following paragraphs we will discuss these questions and our approach to solve the problem.
What is that the installation does?
We observed with our previous installation efforts done on SQL Server 2005 platform that the Analysis Extension just creates a Dynamics AX OLAP database in the Analysis Server. The latest installation guide of Microsoft Dynamics AX 2009 (Jan 2009 Edition) confirms with the above assertion.
What are the impact areas of the Analysis Extensions setup?
So far we have not seen any major impact areas of the Analysis Extensions Setup.
Will the Administration->Setup->Business Analysis->OLAP Administration setup forms work without the installation?
Yes, it is possible to continue working with the OLAP Administration utility which would recognize the OLAP databases.
Workaround
Considering the fact that Analysis Extensions only installs an Analysis Services Database. The AX installation folder has analysis services Database script present in the support\Analysis Services\Scripts folder called as Dynamics AX. Follow the steps below:
- Open the script in SQL Server Management Studio.
- Locate the following entry:
Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DynamicsAx1
Change the connection string appropriately to point to the Microsoft Dynamics 2009 AX Database. Remember to change the Provider information as specified in the Latest Installation Guide. For your reference the Provider should be SQLNCLI10.1 for SQL Server 2008.
-
Execute the script, this will create the Dynamics AX Analysis Services database.
-
Open the Dynamics AX Client which you are using to connect to the Analysis Service Database.
-
Navigate to Administration->Setup->Business Analysis->OLAP and open OLAP Administration form. You should now be able to see the newly created Analysis Services Database under the OLAP Database Tab here.
Steps Further
The following are the steps for configuring the newly created Analysis Services database.
- Select the Database in OLAP Administration and go to the Advanced Tab, Select the Checkboxes Update BI Data, and Synchronize OLAP Database with OLTP Schema. Click on Update Database, this will take a while to update the data into the Analysis Services Cubes.
- When done with the previous step, Click on Setup Exchange Rates and click on OK button.
- Here comes an important step of deploying the ODC files, these files are the Data Connection files for the reports to connect to the Cubes. Please note that the connection string specified in these ODC files is coming from a Report Library, called as Shared Library. We need to proceed further to edit this library first to connect to our SQL Server 2008 Analysis Services, since the original Library has provider set for SQL Server 2005 Analysis Services i.e. MSOLAP.3, this should now be MSOLAP.4. To do these changes
- Right click on the Shared Library Report Library and click Edit in Visual Studio
- Locate the DynamicsAXOLAP.moxl file and open it.
- Right click on DynamicsAXOLAP data source and view its properties. Change the Provider in the Connection String property to MSOLAP.4.
- Right click the Shared Library project node and say Save to AOD.
- Go to AOT and browse to Shared Library and right click to Deploy.
- Now we are ready for Deploying the ODC files for each cube. Just Click on the Deploy ODC Files Button in OLAP Administration. You can verify the changes in these ODC files by browsing to your Data Connections List in SharePoint and opening the ODC file in notepad. ensure that MSOLAP.4 is present as a provider in the connection string.
- Now we are ready for processing the cubes. Open the Analysis Services Database in SQL Server Management Studio. Right click on the Database and click on Process. This will Process all the cubes and populate the data into the Dimensions.
Troubleshooting
There can be certain errors which can prevent the reports from displaying in Enterprise Portal. I would discuss some errors which I encountered while configuring the Enterprise Portal for Analysis Services.
Case of the Missing Parameter
Default value or value provided for the report parameter is not a valid value. (rsInvalidReportParameter).
Resolution
This is majorly caused due to the data not being populated in the cubes. you need to check the reports by editing them in Visual Studio. Try to browse the Design of the report by specifying the parameters. If there is any error it will be shown in the Errors and Warnings window. To properly examine the error, check the datasets and their queries. There might be some datasets which get their parameters from other datasets, in such a case you need to first check the parameter dataset for the Tuple being passed as a parameter. Generally, the reports should run but due to a failed query execution it can stop the processing by saying a parameter or default value is expected.
Case of missing Key Performance Indicator (KPI)
Query execution failed for dataset ''. (rsErrorExecutingCommand) Get Online Help Query (2, 1) The ' ' argument passed to the 'KPIVALUE' function must be a Key Performance Indicator (KPI) name.
Resolution
This error is majorly due to some dimensions in the cubes not being populated with data. To really find out the problem you need to open the Analysis Services Database in Visual Studio cubes and watch the Key Performance Indicators creating the problem present in the target cube. Check out the used Dimensions of the KPI Value query, and examine the tables used in those Dimensions. This error is majorly due to the failed population of the Dimensions from the Dynamics AX database. When you process the Dimension the results window shows the SQL Query which is being run to populate these dimensions. Run these query in SQL Server Management Studio and check if it returns any results. If no records are fetched it means there is no data in the target table. Ensure that the table is filled appropriately, and reprocess the Cube. This will possibly solve the issue.