ReportServer Forum

FLU73

Member

Registered: 2018-10-25

Saiku report : no cube, error with simple Mondrian schema and postgre

Hello,

As I did not succeed to connect to Sybase, I've exported some tables (csv files) to put them in a Postgre database.

Then I created my mondrian datasource like this :

Name : Sales_orders
user name: "my username"
password: "my password"
URL: jdbc:mondrian:Jdbc=jdbc:postgresql://<my server name>:<port>/<DB name>

properties :
type=OLAP
name=Schema_DEV_POSTGRE_2
driver=mondrian.olap4j.MondrianOlap4jDriver
jdbcDrivers=org.postgresql.Driver

Schema :
<?xml version="1.0"?>
<Schema name="Schema_DEV_POSTGRE_2" description="Schema 2 SAP DEV sur base Postgre">
    <Cube name="Sales_Orders_Cube_2" visible="true" description="Postes Commandes et articles" cache="true" enabled="true">
        <Table name="VBAP" schema="public" alias="Sales_facts_table">
        </Table>
        <Dimension type="StandardDimension" visible="true" foreignKey="MATNR" highCardinality="false" name="Products" description="Produits">
            <Hierarchy name="Products" visible="true" hasAll="true" description="Hierarchie de produits">
                <Table name="MARA" schema="public">
                </Table>
                <Level name="Prod_type" visible="true" table="MARA" column="MTART" nameColumn="MTART" type="String" uniqueMembers="false" description="Type article">
                </Level>
            </Hierarchy>
        </Dimension>
        <Measure name="Net_value" column="NETWR" datatype="Numeric" aggregator="sum" description="Montant HT" visible="true">
        </Measure>
    </Cube>
</Schema>

Xml file of this schema has been generated with pentaho - workbench, (there is no red cross closed schema attributes, so I guess the xml is OK)

But when selecting my Saiku report, I can not select a cube, I get the following error :

net.datenwerke.gxtdto.client.servercommunication.exceptions.ServerCallFailedException
<br>    at net.datenwerke.rs.saiku.server.rest.SaikuRpcServiceImpl.loadCubesFor(SaikuRpcServiceImpl.java:103)
<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
<br>    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
<br>    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
<br>    at java.lang.reflect.Method.invoke(Unknown Source)
<br>    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
<br>    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
<br>    at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
<br>    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
<br>    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>    at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
<br>    at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
<br>    at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
<br>    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
<br>    at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89)
<br>    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
<br>    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>    at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
<br>    at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
<br>    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
<br>    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
<br>    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
<br>    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
<br>    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
<br>    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
<br>    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
<br>    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
<br>    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
<br>    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
<br>    at org.apache.coyote.ajp.AbstractAjpProcessor.process(AbstractAjpProcessor.java:870)
<br>    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
<br>    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2527)
<br>    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2516)
<br>    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
<br>    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
<br>    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
<br>    at java.lang.Thread.run(Unknown Source)
<br>Caused by: java.lang.NullPointerException
<br>    at mondrian.rolap.RolapSchema$PhysLink.deriveSql(RolapSchema.java:2046)
<br>    at mondrian.rolap.RolapSchema$PhysLink.&lt;init&gt;(RolapSchema.java:2004)
<br>    at mondrian.rolap.RolapSchema$PhysSchema.addLink(RolapSchema.java:945)
<br>    at mondrian.rolap.RolapSchemaUpgrader.registerRelation(RolapSchemaUpgrader.java:3013)
<br>    at mondrian.rolap.RolapSchemaUpgrader.convertHierarchy(RolapSchemaUpgrader.java:2800)
<br>    at mondrian.rolap.RolapSchemaUpgrader.convertDimension(RolapSchemaUpgrader.java:2707)
<br>    at mondrian.rolap.RolapSchemaUpgrader.convertCubeDimension(RolapSchemaUpgrader.java:735)
<br>    at mondrian.rolap.RolapSchemaUpgrader.convertCube(RolapSchemaUpgrader.java:201)
<br>    at mondrian.rolap.RolapSchemaUpgrader.convertSchema(RolapSchemaUpgrader.java:2208)
<br>    at mondrian.rolap.RolapSchemaUpgrader.upgrade(RolapSchemaUpgrader.java:125)
<br>    at mondrian.rolap.RolapSchemaLoader.loadStage0(RolapSchemaLoader.java:254)
<br>    at mondrian.rolap.RolapSchemaLoader.createSchema(RolapSchemaLoader.java:4303)
<br>    at mondrian.rolap.RolapSchemaPool.get(RolapSchemaPool.java:210)
<br>    at mondrian.rolap.RolapSchemaPool.get(RolapSchemaPool.java:62)
<br>    at mondrian.rolap.RolapConnection.&lt;init&gt;(RolapConnection.java:160)
<br>    at mondrian.rolap.RolapConnection.&lt;init&gt;(RolapConnection.java:84)
<br>    at mondrian.olap.DriverManager.getConnection(DriverManager.java:112)
<br>    at mondrian.olap.DriverManager.getConnection(DriverManager.java:68)
<br>    at mondrian.olap4j.MondrianOlap4jConnection.&lt;init&gt;(MondrianOlap4jConnection.java:153)
<br>    at mondrian.olap4j.FactoryJdbc4Plus$AbstractConnection.&lt;init&gt;(FactoryJdbc4Plus.java:323)
<br>    at mondrian.olap4j.FactoryJdbc41Impl$MondrianOlap4jConnectionJdbc41.&lt;init&gt;(FactoryJdbc41Impl.java:118)
<br>    at mondrian.olap4j.FactoryJdbc41Impl.newConnection(FactoryJdbc41Impl.java:32)
<br>    at mondrian.olap4j.MondrianOlap4jDriver.connect(MondrianOlap4jDriver.java:139)
<br>    at java.sql.DriverManager.getConnection(Unknown Source)
<br>    at java.sql.DriverManager.getConnection(Unknown Source)
<br>    at net.datenwerke.rs.saiku.service.saiku.OlapUtilServiceImpl.getOlapConnection(OlapUtilServiceImpl.java:189)
<br>    at net.datenwerke.rs.saiku.service.saiku.OlapUtilServiceImpl.getCubes(OlapUtilServiceImpl.java:106)
<br>    at net.datenwerke.rs.saiku.server.rest.SaikuRpcServiceImpl.loadCubesFor(SaikuRpcServiceImpl.java:100)
<br>    ... 50 more
<br>

