Pentaho Community Forums - Mondrian [Pentaho Analysis Services]

Problem with shared dimension names in virtual cubes

paulo.f.rezende — Wed, 22 May 2013 19:51:06 GMT

Hello guys,
I have a situation that I could not solve and would like the opinion of you.

I have the following structure of cubes / facts:

CUB_1
FACT_TABLE 1
TIME_DIM_CUB_1 (use share dimension TIME_DIM with foreign key time_id_1), name: Time_1
DIM_2
MEASURE_1

CUB_2
FACT_TABLE 2
TIME_DIM_CUB_2 (use share dimension TIME_DIM with foreign key time_id_2), name: Time_2
TIME_DIM (use share dimension TIME_DIM with foreign key time_id_3), name: Time_3
DIM_2
MEASURE_2

TIME_DIM
Name: Time
-Hierarchy TIME
- Year
- Month
- Day

Where the size TIME_DIM is a shared dimension. When I explore the cube in any analysis tool (Analyzer / JPivot) is given the name I gave the cub, eg. when I use CUB_1 name is displayed Time_1.

However, when I make a virtual cube with the following structure:

VIRTUAL_CUBE
TIME_DIM_CUB_1
TIME_DIM_CUB_2
TIME_DIM
MEASURE_1
MEASURE_2

In the analysis tool (Analyzer / JPivot) is presented as follows

Name: Time
- Year
- Month
- Day
- Year
- Month
- Day
- Year
- Month
- Day

But what I need is to be displayed in the virtual cube, the three hierarchies separately, as in:

Name: Time_1
- Year
- Month
- Day

Name: Time_2
- Year
- Month
- Day

Name: Time_3
- Year
- Month
- Day

Any suggestions?

Thanks for the help guys!

Left join in virtual cubes

paulo.f.rezende — Wed, 22 May 2013 13:51:52 GMT

Hello guys,
I have a situation that I could not solve and would like the opinion of you.

I have two cubes, as follows:

CUB_1
FACT_TABLE 1
DIM_1
DIM_2
MEASURE_1
MEASURE_2

CUB_2
FACT_TABLE 2
DIM_1
DIM_2
MEASURE_3
MEASURE_4

I built a virtual cube to integrate my two cubes, my two tables facts, and everything worked perfectly. My problem was when I include a new cub:

CUB_3
FACT_TABLE 3
DIM_1
DIM_3
MEASURE_5

When I filter by any attribute DIM_2 that exists in CUB_1 and CUB_2 are not presented metrics CUB_3, I think because there is no dim_2 in CUB_3.
I think a possible solution would be to use some type structure like LEFT JOIN, just do not know how to do this using the mondrian

Thank you in advance for help!

Aggregate doesn't use the best table when use with inside
DreamingFighter — Mon, 20 May 2013 09:37:24 GMT
Hi all, when working with Mondrian I encounter a strange problem this morning:
- I create a Time dimension that have data from 2001 to 2020: D_PRD. Config in xml file:

Code:

`<=!=[=C=D=A=T=A=[ ( D_PRD.prd_id >= 20120101 and D_PRD.prd_id < 20140101]=]=>`

- I create a fact table that have 2 dimension, one is Time dimension: F_FACT_TABLE
- I create an aggregate table that only have Time dimension at Day level: AGG_01_F_FACT_TABLE
- I create another aggregate table that only have Time dimension at Month level: AGG_02_F_FACT_TABLE

Because I add condition to the dimension configuration, Mondrian never use the second aggregate table AGG_02_F_FACT_TABLE. Below is 2 SQL for when I add condition (SQL 1) and then remove it (SQL 2)
SQL 1:

Code:

select "D_PRD"."YEAR" as "c1", "D_PRD"."MONTH" as "c2", sum("AGG_01_F_FACT_TABLE"."T_ORG_COST") as "m0", sum("AGG_01_F_FACT_TABLE"."T_PROM_COST") as "m1", sum("AGG_01_F_FACT_TABLE"."T_TOT_COST") as "m2" from "MONDRIAN_TEST"."AGG_01_F_FACT_TABLE" "AGG_01_F_FACT_TABLE", "MONDRIAN_TEST"."D_PRD" "D_PRD" where "AGG_01_F_FACT_TABLE"."PRD_ID" = "D_PRD"."PRD_ID" and (D_PRD.prd_id >= 20120101 and D_PRD.prd_id < 20140101) and "AGG_03_THANGLD_MONDRIAN"."PRD_ID" = "D_PRD"."PRD_ID" and "D_PRD"."YEAR" = 2013 group by "D_PRD"."YEAR", "D_PRD"."MONTH"

