Sql 2005 Integration

OpenI connects to OLAP servers via XML for Analysis interface. OpenI has been tested with Microsoft Analysis Services 2000 containing MSAS service pack 3 via MS XMLA 1.1.

[edit]

Requirement

As OpenI interacts with Analysis Services via JPivot's XMLA part and XML/A, need to investigate changes made to XML/A implementation of SQL Server Analysis Services 2005 and its compatibility with JPivot XMLA part. Therefore need to investigate working of OpenI with:

Microsoft Analysis Services 2000 with latest service pack (i.e. service pack 4)
SQL Server Analysis Services 2005

[edit]

Iterations

Iteration 1
- investigate on the issue and possible fix
Iteration 2
- Fix the issue

[edit]

Resources

Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 (http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx)
XML for Analysis Specification (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnxmlspec/html/xmlanalysis.asp)
Experiences in the use of Microsoft's XML for Analysis (http://www.activeinterface.com/thinolap.html)

[edit]

Iteration 1 (Investigation)

[edit]

MSAS 2000 with service pack 4

OpenI doesn't work with MSAS 2000 containing service pack 4. All other functionalities work fine but navigator throws exception while buidling hierarchies.
But sample that comes with XMLA works fine with MSAS 2000 containing service pack 3 and 4.
This requires fix in JPivot's XMLA part and fixing for MSAS 2005 will fix this problem as well.

[edit]

SQL Server Analysis Services 2005

OpenI doesn't work with SSAS 2005.
.NET sample that comes with MS XMLA SDK doesn't work with SSAS 2005.

[edit]

Findings

On the basis of testing of some JPivot SOAP requests with SSAS 2005 XMLA:

DISCOVER_DATASOURCE SOAP request of SSAS 2005 XMLA returns empty DataSourceInfo element which is used by JPivot to idendentify XMLA datasource. However DataSourceName element is not empty in this case. Therefore JPivot must use DataSourceName to identify XMLA datasources.

