11.5 OLAP Query to SSAS 2005

[Former Member]

I have successfully connected and queried data from a SQL Server Analysis Services 2005 database. My problem is that all data of DOUBLE or DECIMAL data type does not show the proper precision.

From the log, I can see that SSAS 2005 is returning (for example)

<Value xsi:type="xsd:double">3.396E4</Value>

referring to 33,960. The Lighthammer OLAP driver, however, does not appear to be recognizing the "E" and simply returns 3.396.

Does anyone know a workaround for this issue? Is the OLAP driver supported with SSAS 2005? Any help is appreciated.

Dave

[salvatore_castro]

This is probably not MII but rather the dll you are using for communication. Please post your dataserver configuration.

Sam

[Former Member]

My OLAP connector configuration is as follows:

Catalog	<CUBE DATABASE>
ColumnNameAttribute	sql:field
Connector	OLAP
ConnectorType	OLP
DataSourceInfo	Provider=Local Analysis Server
DataTypeAttribute	type
DecimalDelimiter	.
Description	<CUBE DATABASE DESCRIPTION>
Enabled	true
IP	localhost
InternalDateFormat	yyyy-MM-dd HH:mm:ss
Name	myOLAPConnection
Port	80
ResultInNamespace	true
ServerPackage	com.lighthammer.Illuminator.connectors.OLAP
UserName	 
WebService	/olap/msmdpump.dll

I also have an open thread on a similar topic: .

I can connect to the data source, just the format of the data returned is incorrect:

u2022 float data returns with incorrect precision (2,543 returns as 2.543)

u2022 default data type, if none specified in the return XMLA document, is a float instead of a string

u2022 CubeList, DimensionList fail if no description exists

I agree that the DLL may be the issue (the DLL used for SSAS 2005 is different than for 2000). I can view the response document from the MSMDPUMP.DLL through the log and I believe that it is returning data as expected, which leads me to believe that the 11.5 OLAP connector may not be compatible with SSAS 2005.

Is SSAS 2005 compatible with ver. 12? Are there any workarounds?

Thanks again in advance.

[salvatore_castro]

David,

I know that in the SAP BI OLAP communication the user's profile affects the formatting of the data returned. Maybe it's a similar issue with MSSQL AS. Take a look at the user configuration parameters on the MS side to see if there's formatting configuration there which can be modified. In the mean time enter this into the SAP CSS system. Thanks.

Sam

[Former Member]

Thanks Sam,

From what I can determine, SSAS does allow you to modify the format of the response. It tacks on a <fmtvalue> tag that displays float data in the non-exponent format. The only problem is that it appears that the OLAP driver only reads the <value> tag that remains in the "#E# format.

Thanks again,

Dave

[salvatore_castro]

David,

Thanks for the insight, I am trying to replicate your issue but am not familiar with SSAS 2005. So I have a basic question for you...

How did you determine the port for the XMLA call?

Sam

[Former Member]

Hi Sam,

I basically followed the procedure [from Microsoft|http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx]. I used port 80 to connect to the msmdpump.dll.

In IIS, I also set directory security on \olap: Annonymous Access user to a local admin (or a user must be able to access SQL data). There are probably more sophisticated ways, but this worked for me.

I tested it by querying http:
...\olap\ msmdpump.dll in a web browser (not IE though) to and got an XML response with an error (expected), which confirms that the msmdpump.dll is setup correctly.

Thanks,

Dave