SQL 2:

Code:

`select "AGG_02_F_FACT_TABLE"."YEAR" as "c1", "AGG_02_F_FACT_TABLE"."MONTH" as "c2", sum("AGG_02_F_FACT_TABLE"."T_ORG_COST") as "m0", sum("AGG_02_F_FACT_TABLE"."T_PROM_COST") as "m1", sum("AGG_02_F_FACT_TABLE"."T_TOT_COST") as "m2" from "MONDRIAN_TEST"."AGG_02_F_FACT_TABLE" "AGG_02_F_FACT_TABLE", where "AGG_02_F_FACT_TABLE"."YEAR" = 2013 group by "AGG_02_F_FACT_TABLE"."YEAR", "AGG_02_F_FACT_TABLE"."MONTH"`

Please help me adding condition to dimension table but can still make good use of aggregate table. Thanks!

Read Mondrian Cache Issue

avinokurov — Fri, 17 May 2013 14:57:11 GMT

I see a problem running queries back to back.
Second query returns cells that are a sum of cells from the first query. Mondrian recognizes that fact and instead of retrieving data for the second query from the database it aggregates cells from the cache. During that roll up process the problem occurs.

I am having an issue when Mondrian rolls up data from cache. It seems to be happening here: SegmentBodyIterator. It looks like this is loaded from the cache: DenseDoubleSegmentBody, but the first value is zero so SegmentBodyIterator skips the first value and reports size() = (one less than in the cache). But index is not adjusted and as a result I am missing the last value from the cache in my total. Please let me know if this is not the right place to report this, but I just wanted to find out whether this is a problem or maybe I am missing something.

Sum of measures for calculated members

pentaho.user — Fri, 17 May 2013 10:32:48 GMT

Hi,

I want a formula for calculating [Measures].[Accounts]/SUM([Measures].[Accounts]), something like that.

For example,

Bank	Account	Calculated Account
1	26	0.16
2	73	0.46
3	85	0.54

Sum of all accounts is 157, Calculated Account of Bank 1 is 26/157=0.16, how to achieve this result? Can someone help?

Thanks!

Snowflake Dimension Referencing Same Table in Mondrian 4

adamw2k — Fri, 17 May 2013 00:50:28 GMT

Hi everyone,

Thanks in advance for any ideas/help. I have a shared dimension (call it DIM_USER) that has 2 uses of a snowflaked table - call it BIRTH_LOCATION_KEY and CURRENT_LOCATION_KEY. How would that work in Mondrian 4?

Right now i have it as:

When it comes to the actual User dimension, how do I setup the table="" command in an attribute to know which Link it is referring to?

Thanks!

How to disable Mondrian automatically find aggregate table

DreamingFighter — Thu, 16 May 2013 04:52:57 GMT

Hi everyone, I am brand new to Mondrian, I can make aggregate table to work but cannot control them.

I use mondrian 3.5 (version that bundled in Pentaho BI Suite 4.8), my mondrian config:
mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true

I have 1 fact table F_FACT_TABLE, one aggregate table F_FACT_TABLE_AGG_01. My mondrian schema as follow:

Code:



  

    

      

      

      

      

      

      

      

    

  

  

    


    

    

    

    

    

    F_FACT_TABLE_AGG_01">

        

        

        

        

        

        

    

    

  


But the SQL Script generated by mondrian doesn't use F_FACT_TABLE_AGG_01. If I change table name to AGG_01_F_FACT_TABLE, mondrian automatically use the table without anyone change the mondrian config file. If I create another AGG_02_F_FACT_TABLE with dummy column, mondrian cannot start the schema with error: 


	Code:
	