DISCOVER_DATASOURCE SOAP request result of SSAS 2005 XMLA (DataSourceInfo is empty in the result, so JPivot doesn't work with SSAS 2005):

 <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
 <xsd:element name="root">
 <xsd:complexType>
 <xsd:sequence minOccurs="0" maxOccurs="unbounded">
 <xsd:element name="row" type="row" /> 
 </xsd:sequence>
 </xsd:complexType>
 </xsd:element>
 <xsd:simpleType name="uuid">
 <xsd:restriction base="xsd:string">
 <xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" /> 
 </xsd:restriction>
 </xsd:simpleType>
 <xsd:complexType name="xmlDocument">
 <xsd:sequence>
 <xsd:any /> 
 </xsd:sequence>
 </xsd:complexType>
 <xsd:complexType name="row">
 <xsd:sequence>
 <xsd:element sql:field="DataSourceName" name="DataSourceName" type="xsd:string" /> 
 <xsd:element sql:field="DataSourceDescription" name="DataSourceDescription" type="xsd:string" minOccurs="0" /> 
 <xsd:element sql:field="URL" name="URL" type="xsd:string" minOccurs="0" /> 
 <xsd:element sql:field="DataSourceInfo" name="DataSourceInfo" type="xsd:string" minOccurs="0" /> 
 <xsd:element sql:field="ProviderName" name="ProviderName" type="xsd:string" /> 
 <xsd:element sql:field="ProviderType" name="ProviderType" type="xsd:string" minOccurs="0" maxOccurs="unbounded" /> 
 <xsd:element sql:field="AuthenticationMode" name="AuthenticationMode" type="xsd:string" minOccurs="0" /> 
 </xsd:sequence>
 </xsd:complexType>
 </xsd:schema>
 <row>
 <DataSourceName>STAGE</DataSourceName> 
 <DataSourceDescription /> 
 <URL /> 
 <DataSourceInfo /> 
 <ProviderName>Microsoft Analysis Services</ProviderName> 
 <ProviderType>MDP</ProviderType> 
 <ProviderType>TDP</ProviderType> 
 <ProviderType>DMP</ProviderType> 
 <AuthenticationMode>Authenticated</AuthenticationMode> 
 </row>
 </root>

DISCOVER_DATASOURCE SOAP request result of MS XMLA 1.1 (DataSourceInfo is not empty in the result, hence JPivot works well with MSAS 2000):

  <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:EX="urn:schemas-microsoft-com:xml-analysis:exception">
 <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
 <xsd:element name="root">
 <xsd:complexType>
 <xsd:sequence>
 <xsd:element name="row" type="row" minOccurs="0" maxOccurs="unbounded" /> 
 </xsd:sequence>
 </xsd:complexType>
 </xsd:element>
 <xsd:simpleType name="uuid">
 <xsd:restriction base="xsd:string">
 <xsd:pattern value="[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}" /> 
 </xsd:restriction>
 </xsd:simpleType>
 <xsd:complexType name="row">
 <xsd:sequence>
 <xsd:element name="DataSourceName" type="xsd:string" sql:field="DataSourceName" minOccurs="0" /> 
 <xsd:element name="DataSourceDescription" type="xsd:string" sql:field="DataSourceDescription" minOccurs="0" /> 
 <xsd:element name="URL" type="xsd:string" sql:field="URL" minOccurs="0" /> 
 <xsd:element name="DataSourceInfo" type="xsd:string" sql:field="DataSourceInfo" minOccurs="0" /> 
 <xsd:element name="ProviderName" type="xsd:string" sql:field="ProviderName" minOccurs="0" /> 
 <xsd:element name="ProviderType" type="xsd:string" sql:field="ProviderType" minOccurs="0" maxOccurs="unbounded" /> 
 <xsd:element name="AuthenticationMode" type="xsd:string" sql:field="AuthenticationMode" minOccurs="0" /> 
 </xsd:sequence>
 </xsd:complexType>
 </xsd:schema>
 <row>
 <DataSourceName>Local Analysis Server</DataSourceName> 
 <DataSourceDescription>Microsoft Analysis Server 2000 on local machine</DataSourceDescription> 
 <URL>http://localhost/xmla/msxisapi.dll</URL> 
 <DataSourceInfo>Local Analysis Server</DataSourceInfo> 
 <ProviderName>Microsoft XML for Analysis</ProviderName> 
 <ProviderType>TDP</ProviderType> 
 <ProviderType>MDP</ProviderType> 
 <ProviderType>DMP</ProviderType> 
 <AuthenticationMode>Unauthenticated</AuthenticationMode> 
 </row>
 </root>

.NET sample that comes with MS XMLA SDK also uses DataSourceInfo element to idendentify XMLA datasource and because of this, the sample also doesn't work with SSAS 2005. Using DataSourceName element to identity XMLA datasource worked the sample with SSAS 2005.
SSAS 2005 XMLA gives errors for some JPivot XMLA SOAP requests. E.g. in DISCOVER_DATASOURCE, We found that in SOAP Envelope created by JPivot there are few empty xmlns attributes which is executed well by MS XMLA 1.1 and Mondrian, but SSAS 2005 XMLA gives an error. Removing the empty xmlns attribute works with SSAS 2005 XMLA.
Discover Datasource request created by the MS XMLA 1.1 SDK sample is :

  <SOAP-ENV:Envelope
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SOAP-ENV:Body>
  <Discover xmlns="urn:schemas-microsoft-com:xml-analysis"    SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <RequestType>DISCOVER_DATASOURCES</RequestType>
  <Restrictions>
  <RestrictionList></RestrictionList>
  </Restrictions>
  <Properties>
  <PropertyList>
  <DataSourceInfo>Local Analysis Server</DataSourceInfo>
  <Catalog>Foodmart 2000</Catalog>
  <Format>Tabular</Format>
  <Content>SchemaData</Content>
  </PropertyList>
  </Properties>
  </Discover>
  </SOAP-ENV:Body>  
  </SOAP-ENV:Envelope>

Discover Datasource request created by the JPivot is :

  <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header></soap-env:Header>
  <soap-env:Body>
  <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType xmlns="">DISCOVER_DATASOURCES</RequestType>
  <Restrictions>
  <RestrictionList></RestrictionList>
  </Restrictions>
  <Properties>
  <PropertyList>
  <Content>Data</Content>
  </PropertyList>
  </Properties>
  </Discover>
  </soap-env:Body>
  </soap-env:Envelope>}}}

[edit]

Iteration 2

[edit]

JPivot fix

To remove empty xmlns attributes in SOAP request:
- In XMLA_SOAP.discover method (XMLA_SOAP.java), xmlns attribute is added:

Name nPara = envelope.createName("RequestType", "", XMLA_URI);

- In XMLA_SOAP.executeQuery method, xmlns attribute is added:

Name nCom = envelope.createName("Command", "", XMLA_URI);
Name nSta = envelope.createName("Statement", "", XMLA_URI);

As JPivot uses DataSourceInfo element of DISCOVER_DATASOURCE SOAP request result to identify xmla datasource and this element is empty in SSAS 2005, No DataSourceInfo from Discover Datasource exception is thrown. To fix this:
- In XMLA_SOAP.setProviderAndDataSource method XMLA_SOAP.java), DataSourceInfo is replaced with DataSourceName.

dataSource = (String) resMap.get("DataSourceName");

[edit]

The above three set of fixes were applied to XMLA_SOAP.java

