Mondrian OLAP - Not Optimized Querys

Question

Like all the times....my olap is Invoice based.

So, i have invoice and related two tables: dates and customers.

Invoice: customerid, dateid, measures fileds.

Customers: customerid, name_of_customer

dates: dateid, year, month, day

The problem is when de cube is open in vertical i have Dates Dimension and then i put Customers.

If i see what is happening using Sql Profiler:

select name_of_customer from customers where 'name of some customer' = customers.name_of_customer group by customers.name_of_customers.

This line is by EACH customer. So if i have 3000 customers, 3000 select are performed. name_of_customer has "uniqueMembers" checked in mondrian schema. Its like mondrian try to group a field that is unique and its slowly at that moment.

Answer 1

Hi Diego,

if I got the situation, your Mondrian schema should have a dimension like this one:

    <Dimension name="Customer">

                 <Hierarchy hasAll="true" allMemberName="All Customers" primaryKey="customerid">

<Table name="customer"/>

                          <Level name="name of customer" column="name_of_customer" uniqueMembers="true"/>                  

                      </Hierarchy>

       </Dimension>

The uniqueMembers attribute is not mandatory, as you said it is typically used to improve performances. As explained in Pentaho Mondrian documentation you have "The uniqueMembers attribute is used to optimize SQL generation. If you know that the values of a given level column in the dimension table are unique across all the other values in that column across the parent levels, then set uniqueMembers="true", otherwise, set to "false". For example, a time dimension like [Year].[Month] will have uniqueMembers="false" at the Month level, as the same month appears in different years. On the other hand, if you had a [Product Class].[Product Name] hierarchy, and you were sure that [Product Name] was unique, then you can set uniqueMembers="true". If you are not sure, then always set uniqueMembers="false". At the top level, this will always be uniqueMembers="true", as there is no parent level."

Try to set the attribute to false. 

I also suggest to check you correctly mapped  the dimension into the cube definition.:

  <Cube name="Invoice">

                    <Table name="my_invoice_fact"/>

                    <DimensionUsage name="Customer" source="Customer" foreignKey="customerid"/> 

                    <Measure name="value1 " column="value1" aggregator="sum"       formatString="Standard"/>  

   </Cube>

I hope I was of any help. I wait for any feedback.

Regards,

Nunzia

Comments

Tks for your answer, i try both options with and without unique.

The problem is the knowage engine. The samle XML mondrian in pentaho has instant results, but in knowage has a big delay to show data to the user, also in knowage has ineficient sql querys by each customer instead of a one query to resolve.