2013-05-16 11:08:45,861 ERROR [mondrian.rolap.aggmatcher.AggTableManager] Recognizer.checkFactCount: Candidate aggregate table 'AGG_02_F_FACT_TABLE' for fact table 'F_FACT_TABLE' has no fact count columns.

2013-05-16 11:08:45,861 ERROR [mondrian.rolap.aggmatcher.AggTableManager] Recognizer.checkNosMeasures: Candidate aggregate table 'AGG_02_F_FACT_TABLE' for fact table 'F_FACT_TABLE' has no measure columns.
Can anybody show me how to config mondrian to use F_FACT_TABLE_AGG_01? Also, can anybody show me how make mondrian only find aggregated tables in pre-defined table list?



Thanks in advanced!




Drilling across
alcarrillo — Wed, 15 May 2013 19:18:04 GMT
I just read a book where it is used the term "Drilling Across", the idea is involving data from multiple fact tables and combining the answer sets. I have looked over the web and in this forum trying to find if Mondrian is capable to handle it. So far no luck.



This is a core feature required for my project, any help or info about if Mondrian is able to do it will be highly appreciated.




Mondrian - Redshift
sujenmaharjan — Tue, 14 May 2013 22:15:34 GMT
I just did a quick test on mondrian(3.3 version)-redshift connection. I have been able to get my cube working so far. Redshift is based on postgre 8.0...and mondrian already has support for postgres dialects, so this is as expected. But I have seen some commits in the mondrian master branch @ github, re redshift, like a new calss "RedshiftDialect.java".

https://github.com/pentaho/mondrian/...drian/spi/impl



So I am just wondering, what sort of unseen issues should I expect from using mondrian 3.3? I know, redshift has many unsupported postgres types and features, so they could be bottleneck at some time. But I really need to know if mondrian needs any tweak, or I can just use the existing version or I need to build the master branch source?



Note: Pentaho EE latest claims to have full support for Redshift. I check the mondrian version @ 4.8.1 GA release, it uses mondrian 3.5.6 which lacks the RedshiftDialect class.



So, could you please put some more light over this?



Thanks,

Sujen




Where to publish a star schema
scollins — Mon, 13 May 2013 14:34:18 GMT
I have created a star schema ... I am not able to see it in the User  Console....where does this need to be published in order to view and use  it in the User Console?




Cell Formatter issue
Minimoy — Mon, 13 May 2013 08:54:19 GMT
Hello,

I'm using a JavaScript into a cell formatter as part of a Calculated Member. The Java script converts seconds into a D/H/M/S format. The script works fine on a dev server (running BI Server 4.5) BUT does not works under the pre prod environment (running  BI 4.1 server). The format is not applied. I know the Javascript piece is fine (working on the dev server). Just wonder why it doesn't work on our pre work server. The Cell formatter feature was there in 4.1 so it should work with that version. But I wonder if there was any difference in the declaration of the cell formatter. 



See the calculated meber definition here below.





      

        

          

        

      

      

        

      

    




Openi Plugin for Pentaho not working with mondrian roles
amirdhagopal — Fri, 10 May 2013 11:43:23 GMT
I have a mondrian schema file with Role configuration, restricting the role only for Central region.





    

          

                   

                                    

                    

          

    





In Pentaho, I have a user central_user with roles Authenticated and Central.



Also, uncommented Mondrian-One-To-One-UserRoleMapper in pentahoObjects.spring.xml



I am evaluating openi and saiku plugins for Pentaho.



In saiku plugin, the analysis report correctly filters and shows only Central Region.



But in the Openi plugin, the report shows all the regions.



Is there any other additional settings required for Openi to make it pass the role information to Mondrian?




Query regarding: Mondrain interaction via MS ADOMD Provider - XML/A Service
HARI VENKATESH — Fri, 10 May 2013 09:34:24 GMT
Hi Support, 





We are trying to connect the FoodMart cube hosted in flexmonster.com (via XML/A and Mondrian) using Microsoft ADOMD provider. When we try to execute the MDX query using Microsoft ADOMD provider, we are getting exception "The server sent an unrecognizable response." 





Please find the sample (MondrianSample.zip) under the below link. http://www.fileconvoy.com/dfl.php?id...48fd61222ac1e4