- In XMLA_SOAP.determineProvider method, DataSourceInfo is replaced with DataSourceName.

String dsInfo = (String) dsMap.get("DataSourceName");

handleHierInfo method of XMLA_Result class puts hierarchies within square bracket ([]). SSAS 2005 results hierarchies within square bracket but MSAS 2000 doesn't include square bracket in hierarchies. Therefore OLAP Navigator throws exception with SSAS 2005. To fix this issue:
- In handleHierInfo method of XMLA_Result class (XMLA_Result.java), square bracket in hierarchies is added if it is missiing:

XMLA_Hierarchy hier;
if(hierName.indexOf("[") > -1 && hierName.indexOf("]") > -1){
   hier = ((XMLA_Model) model).lookupHierByUName( hierName );          
}else{
   hier = ((XMLA_Model) model).lookupHierByUName("[" + hierName + "]" );
}

[edit]

The above fix was applied to XMLA_Result.java

[edit]

The following JPivot fixes were applied to XMLA_SOAP.java and XMLA_Result.java:

- In XMLA_SOAP.discover method (XMLA_SOAP.java), xmlns attribute is added:

Name nPara = envelope.createName("RequestType", "", XMLA_URI);

- In XMLA_SOAP.executeQuery method, xmlns attribute is added:

Name nCom = envelope.createName("Command", "", XMLA_URI);
Name nSta = envelope.createName("Statement", "", XMLA_URI);

- In XMLA_SOAP.setProviderAndDataSource method XMLA_SOAP.java), DataSourceInfo is replaced with DataSourceName.

dataSource = (String) resMap.get("DataSourceName");

- In handleHierInfo method of XMLA_Result class (XMLA_Result.java), square bracket in hierarchies is added :

XMLA_Hierarchy hier;
if(hierName.indexOf("[") > -1 && hierName.indexOf("]") > -1){
   hier = ((XMLA_Model) model).lookupHierByUName( hierName );          
}else{
   hier = ((XMLA_Model) model).lookupHierByUName("[" + hierName + "]" );
}

There is problem compiling above files with JPIVOT HEAD.

[edit]

OpenI fix

In DatasourceFormController.getXmlaDatasourceList method, replace DataSourceInfo with DataSourceName:

String dsName = (String) ((Map) iterator.next()).get("DataSourceName");

[edit]

Test

Above fixes are tested on MSAS 2000, MSAS 2000 with service pack 4, SSAS 2005 and Mondrian.

[edit]

Configuration

[edit]

Sql 2005 Installation

Ensure IIS is installed
Install SQL server 2005
- hey forgot some things:
- howto install foodmart rdbms?
- howto install foodmart cubes?
Copy the contents of the %SQL 2005 Installation folder%\OLAP\bin\isapi directory into the folder you would like to become the base for the virtual directory in IIS.(e.g. C:\inetpub\wwwroot\olap)
Create an application pool:
- Goto IIS management console
- Right-click the Application pools to open the shortcut menu and select New, then Application Pool.
- Name the application pool. e.g. OLAP
Create a virtual directory
- Goto IIS management console
  - Right-click the Web site to open the shortcut menu and select New, then Virtual directory.
  - Name the virtual directory.(e.g. OLAP). The Content Directory should point to the folder you’ve just created. (e.g. C:\inetpub\wwroot\olap)
  - Make sure that under the Access Permissions, only the second check box, Run Scripts (Such as ASP) is selected
Set up virtual directory properties
- Right-click your virtual directory node and select Properties from the menu.
- In virtual directory tab:
  - Choose the application pool as one you’ve just created.
  - Click the Configuration button. It will show 'Application Configuration' Dialog
  - Click on add button
  - In the Executable option, enter the full path name to msmdpump.dll. (e.g. C:\wwroot\olap\msmdpump.dll)
  - Enter .dll in the Extension text box.
  - Click the OK button to accept the settings
- In 'Directory Security' tab:
  - Click to Edit on Authentication and access control. It will show 'Authentication Method' dialog box
  - Enable 'Anonymous Access' or you can set up the security as per your requirement
