If you want to use Microsoft OLE DB provider for analysis services Driver with your Excel to access and display multidimensional data, then you need to modify Mondrian source files to make it compatible with Microsoft OLE DB provider for analysis services Driver.
Since Microsoft OLE DB provider for analysis services Driver is developed in compatible with Microsoft Analysis Services Server, Mondrian OLAP server should generate response which is in the same format as that of Microsoft Analysis Services response.
I have done all these changes to latest 3.5 source code and I have used Microsoft OLE DB provider for analysis services 10.0 driver with Microsoft Excel 2010.
Issue : When you try to connect to Mondrian through Excel you will get an error which says
"The xsd:schema element at line 9, column 312 (namespace
http://www.w3.org/2001/XMLSchema) cannot appear under
Envelope/Body/ExecuteResponse/return/root"
This error occures because of BeginSession request. To deal with this request Mondrian will generate empty XMLA response as follows.
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Header />
<SOAP-ENV:Body>
<cxmla:ExecuteResponse xmlns:cxmla="urn:schemas-microsoft-com:xml-analysis">
<cxmla:return>
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" xmlns:EX="urn:schemas- microsoft-com:xml-analysis:exception" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" elementFormDefault="qualified">
<xsd:element name="root" />
</xsd:schema>
</root>
</cxmla:return>
</cxmla:ExecuteResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
but accoring to XMLA standards specification empty XMLA response should not contain '<xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" elementFormDefault="qualified">' under Envelope/Body/ExecuteResponse/return/root element.
To fix this issue comment out entire body of the method writeEmptyDatasetXmlSchema which is in mondrian.xmla.XmlaHandler class
Issue : Internal error:An unexpected error occured(file 'pcxmlaclient.cpp', line 566, function 'PCXMLAclient::BeginSession').
This error occures because MS OLE DB Provider for Analysis Services expects sessionId to be present in response header of beginSession request. But, MS OLE DB Provider for Analysis Services send a request to begin a session without "mustUnderstand" property in beginSession header element. Mondrian will return sessionId only if "mustUnderstand" property is set to "1" in beginSession header element. To fix this comment out the code which will check mustUnderstand value is present or not in header element in handleSoapHeader method which is in mondrian.xmla.impl.DefaultXmlaServlet class.
Issue :"Unable to obtain database list"
If we call the Discover method with the DISCOVER_SCHEMA_ROWSETS enumeration value in the RequestType element, the Discover method returns the DISCOVER_SCHEMA_ROWSETS rowset.
The DISCOVER_SCHEMA_ROWSETS rowset should contain following columns.
SchemaName, SchemaGuid, Restrictions, Description and RestrictionsMask.
(refer this:: http://technet.microsoft.com/en-us/library/ms126280.aspx)
* But Mondrian is not adding RestrictionsMask column in to DISCOVER_SCHEMA_ROWSETS columns list.
File to be modified : mondrian.xmla.RowsetDefinition.DiscoverSchemaRowsetsRowset
Changes :
- Create a new column with a name RestrictionsMask.
private static final Column RestrictionsMask =
new Column(
"RestrictionsMask",
Type.UnsignedLong,
null,
Column.NOT_RESTRICTION,
Column.OPTIONAL,
"");
- Add RestrictionsMask column to DISCOVER_SCHEMA_ROWSETS's columns list. Since we are specifying column value is optional no need to add values.
* MS OLE DB Provider for Analysis driver will throw an exception if SchemaGuid column value is not present or if it is an invalid value.
(refer this:: http://books.google.co.in/books?id=b16-TBRICPIC&pg=PA446&lpg=PA446&dq=discover_schema_rowset+guid+value&source=bl&ots=VBaCGHPi4-&sig=mHCVzF1uuAWRcvFJ8bi63i0RFdU&hl=en&sa=X&ei=ySlQUuvIKsWLrQex5IHADQ#v=onepage&q=discover_schema_rowset%20guid%20value&f=false)
But Mondrian is not setting values for this column.
File to be modified : mondrian.xmla.RowsetDefinition
Changes : Add SchemaGuid values to each schema rowset. Set below specified SchemaGuid values.
DISCOVER_DATASOURCES - 06C03D41-F66D-49F3-B1B8-987F7AF4CF18
DISCOVER_SCHEMA_ROWSETS - EEA0302B-7922-4992-8991-0E605D0E5593
DISCOVER_ENUMERATORS - 55A9E78B-ACCB-45B4-95A6-94C5065617A7
DISCOVER_PROPERTIES - 4B40ADFB-8B09-4758-97BB-636E8AE97BCF
DISCOVER_KEYWORDS - 1426C443-4CDD-4A40-8F45-572FAB9BBAA1
DISCOVER_LITERALS - C3EF5ECB-0A07-4665-A140-B075722DBDC2
DBSCHEMA_CATALOGS - C8B52211-5CF3-11CE-ADE5-00AA0044773D
DBSCHEMA_COLUMNS - C8B52214-5CF3-11CE-ADE5-00AA0044773D
DBSCHEMA_PROVIDER_TYPES - C8B5222C-5CF3-11CE-ADE5-00AA0044773D
DBSCHEMA_TABLES - C8B52229-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_ACTIONS - A07CCD08-8148-11D0-87BB-00C04FC33942
MDSCHEMA_CUBES - C8B522D8-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_DIMENSIONS - C8B522D9-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_FUNCTIONS - A07CCD07-8148-11D0-87BB-00C04FC33942
MDSCHEMA_HIERARCHIES - C8B522DA-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_LEVELS - C8B522DB-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_MEASURES - C8B522DC-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_MEMBERS - C8B522DE-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_PROPERTIES - C8B522DD-5CF3-11CE-ADE5-00AA0044773D
MDSCHEMA_SETS - A07CCD0B-8148-11D0-87BB-00C04FC33942
DBSCHEMA_TABLES_INFO - C8B522E0-5CF3-11CE-ADE5-00AA0044773D
DBSCHEMA_SCHEMATA - According to Mondrian documentation DBSCHEMA_SCHEMATA will return list of mondrian schemas. As we don't have SchemaGuid value for this request type and Microsoft OLE DB for analysis services may not call Discover
method with this request type we can comment this enum value and all related code.
Issue : If we select a particular Database(Catalog) it should be saved as session default catalog. But this is not working.
File to be modified : mondrian.xmla.RowsetDefinition.DiscoverPropertiesRowset
Method to be added :
private void setCatalogPropertyValue(OlapConnection connection) {
LOGGER.debug("setting property value");
try {
LOGGER.debug("connection"+connection);
List<Catalog> catalogs = connection.getOlapCatalogs();
LOGGER.debug("catalogs length"+catalogs.size());
if(catalogs.size() !=0 ) {
PropertyDefinition.Catalog.setValue(catalogs.get(0).getName());
}
} catch (OlapException e) {
throw new RuntimeException(
"Failed to obtain a list of catalogs form the connection object.", e);
}
}
Method to be modified : populateImpl
Change : Add this code in for loop of populateImpl method
if(propertyDefinition.name().equals(PropertyDefinition.Catalog.name()) &&
propertyDefinition.value.equals("")) {
setCatalogPropertyValue(connection);
}
Issue : If we select a datasource to import data and click ok it will give following error.
Microsoft Excel: The XA:error element at line 11, column 57 (namespace http://mondrian.sourceforge.net) cannot appear under Envelope/Body/Fault/detail.
This error says that some properties are missing in PropertyDefinition enum.
File to be modified : mondrian.xmla.PropertyDefinition enum
Changes : Add these properties to enum.
DbpropMsmdMDXCompatibility(
RowsetDefinition.Type.Integer,
null,
XmlaConstants.Access.ReadWrite,
"1",
XmlaConstants.Method.DISCOVER_AND_EXECUTE,
"DbpropMsmdMDXCompatibility" )
MdxMissingMemberMode(
RowsetDefinition.Type.String,
null,
XmlaConstants.Access.ReadWrite,
"Error",
XmlaConstants.Method.DISCOVER_AND_EXECUTE,
"DbpropMsmdSubqueries" )
SafetyOptions(
RowsetDefinition.Type.Integer,
null,
XmlaConstants.Access.ReadWrite,
"",
XmlaConstants.Method.DISCOVER_AND_EXECUTE,
"SafetyOptions" )
MdpropMdxDrillFunctions(
RowsetDefinition.Type.Integer,
null,
XmlaConstants.Access.Read,
"3",
XmlaConstants.Method.DISCOVER_AND_EXECUTE,
"A bitmask indicating support for drilldown and drillup groups of functions. " )
Issue : When ever client send a request with Execute Method which contains <Format>Native</Format> tag in its PropertiesList Mondrian will give error
saying that Currently Mondrian will support only Tabular and Multidimensional Xml format.
To fix this issue we can make changes according to this bug reported in Mondrian Issues list - http://jira.pentaho.com/browse/MONDRIAN-131 and its description.
Accoring to the description specified in the bug report we can make Multidimensional format as native format to Mondrian.
File to be modified : mondrian.xmla.XmlaHandler
Method : checkFormat
Change : comment out below code. This specifies that Mondrian will support all three formats including "Native" format.
final String formatName =
properties.get(PropertyDefinition.Format.name());
if (formatName != null) {
Format format = getFormat(request, null);
if (format != Format.Multidimensional
&& format != Format.Tabular)
{
throw new UnsupportedOperationException(
"<Format>: only 'Multidimensional', 'Tabular' "
+ "currently supported");
}
}
Method : executeQuery
Change :
Change this code
if (format == Format.Multidimensional) {
dataSet =
new MDDataSet_Multidimensional(
cellSet,
content != Content.DataIncludeDefaultSlicer,
responseMimeType
== Enumeration.ResponseMimeType.JSON);
} else {
dataSet =
new MDDataSet_Tabular(cellSet);
}
to
if (format == Format.Tabular) {
dataSet =
new MDDataSet_Tabular(cellSet);
} else {
dataSet =
new MDDataSet_Multidimensional(
cellSet,
content != Content.DataIncludeDefaultSlicer,
responseMimeType
== Enumeration.ResponseMimeType.JSON);
}
Issue : Excel will crash if it get response for Discover method with request type MDSCHEMA_PROPERTIES.
This because of incompatible response values for HierarchyUniqueName and LevelUniqueName.
HierarchyUniqueName and LevelUniqueName values format refer this http://technet.microsoft.com/en-us/library/ms146038.aspx
File to be modified : mondrian.xmla.RowSetDefinition.MdschemaPropertiesRowset
method : outputProperty()
change :
row.set(HierarchyUniqueName.name, hierarchy.getUniqueName());
row.set(LevelUniqueName.name, level.getUniqueName());
to
row.set(HierarchyUniqueName.name, dimension.getUniqueName()+"."+hierarchy.getUniqueName());
row.set(LevelUniqueName.name, dimension.getUniqueName()+"."+hierarchy.getUniqueName()+"."+level.getUniqueName());