Kindly let us know the complete details on this error and solution if any. 





Thanks in advance.




Query regarding: Mondrain interaction via MS ADOMD Provider - XML/A
HARI VENKATESH — Fri, 10 May 2013 09:32:54 GMT
Hi Support, 



We are trying to connect the FoodMart cube hosted in flexmonster.com (via XML/A and Mondrian) using Microsoft ADOMD provider. When we try to execute the MDX query using Microsoft ADOMD provider, we are getting exception "The server sent an unrecognizable response." 



Please find the sample (MondrianSample.zip) under the below link. http://www.fileconvoy.com/dfl.php?id...48fd61222ac1e4



Kindly let us know the complete details on this error and solution if any. 



Thanks in advance.




Query regarding: Mondrain interaction via MS ADOMD Provider - XML/A
HARI VENKATESH — Fri, 10 May 2013 09:30:52 GMT
Hi Support, 



We are trying to connect the FoodMart cube hosted in flexmonster.com (via XML/A and Mondrian) using Microsoft ADOMD provider. When we try to execute the MDX query using Microsoft ADOMD provider, we are getting exception "The server sent an unrecognizable response." 



Please find the sample (MondrianSample.zip) under the below link. http://www.fileconvoy.com/dfl.php?id...48fd61222ac1e4



Kindly let us know the complete details on this error and solution if any. 



Thanks in advance.




Custom MDX connection to dynamically change roles
xgumara — Wed, 08 May 2013 21:40:24 GMT
Hello. I am having problems to dynamically change the role of an user in Pentaho.



I am trying it with the Steel Wheels schema and I hope to get some help here.



First I added this to the mondrian schema:


	HTML Code:
	
"Region Manager">

  "all">

    "SteelWheelsSales" access="all">

      "[Markets].[Markets]" access="custom" rollupPolicy="partial">

        "[Markets].[APAC]" access="all"/>

      

    

  


And I implemented my custom SteelWheelsMDXConnection class and added it to the connection-MDX bean in pentahoObjects.spring.xml:




	Code:
	
import mondrian.olap.*;

import org.pentaho.platform.plugin.services.connections.mondrian.MDXConnection;



public class SteelWheelsMDXConnection extends MDXConnection {

  @Override

  protected void init (Util.PropertyList properties) {

    super.init(properties);

    Connection thisConn = this.getConnection();

    Role authRole = thisConn.getSchema().lookupRole("Region Manager");

    SteelWheelsCustomRole customRole = new SteelWheelsCustomRole(authRole, thisConn.getSchema());

    thisConn.setRole(customRole);

    setRole(customRole);

  }

}
Finally this is the code for the SteelWheelsCustomRole class:




	Code:
	
import mondrian.olap.*;

import java.util.List;





public class SteelWheelsCustomRole extends DelegatingRole {





 public SteelWheelsCustomRole(Role role, Schema schema) {

   super(((RoleImpl)role).makeMutableClone());

  defineGrantsForUser(schema);

 }





 private void defineGrantsForUser(Schema schema) {

  RoleImpl role = (RoleImpl) this.role;

  Cube cube = schema.lookupCube("SteelWheelsSales", true);

  Dimension dimension = null;

  for (Dimension d : cube.getDimensions()) {

   if (d.getName().compareTo("Markets") == 0) {

   dimension = d;

   break;

   }

  }

  Hierarchy hierarchy = dimension.getHierarchies()[0];

  Level[] levels = hierarchy.getLevels();

  Level topLevel = levels[1];

  List  members = schema.getSchemaReader().withLocus().getLevelMembers(topLevel, true);

  for (Member member : members) {

   if (member.getName().compareTo("Japan") == 0) {

    role.grant(member, Access.ALL);

   } else {

    role.grant(member, Access.NONE);

   }

  }

 }

}
I removed Log lines for readability, but this code runs without errors. The result should be that the user that has the "Region Manager" role can only see the Japan market. But when I log in Pentaho and create a new analysis view, I still get the old role, and only APAC is visible.



Do you know what I am doing wrong? It just looks like the role is being created well but it is not saved to the connection somehow.