Set up Web Service Extension
- Goto IIS management console, right-click the Web Service Extensions node and select Add new Web Service Extension.
- Name the extension.(e.g. olap)
- Click the Add button and provide a path to your msmdpump.dll file
- Check the Set extension status to Allowed check box.
Now XMLA IIS instance for SQL 2005 has been configured. In this example, the instance url is something like 'http://yourserver/olap/msmdpump.dll'. You should see a SOAP response like this (use firefox, IE will serve up it's own error page):

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
  <soap:Fault xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <faultcode>XMLAnalysisError.0xc10e0002</faultcode>
  <faultstring>Parser: The syntax for 'GET' is incorrect.</faultstring>
  <detail>
   <Error ErrorCode="3238920194" Description="Parser: The syntax for 'GET' is incorrect." Source="Unknown" HelpFile=""/>
  </detail>
  </soap:Fault>
 </soap:Body>
</soap:Envelope>

[edit]

OpenI

XMLA Datasource format for SQL 2005 depends on the vritual directory that you have created for msmdpump.dll. Generally as given in above example, it is something like http://yourserver:port/olap/msmdpump.dll.
to ensure compatibility, make sure to enable sql 2005 compatibility in OpenI:
- login
- select any project
- Under Administration -> Manage Application
- check the SQL2005 compatibility box

[edit]

Discussions

OpenI forum
- http://sourceforge.net/forum/forum.php?thread_id=1331000&forum_id=478298
JPivot forum
- http://sourceforge.net/forum/forum.php?thread_id=1394806&forum_id=197390

[edit]

Troubleshooting

[edit]

in IE, navigate to http://hostname/olap/msmdpump.dll, get nothing

IE does not serve up the page properly, try using firefox, telnet or some other client that will show the true SOAP response

[edit]

navigate to http://hostname/olap/msmdpump.dll, get 500, internal server error

Make sure that application pool user (for me NETWORK SERVICES) has read/execute/list permissions on msmdpump.dll

[edit]

IIS Anonymous User

Need to make sure your anonymous user, has access to the olap cubes. I've only done this using admin, but of course there must be a better way. If anyone has more info on how to secure this properly, please let me know.

[edit]

Bogus Chunk Size

Such a big deal, that this needs its own page: Sql_2005_Bogus_Chunk_Size

[edit]

DataSourceInfo property was missing or not correctly specified

error message:

Soap Fault code=XMLAnalysisError.88BA0500 fault string=Unable to process the request, because the DataSourceInfo property 
was missing or not correctly specified. fault actor=XML for Analysis Provider detail:

Stack Trace:

com.tonbeller.jpivot.olap.model.OlapException: Soap Fault code=XMLAnalysisError.88BA0500 fault string=Unable to process the request, because the 
DataSourceInfo property was missing or not correctly specified. fault actor=XML for Analysis Provider detail: at 
com.tonbeller.jpivot.xmla.XMLA_SOAP.errorCheck(XMLA_SOAP.java:1449) at com.tonbeller.jpivot.xmla.XMLA_SOAP.discover(XMLA_SOAP.java:1398) at

...

Solution install sql service pack 4 available here: http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en#filelist

sql server service pack 4 (http://www.microsoft.com/downloads/info.aspx?na=46&p=8&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f1%2fb%2fd%2f1bdf5b78-584e-4de0-b36f-c44e06b0d2a3%2fSQL2000-KB884525-SP4-x86-ENU.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2ftechnet%2fprodtechnol%2fsql%2f2000%2fdownloads%2fdefault.mspx)
Analysis Services service pack 4 (http://www.microsoft.com/downloads/info.aspx?na=46&p=5&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f1%2fb%2fd%2f1bdf5b78-584e-4de0-b36f-c44e06b0d2a3%2fSQL2000.AS-KB884525-SP4-x86-ENU.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2ftechnet%2fprodtechnol%2fsql%2f2000%2fdownloads%2fdefault.mspx)

[edit]

Keep getting 403 error on dashboard

When running IIS on XP, there is a default incoming connection limit, when max'd, IIS serves up 403's.

info:
- http://geekswithblogs.net/ewright/archive/2003/10/26/254.aspx
- http://dotavery.com/blog/archive/2004/01/30/385.aspx

The fix:

download MetaEdit (http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B301386)
goto LM/W3WSVC/MaxConnections
modify up from 10, anything < 40 (have not tried 40 yet).

[edit]

Test Cube setup

Adventure works: http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

Retrieved from "http://wiki.openi.org/index.php/Sql_2005_Integration"

Sql 2005 Integration

From OpenI Wiki

Description

Requirement

Iterations

Resources

Iteration 1 (Investigation)

MSAS 2000 with service pack 4

SQL Server Analysis Services 2005

Findings

Iteration 2

JPivot fix

The above three set of fixes were applied to XMLA_SOAP.java

The above fix was applied to XMLA_Result.java

The following JPivot fixes were applied to XMLA_SOAP.java and XMLA_Result.java:

OpenI fix

Test

Configuration

Sql 2005 Installation

OpenI

Discussions

Troubleshooting

in IE, navigate to http://hostname/olap/msmdpump.dll, get nothing

navigate to http://hostname/olap/msmdpump.dll, get 500, internal server error

IIS Anonymous User

Bogus Chunk Size

DataSourceInfo property was missing or not correctly specified

Keep getting 403 error on dashboard

Test Cube setup

Views

Personal tools

Navigation

Search

Toolbox