Geeks With Blogs
Satya Srikant Mantha Reflecting DAX NET and SQL Server

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 :

  1. What is that the installation does?
  2. What are the impact areas of the Analysis Extensions setup?
  3. 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:

<ConnectionString>Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DynamicsAx1</ConnectionString>

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.

image

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 <parameter name>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.

Posted on Tuesday, February 17, 2009 4:04 PM Enterprise Portal Administration | Back to top


Comments on this post: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hi,

First of all, thank you for a very interesting article.
I wonder if you could help us.
We have a fresh DAX 2009 installation + SQL 2008 (SS + RS + AS).
We have managed to deploy the ODC (Reporting and Role centers with BI works fine), however whenever we try to Edit the Reports (either the whole library or a single report) in Visual Studio (2008) we get an error:

'Exception from HRESULT: 0x80041FE1'

and the project is not loaded?

Have you faced this issue?
Do you have any sugesstions?

Regards,
Sebastian
Left by Sebastian Widz on May 05, 2009 12:23 AM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hi Sabastian,

This post was a workaround for a problem in which the Analysis Services Extensions was not getting installed on the System properly, I was then working with SQL Server 2005 Analysis Services after which everything else seemed to be working fine even the editing of the reports.

However Now we have moved to SQL Server 2008 and RS and AS, which installed smoothly without this workaround. As for the issue I have not encountered the issue while editing one of the reporting projects I created and it looks fine to me.

I hope the Reporting Tools are correctly installed on your system? If not then this can be an Issue!!!

Regards,
Satya Srikant Mantha
Left by Satya Srikant Mantha on May 09, 2009 5:12 PM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hi Satya,

Glad i found yuor blog to create the database by running the script. You point to changing the report library in shared library.

"Right click on the Shared Library Report Library and click Edit in Visual Studio"

Exactly where is this. what i have to view in visual studio and change. Can you clear it a little for me.
Thanks.

regards,
Manpreet
Left by Manpreet Singh on Dec 24, 2009 10:31 PM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hi,
Thanks for the location. But when i edit it in vs i get this error.

Error executing code: SysReportLibraryExport (object) has no valid runable code in method 'new'.

(C)\Classes\SysReportLibraryExport\new

Till now i've just made ax 2009 setup + sp1. Ran checklist and now installed analysis database.
Left by Manpreet on Dec 25, 2009 4:19 PM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hi Satya,

What would be location of Shared Library. Same question which manpreet has asked.

Thanks,
Mohit
Left by Mohit on Feb 18, 2010 12:45 PM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hi,
great post!
But one point does not work in my AX environment: After executing the script, i try to setup the OLAP parameters in Dynamics but AX does not find any analysis databases. I tried it on SQLServer 2005, 2008 (named and default instance name) and it only works for SQL Server 2005. Do you have any idea what could the problem? I cannot debug the code in AX which fills the tmp table for the analysis databases in the form BIOlapAdministration, method CheckBoxSelect:modified()

Thanks,
Stefan
Left by Stefan Schiffer on Apr 08, 2010 8:47 PM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hi Stefan,
may be its a problem with the Windows Firewall.
Port 2383

Tahnks,
Dieter
Left by Dieter Higi on Jun 10, 2010 12:19 AM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
the error saying it need analysis services 2005 sp1 or sp2 is due to had sql express 2005 instaled in the server, usually this happens if you instal a full visual studio suit.
Left by Ignacium on May 06, 2011 8:45 AM

# re: MS Dynamics 2009 Analysis Extension Problem on SQL Server 2008 - A WorkAround
Requesting Gravatar...
Hello

im having some troubles with this workaround:
first when i execute the script in SQL Management Studio i get some errors but it creates the database
<Warning WarningCode="2165374978" Description="Fehler im Back-End-Datenbankzugriffsmodul. Der für eine Bindung angegebene Größenwert war zu niedrig, daher wurde mindestens ein Spaltenwert abgeschnitten." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Name-Attributs der Customers - Business relations-Dimension aus der Dynamics AX-Datenbank." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034310" Description="Fehler im OLAP-Speichermodul: Der Verarbeitungsvorgang wurde beendet, da die Anzahl von Fehlern, die während der Verarbeitung ermittelt wurden, die für den Vorgang maximal zulässige Anzahl von Fehlern erreicht hat." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3239837698" Description="Server: Der Vorgang wurde abgebrochen." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3238395904" Description="OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Operation canceled; HY008." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034316" Description="Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des dimension-Objekts mit der ID 'CUSTBUSREL' und dem Namen 'Customers - Business relations'." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Subsegment description-Attributs der Customers - Business relations-Dimension aus der Dynamics AX-Datenbank." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3238395904" Description="OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Operation canceled; HY008." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034316" Description="Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des dimension-Objekts mit der ID 'CUSTBUSREL' und dem Namen 'Customers - Business relations'." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Segment-Attributs der Customers - Business relations-Dimension aus der Dynamics AX-Datenbank." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3238395904" Description="OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Operation canceled; HY008." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034316" Description="Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des dimension-Objekts mit der ID 'CUSTBUSREL' und dem Namen 'Customers - Business relations'." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Sales person-Attributs der Customers - Business relations-Dimension aus der Dynamics AX-Datenbank." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />
<Error ErrorCode="3238002695" Description="Interner Fehler: Der Vorgang wurde erfolglos beendet." Source="Microsoft SQL Server 2008 R2 Analysis Services" HelpFile="" />

second when i try to synchronisze the OLAP database - it doest not start, there is no error message - it just dont start

can anybody help me?
Left by Stefan on Oct 13, 2011 9:28 PM

Your comment:
 (will show your gravatar)


Copyright © ssmantha | Powered by: GeeksWithBlogs.net