Any help would be much apreciated!




MDX Query without measure
AdamPoolman — Wed, 08 May 2013 11:00:56 GMT
Hi,



I'm currently experiencing an issue with Mondrian with some MDX generated via Saiku. If I run a query with two dimensions and no defined measure, the query takes a long time to return. However, if I specify a measure as well as the two dimensions then the query returns quickly. 



I'm confused by this as I would expect Mondrian to be using the default measure and to return at the same speed as when I specify the measure. Looking at the SQL it appears to load a number of segments in batches of five values and the generated MDX has no measures shown in it.



Any help is much appreciated,



Adam Poolman




How to combine / join Cube Facts
Heisenberg — Fri, 03 May 2013 09:41:24 GMT
Hello Community.



I have three cubes. Two of them share three dimensions (one of them a Date Dimension), the third one only shares two (one of them a Date Dimension) with the other ones.

The cubes are like that:



Cube #1 defines operational parameters of a maschine, like hours it run per day. The dimensions are Date and Maschine Specifications.

Cube #2 defines problems per maschine, per day. The dimensions are Date, Maschine Specifications and Error Code Specifications.

Cube #3 is quiet similar to cube #2, it only differs in the facts, the dimensions are the same.



The Fact Tables have foreign keys to the same Dimension Tables. As standard aggregation, the fact fields should all get summed up.



I read about virtual cubes and shared dimensions but I'm missing an example how to build the cubes in Schema Workbench.



My approach was to build a schema, which contains three cubes and the thee dimensions. I think if I add the Dimensions directly to a Schema but not to a cube, it's a shared dimension, right? I added "Dimension Usage" to the cubes.



But now I don't know how to build the virtual cube or if this approach is the right one at all? I want to be able to see dependencys between the facts of the three cubes like: How did problems, which are logged in cube #2 affect the operational parameters logged in cube #1.



Thanks & Regards!




Handling Boolean Type in Calculated measures
rvmahadevan — Thu, 02 May 2013 09:02:21 GMT
Hi,

I created a Dimnsion called LogCount and Hierarchy as Logcount, I have a level inside the hierarchy as IsLogCount (boolean). I have a measure LogEvent which gives me the count of logs. Now I want to have a calculated measure which gives me the count of logs having IslogCount as True only. Below is the query could anyone help me:



COALESCEEMPTY([Measures].[LogEvent],[LingoEvent].[IsLogCount].&[True])




Why is Mondrian pre-aggegating before calculating Average?
scottedwards2000 — Mon, 29 Apr 2013 21:32:07 GMT
Hi,

I have a super-simple analyzer report, where all I'm doing is calculating the average of a measure.  In the schema, it's default aggregation is AVERAGE.  The only other aspect of the report is a filter on date, where I restrict it to being within a list of 3 dates.



What's odd is that it appears that Mondrian is actually calculating the average for each date BEFORE averaging those 3 numbers to get the value displayed in the report.  This seems very wrong (the report only has that one average displayed - no other fields).



I don't know MDX that well, but below is what I pulled from the mdx log if that helps:



With

Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_ActivityDate], Not IsEmpty ([Measures].[AveragePosition]))'

Set [*NATIVE_MEMBERS_ActivityDate] as 'Generate([*NATIVE_CJ_SET], {[ActivityDate].CurrentMember})'

Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'

Set [*BASE_MEMBERS_ActivityDate] as '{[ActivityDate].[2012-09-01 00:00:00.0],[ActivityDate].[2012-09-02 00:00:00.0],[ActivityDate].[2012-09-03 00:00:00.0]}'

Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'

Member [ActivityDate].[*SLICER_MEMBER] as 'Aggregate ([*NATIVE_MEMBERS_ActivityDate])', SOLVE_ORDER=-400

Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[AveragePosition]', FORMAT_STRING = '#,###.00;(#,###.00)', SOLVE_ORDER=400

Select

[*BASE_MEMBERS_Measures] on columns

From [SQLTestCube1_JustResults]

Where ([ActivityDate].[*SLICER_MEMBER])




How to do report with same month and different years?
krisss — Mon, 29 Apr 2013 10:27:41 GMT
Hello everybody!