If somebody can help .... thanks a lot

FLU73

Member

Registered: 2018-10-25

Re: Saiku report : no cube, error with simple Mondrian schema and postgre

Hello,
I finally succeeded to debug my schema.

meta model version need to be specified after the schema name :
<Schema name="Schema_DEV_POSTGRE_2" metamodelVersion="4.0">
instead of <Schema name="Schema_DEV_POSTGRE_2">

For someone else that would face with the same problem and is a newbie like me : if you have generated the schema from pentaho workbench and copied the xml code into report server, it will not work.

The xml need to be rework, I would say to be compliant with metamodelVersion="4.0".
Then you need to add :
at first the "PhysicalSchema" description :
    <PhysicalSchema>
        <Table name='your_table'>
            <Key>
                <Column name='your_column_name'/>
            </Key>
        </Table>
       .........
    </PhysicalSchema>

before the cube description :
    <Cube name='your_cube'>
        <Dimensions>
           <Dimension name='your_dimension' table='you_table' key='your_key'>
                <Attributes>
                    <Attribute name='your_attribute' keyColumn='your_column'/>
                </Attributes>
            </Dimension>
        </Dimensions>

"MeasureGroups" is mandatory and need to contain at least one "Measure group":
        <MeasureGroups>
            <MeasureGroup name='your_measure_group' table='your_fact_table'>
                <Measures>
                    <Measure name='your_measure' column='your_column' datatype='Numeric' aggregator='sum'/>
                    </Measure>
               .........
                </Measures>
            </MeasureGroup>
        </MeasureGroups>
    </Cube>

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Saiku report : no cube, error with simple Mondrian schema and postgre

Hi FLU73,

thanks for pointing this out. As you noticed, we use Mondrian version 4, so it is necessary to use schema version = 4. We have this information in the upgrade guide: https://reportserver.net/en/tutorials/upgrade-30/
We will also update our documentation to make this more explicit and avoid confusion.

Regards,
Eduardo

FLU73

Member

Registered: 2018-10-25

Re: Saiku report : no cube, error with simple Mondrian schema and postgre

Hello,

I'am facing with another issue, I succeed to build a Saiku report with dimensions and measure, but the measure (sum) is not calculated. There is no value in the result.
I've tried with another measure (count for an integer) and it's still the same result.

When doing the same job with a dynamic list with pivot mode, all is working fine, calculations are done properly.

Here below is the detail of my xml schema :
<Schema name="Schema_DEV_SALES" metamodelVersion="4.0">
<PhysicalSchema>
<Table name="VBAP">
<Key>
<Column name="VBELN"/>
</Key>
</Table>
<Table name="MARA">
<Key>
<Column name="MATNR"/>
</Key>
</Table>
<Table name="T001W">
<Key>
<Column name="WERKS"/>
</Key>
</Table>
</PhysicalSchema>
<Cube name="SALES_Cube">
<Dimensions>
<Dimension name="Product" table="MARA" Key="MATNR">
<Attributes>
<Attribute name="Material type" table="MARA" keyColumn="MTART" hasHierarchy="false"/>
</Attributes>
<Hierarchies>
<Hierarchy name="Products" allMemberName="All Products">
<Level attribute="Material type"/>
</Hierarchy>
</Hierarchies>
</Dimension>
<Dimension name="BU" table="T001W" Key="WERKS">
<Attributes>
<Attribute name="BU Name" keyColumn="NAME1" hasHierarchy="false"/>
</Attributes>
<Hierarchies>
<Hierarchy name="BU" allMemberName="All BU">
<Level attribute="BU Name"/>
</Hierarchy>
</Hierarchies>
</Dimension>
</Dimensions>
<MeasureGroups>
<MeasureGroup name="Sales" table="VBAP">
<Measures>
<Measure name="Net_value" column="NETWR" datatype="Numeric" aggregator="sum" format="'standard"/>
<Measure name="Number of SO" column="VBELN" datatype="Integer" aggregator="distinct-count" format="standard"/>
</Measures>
<DimensionLinks>
<ForeignKeyLink dimension="Product" foreignKeyColumn="MATNR"/>
<ForeignKeyLink dimension="BU" foreignKeyColumn="WERKS"/>
</DimensionLinks>
</MeasureGroup>
</MeasureGroups>
</Cube>
</Schema>

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Saiku report : no cube, error with simple Mondrian schema and postgre

Hi FLU73,

in our tests the mondrian schemas are working, including sum measures. In fact, the pivot list you mention creates a dynamic xml schema and runs it against the mondrian library..
I think your mondrian xml schema definition has some error.. you might want to post the schema to a mondrian specialized forum.

Regards,
Eduardo