I need to build a comparative Analyzer Report, so that the month was common, and different years.



For example, in January (a common column) / 2012 - 2013 (two columns in January), and so on for all months.



Could somebody suggest how should I achieve this?

Please an example.



Thnaks in advance!




Too slow MDX queries
Emilio1985 — Mon, 29 Apr 2013 09:02:53 GMT
Hello.



I work with bi server 4.5 with windows xp plataform. I read a post if you clear all in biserver-ce\tomcat\temp 

mondrian goes  faster I do it. And works fine but another solutions? 





For example  a configuration file when you change it, it improve fast execute queries?




TimeDimension defined Year-Month crashes when "Current" or "Previous" etc. are used
EvanYates — Mon, 29 Apr 2013 04:13:36 GMT
I have created a few TimeDimensions around dates in a schema I defined using Schema Workbench (v3.3.0.14703). 



When I go to use those as a filter in the User Console Anlayzer Report (v4.1.0) creator, it gives me all the options for filtering you would expect from a TimeDimension (List, Range, Current, Previous, Next etc.). 



I can successfully "Select from a list" for an individual Year-Month combination (e.g. 2013-04). I can successfully "Select a range". (e.g Between (incl.) 2013-01 and 2013-04)



However when I try to do any of the  "Current", "Previous", "Next"  type filters, the analyzer filter update crashes with this error.




	Code:
	
java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: mondrian.olap.MondrianException: Mondrian Error:Syntax error at line 1, column 134, token '}'
I have checked the table defined for the Cube and none of the date fields are NULL. I have checked the lookup table (dim_HR_date) and all values in the table link to a record with a valid value. (every NC_Expiry_date_sk in the fact table has a corresponding date_sk in the lookup table. Every row in dim_HR_date has a valid and correctly formatted Year-Month and YearMonth value ).



A typical example of the Dimensions I have set up is defined below




	Code:

Is there something I have missed in defining the dimension that would cause the "Current", "Previous", "Next" style filters to fail? 

Are there any known issues with the "Current", "Previous", "Next" style filters? I've searched the forum but can't find anything that exactly reflects my problem.



Thanks



Pentaho schema workbench cube error message after being published
pentahouser4 — Mon, 29 Apr 2013 02:47:52 GMT
I created a cube using schema workbench. I published the report in 'pentaho user console'. When I try to filter ID, It says: 'Oops. There was an error. Please try again, or contact your administrator.'



On the pentaho.log inside /opt/pentaho/server/biserver-ce/logs, I get below message:





Caused by: java.lang.RuntimeException: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp



How can I get rid of this error message. 



Thanks.




XML for Analysis tester: can't connect to the database
fromcelticpark — Fri, 26 Apr 2013 13:21:54 GMT
Hello!

I configured mondrain as it's described in the documentation, and everything seems to work fine except for the "XML for Analysis tester".

When I try to make request through the xmla interface I get this error: 











SOAP-ENV:Server.00HSBE02

XMLA Discover unparse results error

Mondrian





00HSBE02



The Mondrian XML: Mondrian Error:Internal error: Unknown database 'FoodMart'















Does anyone have a suggestion? 





Mondrian ver.: 3.4.1

Sql connector: mysql-connector-java-5.1.24-bin



DataSources.xml:







    

        Provider=Mondrian;DataSource=MondrianFoodMart;

        Mondrian FoodMart Data Warehouse

        http://localhost:8080/mondrian/xmla

        Provider=mondrian; Jdbc=jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart; JdbcDrivers=com.mysql.jdbc.Driver

Mondrian

MDP

Unauthenticated

        

            

                

/WEB-INF/queries/FoodMart.xml

            

        

    






How many cubes can I fit in a virtual cube?
fconsult — Wed, 24 Apr 2013 18:51:49 GMT
Hi all!





How many cubes can a virtual cube hold? Ten? 256? 65535? 



[]s,





Fábio




Life cycle of Pentaho Analysis services
k.senthilkumar — Wed, 24 Apr 2013 05:19:19 GMT
Hello everyone,



Is there any blog content or wiki URL to learn about the life cycle of Pentaho Analysis cubes?



Pls suggest.



Senthil.