mondrian.olap.MondrianException: Mondrian Error:Internal error: Can not find Default Member with name "[Time.Weekly].[All Time.Weeklys].[1997]" in Hierarchy "Time.Weekly"

2,265 views
Skip to first unread message

jcha...@gmail.com

unread,
Mar 29, 2009, 10:07:13 AM3/29/09
to PHI's Mondrian OLAP Group
I've followed the install guide that you guys have very helpfully
posted, and I'm afraid I've just missed something somewhere in the
configuration process, because each of the sample links just presents
me with a huge stack trace with the lead message being:

mondrian.olap.MondrianException: Mondrian Error:Internal error: Can
not find Default Member with name "[Time.Weekly].[All Time.Weeklys].
[1997]" in Hierarchy "Time.Weekly"

I'm running on a Ubuntu linux install, apache tomcat, and JVM 1.6.

Any help would be appreciated.

If you need me to provide further details, please let me know.

Feris Thia

unread,
Mar 29, 2009, 10:14:09 AM3/29/09
to phi-mond...@googlegroups.com
Hi Jchadwic,

Can you attach foodmart.xml which is located in your [TOMCAT]/webapps/[mondrian]/WEB-INF/queries/ folder ?

Regards,

Feris

jcha...@gmail.com

unread,
Mar 29, 2009, 10:53:09 AM3/29/09
to PHI's Mondrian OLAP Group
<?xml version="1.0"?>
<Schema name="FoodMart">
<!--
== $Id: //open/mondrian-release/3.0/demo/FoodMart.xml#4 $
== This software is subject to the terms of the Common Public
License
== Agreement, available at the following URL:
== http://www.opensource.org/licenses/cpl.html.
== Copyright (C) 2000-2002 Kana Software, Inc.
== Copyright (C) 2002-2007 Julian Hyde and others.
== All Rights Reserved.
== You must accept the terms of that agreement to use this software.
-->

<!-- Shared dimensions -->

<Dimension name="Store">
<Hierarchy hasAll="true" primaryKey="store_id">
<Table name="store"/>
<Level name="Store Country" column="store_country"
uniqueMembers="true"/>
<Level name="Store State" column="store_state"
uniqueMembers="true"/>
<Level name="Store City" column="store_city"
uniqueMembers="false"/>
<Level name="Store Name" column="store_name"
uniqueMembers="true">
<Property name="Store Type" column="store_type"/>
<Property name="Store Manager" column="store_manager"/>
<Property name="Store Sqft" column="store_sqft" type="Numeric"/
>
<Property name="Grocery Sqft" column="grocery_sqft"
type="Numeric"/>
<Property name="Frozen Sqft" column="frozen_sqft"
type="Numeric"/>
<Property name="Meat Sqft" column="meat_sqft" type="Numeric"/>
<Property name="Has coffee bar" column="coffee_bar"
type="Boolean"/>
<Property name="Street address" column="store_street_address"
type="String"/>
</Level>
</Hierarchy>
</Dimension>

<Dimension name="Store Size in SQFT">
<Hierarchy hasAll="true" primaryKey="store_id">
<Table name="store"/>
<Level name="Store Sqft" column="store_sqft" type="Numeric"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Dimension name="Store Type">
<Hierarchy hasAll="true" primaryKey="store_id">
<Table name="store"/>
<Level name="Store Type" column="store_type"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Dimension name="Time" type="TimeDimension">
<Hierarchy hasAll="false" primaryKey="time_id">
<Table name="time_by_day"/>
<Level name="Year" column="the_year" type="Numeric"
uniqueMembers="true"
levelType="TimeYears"/>
<Level name="Quarter" column="quarter" uniqueMembers="false"
levelType="TimeQuarters"/>
<Level name="Month" column="month_of_year" uniqueMembers="false"
type="Numeric"
levelType="TimeMonths"/>
</Hierarchy>
<Hierarchy hasAll="true" name="Weekly" primaryKey="time_id"
defaultMember="[Time.Weekly].[All Time.Weeklys].[1997]">
<Table name="time_by_day"/>
<Level name="Year" column="the_year" type="Numeric"
uniqueMembers="true"
levelType="TimeYears"/>
<Level name="Week" column="week_of_year" type="Numeric"
uniqueMembers="false"
levelType="TimeWeeks"/>
<Level name="Day" column="day_of_month" uniqueMembers="false"
type="Numeric"
levelType="TimeDays"/>
</Hierarchy>
</Dimension>

<Dimension name="Product">
<Hierarchy hasAll="true" primaryKey="product_id"
primaryKeyTable="product">
<Join leftKey="product_class_id" rightKey="product_class_id">
<Table name="product"/>
<Table name="product_class"/>
</Join>
<!--
<Query>
<SQL dialect="generic">
SELECT *
FROM "product", "product_class"
WHERE "product"."product_class_id" =
"product_class"."product_class_id"
</SQL>
</Query>
<Level name="Product Family" column="product_family"
uniqueMembers="true"/>
<Level name="Product Department" column="product_department"
uniqueMembers="false"/>
<Level name="Product Category" column="product_category"
uniqueMembers="false"/>
<Level name="Product Subcategory" column="product_subcategory"
uniqueMembers="false"/>
<Level name="Brand Name" column="brand_name"
uniqueMembers="false"/>
<Level name="Product Name" column="product_name"
uniqueMembers="true"/>
-->
<Level name="Product Family" table="product_class"
column="product_family"
uniqueMembers="true"/>
<Level name="Product Department" table="product_class"
column="product_department"
uniqueMembers="false"/>
<Level name="Product Category" table="product_class"
column="product_category"
uniqueMembers="false"/>
<Level name="Product Subcategory" table="product_class"
column="product_subcategory"
uniqueMembers="false"/>
<Level name="Brand Name" table="product" column="brand_name"
uniqueMembers="false"/>
<Level name="Product Name" table="product" column="product_name"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Dimension name="Warehouse">
<Hierarchy hasAll="true" primaryKey="warehouse_id">
<Table name="warehouse"/>
<Level name="Country" column="warehouse_country"
uniqueMembers="true"/>
<Level name="State Province" column="warehouse_state_province"
uniqueMembers="true"/>
<Level name="City" column="warehouse_city" uniqueMembers="false"/
>
<Level name="Warehouse Name" column="warehouse_name"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<!-- Sales -->
<Cube name="Sales" defaultMeasure="Unit Sales">
<Table name="sales_fact_1997">
<!--
<AggExclude name="agg_l_03_sales_fact_1997" />
<AggExclude name="agg_ll_01_sales_fact_1997" />
<AggExclude name="agg_pl_01_sales_fact_1997" />
<AggExclude name="agg_l_05_sales_fact_1997" />
-->
<AggExclude name="agg_c_special_sales_fact_1997" />
<!--
<AggExclude name="agg_c_14_sales_fact_1997" />
-->
<AggExclude name="agg_lc_100_sales_fact_1997" />
<AggExclude name="agg_lc_10_sales_fact_1997" />
<AggExclude name="agg_pc_10_sales_fact_1997" />
<AggName name="agg_c_special_sales_fact_1997">
<AggFactCount column="FACT_COUNT"/>
<AggIgnoreColumn column="foo"/>
<AggIgnoreColumn column="bar"/>
<AggForeignKey factColumn="product_id" aggColumn="PRODUCT_ID" /
>
<AggForeignKey factColumn="customer_id"
aggColumn="CUSTOMER_ID" />
<AggForeignKey factColumn="promotion_id"
aggColumn="PROMOTION_ID" />
<AggForeignKey factColumn="store_id" aggColumn="STORE_ID" />
<!--
<AggMeasure name="[Measures].[Avg Unit Sales]"
column="UNIT_SALES_AVG"/>
-->
<AggMeasure name="[Measures].[Unit Sales]"
column="UNIT_SALES_SUM" />
<AggMeasure name="[Measures].[Store Cost]"
column="STORE_COST_SUM" />
<AggMeasure name="[Measures].[Store Sales]"
column="STORE_SALES_SUM" />
<AggLevel name="[Time].[Year]" column="TIME_YEAR" />
<AggLevel name="[Time].[Quarter]" column="TIME_QUARTER" />
<AggLevel name="[Time].[Month]" column="TIME_MONTH" />
</AggName>
</Table>

<DimensionUsage name="Store" source="Store" foreignKey="store_id"/>
<DimensionUsage name="Store Size in SQFT" source="Store Size in
SQFT"
foreignKey="store_id"/>
<DimensionUsage name="Store Type" source="Store Type"
foreignKey="store_id"/>
<DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
<DimensionUsage name="Product" source="Product"
foreignKey="product_id"/>
<Dimension name="Promotion Media" foreignKey="promotion_id">
<Hierarchy hasAll="true" allMemberName="All Media"
primaryKey="promotion_id" defaultMember="All Media">
<Table name="promotion"/>
<Level name="Media Type" column="media_type"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Promotions" foreignKey="promotion_id">
<Hierarchy hasAll="true" allMemberName="All Promotions"
primaryKey="promotion_id" defaultMember="[All Promotions]">
<Table name="promotion"/>
<Level name="Promotion Name" column="promotion_name"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Customers" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Customers"
primaryKey="customer_id">
<Table name="customer"/>
<Level name="Country" column="country" uniqueMembers="true"/>
<Level name="State Province" column="state_province"
uniqueMembers="true"/>
<Level name="City" column="city" uniqueMembers="false"/>
<Level name="Name" column="customer_id" type="Numeric"
uniqueMembers="true">
<NameExpression>
<SQL dialect="oracle">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="access">
fname + ' ' + lname
</SQL>
<SQL dialect="postgres">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="mysql">
CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)
</SQL>
<SQL dialect="mssql">
fname + ' ' + lname
</SQL>
<SQL dialect="derby">
"customer"."fullname"
</SQL>
<SQL dialect="db2">
CONCAT(CONCAT("customer"."fname", ' '), "customer"."lname")
</SQL>
<SQL dialect="luciddb">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="teradata">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="generic">
fullname
</SQL>
</NameExpression>
<OrdinalExpression>
<SQL dialect="oracle">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="access">
fname + ' ' + lname
</SQL>
<SQL dialect="postgres">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="mysql">
CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)
</SQL>
<SQL dialect="mssql">
fname + ' ' + lname
</SQL>
<SQL dialect="derby">
"customer"."fullname"
</SQL>
<SQL dialect="db2">
CONCAT(CONCAT("customer"."fname", ' '), "customer"."lname")
</SQL>
<SQL dialect="luciddb">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="generic">
fullname
</SQL>
</OrdinalExpression>
<Property name="Gender" column="gender"/>
<Property name="Marital Status" column="marital_status"/>
<Property name="Education" column="education"/>
<Property name="Yearly Income" column="yearly_income"/>
</Level>
</Hierarchy>
</Dimension>
<Dimension name="Education Level" foreignKey="customer_id">
<Hierarchy hasAll="true" primaryKey="customer_id">
<Table name="customer"/>
<Level name="Education Level" column="education"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Gender" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Gender"
primaryKey="customer_id">
<Table name="customer"/>
<Level name="Gender" column="gender" uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Marital Status" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Marital Status"
primaryKey="customer_id">
<Table name="customer"/>
<Level name="Marital Status" column="marital_status"
uniqueMembers="true" approxRowCount="111"/>
</Hierarchy>
</Dimension>
<Dimension name="Yearly Income" foreignKey="customer_id">
<Hierarchy hasAll="true" primaryKey="customer_id">
<Table name="customer"/>
<Level name="Yearly Income" column="yearly_income"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Measure name="Unit Sales" column="unit_sales" aggregator="sum"
formatString="Standard"/>
<Measure name="Store Cost" column="store_cost" aggregator="sum"
formatString="#,###.00"/>
<Measure name="Store Sales" column="store_sales" aggregator="sum"
formatString="#,###.00"/>
<Measure name="Sales Count" column="product_id" aggregator="count"
formatString="#,###"/>
<Measure name="Customer Count" column="customer_id"
aggregator="distinct-count" formatString="#,###"/>
<Measure name="Promotion Sales" aggregator="sum" formatString="#,###.
00">
<MeasureExpression>
<SQL dialect="access">
Iif("sales_fact_1997"."promotion_id" = 0, 0,
"sales_fact_1997"."store_sales")
</SQL>
<SQL dialect="oracle">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else
"sales_fact_1997"."store_sales" end)
</SQL>
<SQL dialect="postgres">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else
"sales_fact_1997"."store_sales" end)
</SQL>
<SQL dialect="mysql">
(case when `sales_fact_1997`.`promotion_id` = 0 then 0 else
`sales_fact_1997`.`store_sales` end)
</SQL>
<SQL dialect="derby">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else
"sales_fact_1997"."store_sales" end)
</SQL>
<SQL dialect="luciddb">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else
"sales_fact_1997"."store_sales" end)
</SQL>
<SQL dialect="db2">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else
"sales_fact_1997"."store_sales" end)
</SQL>
<SQL dialect="generic">
(case when sales_fact_1997.promotion_id = 0 then 0 else
sales_fact_1997.store_sales end)
</SQL>
</MeasureExpression>
</Measure>
<CalculatedMember
name="Profit"
dimension="Measures">
<Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</
Formula>
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>
<CalculatedMember
name="Profit last Period"
dimension="Measures"
formula="COALESCEEMPTY((Measures.[Profit],
[Time].PREVMEMBER), Measures.[Profit])"
visible="false">
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="18"/>
</CalculatedMember>
<CalculatedMember
name="Profit Growth"
dimension="Measures"
formula="([Measures].[Profit] - [Measures].[Profit last
Period]) / [Measures].[Profit last Period]"
visible="true"
caption="Gewinn-Wachstum">
<CalculatedMemberProperty name="FORMAT_STRING" value="0.0%"/>
</CalculatedMember>
</Cube>

<Cube name="Warehouse">
<Table name="inventory_fact_1997"/>

<DimensionUsage name="Store" source="Store" foreignKey="store_id"/>
<DimensionUsage name="Store Size in SQFT" source="Store Size in
SQFT"
foreignKey="store_id"/>
<DimensionUsage name="Store Type" source="Store Type"
foreignKey="store_id"/>
<DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
<DimensionUsage name="Product" source="Product"
foreignKey="product_id"/>
<DimensionUsage name="Warehouse" source="Warehouse"
foreignKey="warehouse_id"/>

<Measure name="Store Invoice" column="store_invoice"
aggregator="sum"/>
<Measure name="Supply Time" column="supply_time" aggregator="sum"/>
<Measure name="Warehouse Cost" column="warehouse_cost"
aggregator="sum"/>
<Measure name="Warehouse Sales" column="warehouse_sales"
aggregator="sum"/>
<Measure name="Units Shipped" column="units_shipped"
aggregator="sum" formatString="#.0"/>
<Measure name="Units Ordered" column="units_ordered"
aggregator="sum" formatString="#.0"/>
<Measure name="Warehouse Profit" aggregator="sum">
<MeasureExpression>
<SQL dialect="mysql">
`warehouse_sales` - `inventory_fact_1997`.`warehouse_cost`
</SQL>
<SQL dialect="generic">
&quot;warehouse_sales&quot; -
&quot;inventory_fact_1997&quot;.&quot;warehouse_cost&quot;
</SQL>
</MeasureExpression>
</Measure>
<CalculatedMember
name="Average Warehouse Sale"
dimension="Measures">
<Formula>[Measures].[Warehouse Sales] / [Measures].[Warehouse Cost]
</Formula>
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>
<NamedSet name="Top Sellers">
<Formula>TopCount([Warehouse].[Warehouse Name].MEMBERS, 5,
[Measures].[Warehouse Sales])</Formula>
</NamedSet>
</Cube>

<!-- Test a cube based upon a single table. -->
<Cube name="Store">
<Table name="store"/>
<!-- We could have used the shared dimension "Store Type", but we
want to test private dimensions without primary key. -->
<Dimension name="Store Type">
<Hierarchy hasAll="true">
<Level name="Store Type" column="store_type"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<!-- We don't have to specify primary key or foreign key since the
shared
dimension "Store" has the same underlying table as the cube. -->
<DimensionUsage name="Store" source="Store"/>

<Dimension name="Has coffee bar">
<Hierarchy hasAll="true">
<Level name="Has coffee bar" column="coffee_bar"
uniqueMembers="true"
type="Boolean"/>
</Hierarchy>
</Dimension>

<Measure name="Store Sqft" column="store_sqft" aggregator="sum"
formatString="#,###"/>
<Measure name="Grocery Sqft" column="grocery_sqft" aggregator="sum"
formatString="#,###"/>

</Cube>

<Cube name="HR">
<Table name="salary"/>
<!-- Use private "Time" dimension because key is different than
public
"Time" dimension. -->
<Dimension name="Time" type="TimeDimension" foreignKey="pay_date">
<Hierarchy hasAll="false" primaryKey="the_date">
<Table name="time_by_day"/>
<Level name="Year" column="the_year" type="Numeric"
uniqueMembers="true"
levelType="TimeYears"/>
<Level name="Quarter" column="quarter" uniqueMembers="false"
levelType="TimeQuarters"/>
<!-- Use the_month as source for the name, so members look like
[Time].[1997].[Q1].[Jan] rather than [Time].[1997].[Q1].
[1]. -->
<Level name="Month" column="month_of_year"
nameColumn="the_month"
uniqueMembers="false" type="Numeric" levelType="TimeMonths"/
>
</Hierarchy>
</Dimension>

<Dimension name="Store" foreignKey="employee_id" >
<Hierarchy hasAll="true" primaryKey="employee_id"
primaryKeyTable="employee">
<Join leftKey="store_id" rightKey="store_id">
<Table name="employee"/>
<Table name="store"/>
</Join>
<Level name="Store Country" table="store" column="store_country"
uniqueMembers="true"/>
<Level name="Store State" table="store" column="store_state"
uniqueMembers="true"/>
<Level name="Store City" table="store" column="store_city"
uniqueMembers="false"/>
<Level name="Store Name" table="store" column="store_name"
uniqueMembers="true">
<Property name="Store Type" column="store_type"/>
<Property name="Store Manager" column="store_manager"/>
<Property name="Store Sqft" column="store_sqft" type="Numeric"/
>
<Property name="Grocery Sqft" column="grocery_sqft"
type="Numeric"/>
<Property name="Frozen Sqft" column="frozen_sqft"
type="Numeric"/>
<Property name="Meat Sqft" column="meat_sqft" type="Numeric"/>
<Property name="Has coffee bar" column="coffee_bar"
type="Boolean"/>
<Property name="Street address" column="store_street_address"
type="String"/>
</Level>
</Hierarchy>
</Dimension>

<Dimension name="Pay Type" foreignKey="employee_id">
<Hierarchy hasAll="true" primaryKey="employee_id"
primaryKeyTable="employee">
<Join leftKey="position_id" rightKey="position_id">
<Table name="employee"/>
<Table name="position"/>
</Join>
<Level name="Pay Type" table="position" column="pay_type"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Dimension name="Store Type" foreignKey="employee_id">
<Hierarchy hasAll="true" primaryKeyTable="employee"
primaryKey="employee_id">
<Join leftKey="store_id" rightKey="store_id">
<Table name="employee"/>
<Table name="store"/>
</Join>
<Level name="Store Type" table="store" column="store_type"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Dimension name="Position" foreignKey="employee_id">
<Hierarchy hasAll="true" allMemberName="All Position"
primaryKey="employee_id">
<Table name="employee"/>
<Level name="Management Role" uniqueMembers="true"
column="management_role"/>
<Level name="Position Title" uniqueMembers="false"
column="position_title" ordinalColumn="position_id"/>
</Hierarchy>
</Dimension>

<Dimension name="Department" foreignKey="department_id">
<Hierarchy hasAll="true" primaryKey="department_id">
<Table name="department"/>
<Level name="Department Description" uniqueMembers="true"
column="department_id"/>
</Hierarchy>
</Dimension>
<Dimension name="Employees" foreignKey="employee_id">
<Hierarchy hasAll="true" allMemberName="All Employees"
primaryKey="employee_id">
<Table name="employee"/>
<Level name="Employee Id" type="Numeric" uniqueMembers="true"
column="employee_id" parentColumn="supervisor_id"
nameColumn="full_name" nullParentValue="0">
<Closure parentColumn="supervisor_id"
childColumn="employee_id">
<Table name="employee_closure"/>
</Closure>
<Property name="Marital Status" column="marital_status"/>
<Property name="Position Title" column="position_title"/>
<Property name="Gender" column="gender"/>
<Property name="Salary" column="salary"/>
<Property name="Education Level" column="education_level"/>
<Property name="Management Role" column="management_role"/>
</Level>
</Hierarchy>
</Dimension>

<Measure name="Org Salary" column="salary_paid" aggregator="sum"
formatString="Currency"/>
<Measure name="Count" column="employee_id" aggregator="count"
formatString="#,#"/>
<Measure name="Number of Employees" column="employee_id"
aggregator="distinct-count" formatString="#,#"/>
<CalculatedMember name="Employee Salary" dimension="Measures"
formatString="Currency"
formula="([Employees].currentmember.datamember, [Measures].[Org
Salary])"/>
<CalculatedMember name="Avg Salary" dimension="Measures"
formatString="Currency"
formula="[Measures].[Org Salary]/[Measures].[Number of
Employees]"/>
</Cube>

<!-- Cube with one ragged hierarchy (otherwise the same as the "Sales"
cube). -->
<Cube name="Sales Ragged">
<Table name="sales_fact_1997">
<AggExclude name="agg_pc_10_sales_fact_1997"/>
<AggExclude name="agg_lc_10_sales_fact_1997"/>
</Table>
<Dimension name="Store" foreignKey="store_id">
<Hierarchy hasAll="true" primaryKey="store_id">
<Table name="store_ragged"/>
<Level name="Store Country" column="store_country"
uniqueMembers="true"
hideMemberIf="Never"/>
<Level name="Store State" column="store_state"
uniqueMembers="true"
hideMemberIf="IfParentsName"/>
<Level name="Store City" column="store_city"
uniqueMembers="false"
hideMemberIf="IfBlankName"/>
<Level name="Store Name" column="store_name"
uniqueMembers="true"
hideMemberIf="Never">
<Property name="Store Type" column="store_type"/>
<Property name="Store Manager" column="store_manager"/>
<Property name="Store Sqft" column="store_sqft" type="Numeric"/
>
<Property name="Grocery Sqft" column="grocery_sqft"
type="Numeric"/>
<Property name="Frozen Sqft" column="frozen_sqft"
type="Numeric"/>
<Property name="Meat Sqft" column="meat_sqft" type="Numeric"/>
<Property name="Has coffee bar" column="coffee_bar"
type="Boolean"/>
<Property name="Street address" column="store_street_address"
type="String"/>
</Level>
</Hierarchy>
</Dimension>

<Dimension name="Geography" foreignKey="store_id">
<Hierarchy hasAll="true" primaryKey="store_id">
<Table name="store_ragged"/>
<Level name="Country" column="store_country"
uniqueMembers="true"
hideMemberIf="Never"/>
<Level name="State" column="store_state" uniqueMembers="true"
hideMemberIf="IfParentsName"/>
<Level name="City" column="store_city" uniqueMembers="false"
hideMemberIf="IfBlankName"/>
</Hierarchy>
</Dimension>

<DimensionUsage name="Store Size in SQFT" source="Store Size in
SQFT"
foreignKey="store_id"/>
<DimensionUsage name="Store Type" source="Store Type"
foreignKey="store_id"/>
<DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
<DimensionUsage name="Product" source="Product"
foreignKey="product_id"/>
<Dimension name="Promotion Media" foreignKey="promotion_id">
<Hierarchy hasAll="true" allMemberName="All Media"
primaryKey="promotion_id">
<Table name="promotion"/>
<Level name="Media Type" column="media_type"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Promotions" foreignKey="promotion_id">
<Hierarchy hasAll="true" allMemberName="All Promotions"
primaryKey="promotion_id">
<Table name="promotion"/>
<Level name="Promotion Name" column="promotion_name"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Customers" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Customers"
primaryKey="customer_id">
<Table name="customer"/>
<Level name="Country" column="country" uniqueMembers="true"/>
<Level name="State Province" column="state_province"
uniqueMembers="true"/>
<Level name="City" column="city" uniqueMembers="false"/>
<Level name="Name" uniqueMembers="true">
<KeyExpression>
<SQL dialect="oracle">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="access">
fname + ' ' + lname
</SQL>
<SQL dialect="postgres">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="mysql">
CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)
</SQL>
<SQL dialect="mssql">
fname + ' ' + lname
</SQL>
<SQL dialect="derby">
"customer"."fullname"
</SQL>
<SQL dialect="db2">
CONCAT(CONCAT("customer"."fname", ' '), "customer"."lname")
</SQL>
<SQL dialect="luciddb">
"fname" || ' ' || "lname"
</SQL>
<SQL dialect="generic">
fullname
</SQL>
</KeyExpression>
<Property name="Gender" column="gender"/>
<Property name="Marital Status" column="marital_status"/>
<Property name="Education" column="education"/>
<Property name="Yearly Income" column="yearly_income"/>
</Level>
</Hierarchy>
</Dimension>
<Dimension name="Education Level" foreignKey="customer_id">
<Hierarchy hasAll="true" primaryKey="customer_id">
<Table name="customer"/>
<Level name="Education Level" column="education"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Gender" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Gender"
primaryKey="customer_id">
<Table name="customer"/>
<Level name="Gender" column="gender" uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Marital Status" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Marital Status"
primaryKey="customer_id">
<Table name="customer"/>
<Level name="Marital Status" column="marital_status"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Dimension name="Yearly Income" foreignKey="customer_id">
<Hierarchy hasAll="true" primaryKey="customer_id">
<Table name="customer"/>
<Level name="Yearly Income" column="yearly_income"
uniqueMembers="true"/>
</Hierarchy>
</Dimension>
<Measure name="Unit Sales" column="unit_sales" aggregator="sum"
formatString="Standard"/>
<Measure name="Store Cost" column="store_cost" aggregator="sum"
formatString="#,###.00"/>
<Measure name="Store Sales" column="store_sales" aggregator="sum"
formatString="#,###.00"/>
<Measure name="Sales Count" column="product_id" aggregator="count"
formatString="#,###"/>
<Measure name="Customer Count" column="customer_id"
aggregator="distinct-count"
formatString="#,###"/>
</Cube>

<!-- a simpler version of "Sales" (with MEMBER_ORDINAL-properties) -->
<Cube name="Sales 2">
<Table name="sales_fact_1997"/>

<DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
<DimensionUsage name="Product" source="Product"
foreignKey="product_id"/>

<Dimension name="Gender" foreignKey="customer_id">
<Hierarchy hasAll="true" allMemberName="All Gender"
primaryKey="customer_id">
<Table name="customer"/>
<Level name="Gender" column="gender" uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Measure name="Sales Count" column="product_id" aggregator="count"
formatString="#,###">
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="1"/>
</Measure>

<Measure name="Unit Sales" column="unit_sales" aggregator="sum"
formatString="Standard">
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="2"/>
</Measure>

<Measure name="Store Sales" column="store_sales" aggregator="sum"
formatString="#,###.00">
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="3"/>
</Measure>

<Measure name="Store Cost" column="store_cost" aggregator="sum"
formatString="#,###.00">
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="6"/>
</Measure>

<Measure name="Customer Count" column="customer_id"
aggregator="distinct-count" formatString="#,###">
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="7"/>
</Measure>

<CalculatedMember
name="Profit"
dimension="Measures">
<Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</
Formula>
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/
>
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="4"/>
</CalculatedMember>

<CalculatedMember
name="Profit last Period"
dimension="Measures"
formula="COALESCEEMPTY((Measures.[Profit],
[Time].PREVMEMBER), Measures.[Profit])"
visible="false">
<CalculatedMemberProperty name="MEMBER_ORDINAL" value="5"/>
</CalculatedMember>
</Cube>

<VirtualCube name="Warehouse and Sales" defaultMeasure="Store Sales">
<VirtualCubeDimension cubeName="Sales" name="Customers"/>
<VirtualCubeDimension cubeName="Sales" name="Education Level"/>
<VirtualCubeDimension cubeName="Sales" name="Gender"/>
<VirtualCubeDimension cubeName="Sales" name="Marital Status"/>
<VirtualCubeDimension name="Product"/>
<VirtualCubeDimension cubeName="Sales" name="Promotion Media"/>
<VirtualCubeDimension cubeName="Sales" name="Promotions"/>
<VirtualCubeDimension name="Store"/>
<VirtualCubeDimension name="Time"/>
<VirtualCubeDimension cubeName="Sales" name="Yearly Income"/>
<VirtualCubeDimension cubeName="Warehouse" name="Warehouse"/>
<VirtualCubeMeasure cubeName="Sales" name="[Measures].[Sales Count]"/
>
<VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Cost]"/
>
<VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Sales]"/
>
<VirtualCubeMeasure cubeName="Sales" name="[Measures].[Unit Sales]"/
>
<VirtualCubeMeasure cubeName="Sales" name="[Measures].[Profit]"/>
<VirtualCubeMeasure cubeName="Sales" name="[Measures].[Profit
Growth]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Store
Invoice]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Supply
Time]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Units
Ordered]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Units
Shipped]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse
Cost]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse
Profit]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse
Sales]"/>
<VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Average
Warehouse Sale]"/>
<!--
<VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Sales
Net]"/>
-->
<CalculatedMember name="Profit Per Unit Shipped"
dimension="Measures">
<Formula>[Measures].[Profit] / [Measures].[Units Shipped]</
Formula>
</CalculatedMember>
</VirtualCube>

<!-- A California manager can only see customers and stores in
California.
They cannot drill down on Gender. -->
<Role name="California manager">
<SchemaGrant access="none">
<CubeGrant cube="Sales" access="all">
<HierarchyGrant hierarchy="[Store]" access="custom"
topLevel="[Store].[Store Country]">
<MemberGrant member="[Store].[USA].[CA]" access="all"/>
<MemberGrant member="[Store].[USA].[CA].[Los Angeles]"
access="none"/>
</HierarchyGrant>
<HierarchyGrant hierarchy="[Customers]" access="custom"
topLevel="[Customers].[State Province]"
bottomLevel="[Customers].[City]">
<MemberGrant member="[Customers].[USA].[CA]" access="all"/>
<MemberGrant member="[Customers].[USA].[CA].[Los Angeles]"
access="none"/>
</HierarchyGrant>
<HierarchyGrant hierarchy="[Gender]" access="none"/>
</CubeGrant>
</SchemaGrant>
</Role>

<Role name="No HR Cube">
<SchemaGrant access="all">
<CubeGrant cube="HR" access="none"/>
</SchemaGrant>
</Role>

</Schema>

Feris Thia

unread,
Mar 29, 2009, 11:08:59 AM3/29/09
to phi-mond...@googlegroups.com
Hi Jchadwic,

Your scheme seems ok, have you populated your database properly ? Do you setup using MySQL ?

Can you please following guide in http://pentaho-en.phi-integration.com/mondrian/mysql-check-foodmart-setup and give us some results ?


On Sun, Mar 29, 2009 at 9:53 PM, jcha...@gmail.com <jcha...@gmail.com> wrote:
<?xml version="1.0"?>
<Schema name="FoodMart">



--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483
Mobile : +628176-474-525
http://www.phi-integration.com
http://pentaho.phi-integration.com

jcha...@gmail.com

unread,
Mar 29, 2009, 11:14:01 AM3/29/09
to PHI's Mondrian OLAP Group
Yes, I'm using MySQL, and I have copied the showtables.jsp file over
and here is the output from hitting that URL:

account (11)
agg_c_10_sales_fact_1997 (12)
agg_c_14_sales_fact_1997 (86805)
agg_c_special_sales_fact_1997 (86805)
agg_g_ms_pcat_sales_fact_1997 (2637)
agg_l_03_sales_fact_1997 (20522)
agg_l_04_sales_fact_1997 (323)
agg_l_05_sales_fact_1997 (86154)
agg_lc_100_sales_fact_1997 (86602)
agg_ll_01_sales_fact_1997 (86829)
agg_pl_01_sales_fact_1997 (86829)
category (4)
currency (72)
customer (10281)
customer_sales (5581)
days (7)
department (12)
employee (1155)
employee_closure (7179)
expense_fact (2400)
inventory_fact_1997 (4070)
inventory_fact_1998 (7282)
monthly_profit (36)
position (18)
product (1560)
product_class (110)
product_sales (1559)
promotion (1864)
promotion_sales (148)
region (110)
reserve_employee (143)
salary (21252)
sales_fact_1997 (86837)
sales_fact_1998 (164558)
sales_fact_dec_1998 (18325)
store (25)
store_ragged (25)
time_by_day (730)
warehouse (24)
warehouse_class (6)

On Mar 29, 11:08 am, Feris Thia <fe...@phi-integration.com> wrote:
> Hi Jchadwic,
> Your scheme seems ok, have you populated your database properly ? Do you
> setup using MySQL ?
>
> Can you please following guide inhttp://pentaho-en.phi-integration.com/mondrian/mysql-check-foodmart-s...
> give us some results ?
>

Feris Thia

unread,
Mar 29, 2009, 11:34:21 AM3/29/09
to phi-mond...@googlegroups.com
Can you try to replace your Foodmart.xml with mine attached in this email ? See if there still any error ?


On Sun, Mar 29, 2009 at 10:14 PM, jcha...@gmail.com <jcha...@gmail.com> wrote:
Yes, I'm using MySQL, and I have copied the showtables.jsp file over
and here is the output from hitting that URL:



--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483
FoodMart.xml

jcha...@gmail.com

unread,
Mar 29, 2009, 11:46:55 AM3/29/09
to PHI's Mondrian OLAP Group
Yes, it still produces the same error.

Here is the whole stack trace

JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException:
javax.servlet.jsp.JspException: org.apache.jasper.JasperException:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
mondrian.olap.MondrianException: Mondrian Error:Internal error: Can
not find Default Member with name "[Time.Weekly].[All Time.Weeklys].
[1997]" in Hierarchy "Time.Weekly"

org.apache.jasper.JasperException: javax.servlet.ServletException:
javax.servlet.jsp.JspException: org.apache.jasper.JasperException:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
mondrian.olap.MondrianException: Mondrian Error:Internal error: Can
not find Default Member with name "[Time.Weekly].[All Time.Weeklys].
[1997]" in Hierarchy "Time.Weekly"
at org.apache.jasper.servlet.JspServletWrapper.handleJspException
(JspServletWrapper.java:522)
at org.apache.jasper.servlet.JspServletWrapper.service
(JspServletWrapper.java:398)
at org.apache.jasper.servlet.JspServlet.serviceJspFile
(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:206)
at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest
(RequestFilter.java:139)
at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest
(RequestSynchronizer.java:127)
at com.tonbeller.wcf.controller.RequestFilter.doFilter
(RequestFilter.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke
(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke
(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke
(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke
(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke
(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service
(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process
(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol
$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:
447)
at java.lang.Thread.run(Thread.java:619)
Caused by: javax.servlet.ServletException:
javax.servlet.jsp.JspException: org.apache.jasper.JasperException:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
mondrian.olap.MondrianException: Mondrian Error:Internal error: Can
not find Default Member with name "[Time.Weekly].[All Time.Weeklys].
[1997]" in Hierarchy "Time.Weekly"
at org.apache.jasper.runtime.PageContextImpl.doHandlePageException
(PageContextImpl.java:848)
at org.apache.jasper.runtime.PageContextImpl.handlePageException
(PageContextImpl.java:781)
at org.apache.jsp.testpage_jsp._jspService(testpage_jsp.java:263)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service
(JspServletWrapper.java:374)
... 20 more
Caused by: org.apache.jasper.JasperException:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
mondrian.olap.MondrianException: Mondrian Error:Internal error: Can
not find Default Member with name "[Time.Weekly].[All Time.Weeklys].
[1997]" in Hierarchy "Time.Weekly"
at org.apache.jasper.servlet.JspServletWrapper.handleJspException
(JspServletWrapper.java:522)
at org.apache.jasper.servlet.JspServletWrapper.service
(JspServletWrapper.java:398)
at org.apache.jasper.servlet.JspServlet.serviceJspFile
(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke
(ApplicationDispatcher.java:630)
at org.apache.catalina.core.ApplicationDispatcher.doInclude
(ApplicationDispatcher.java:535)
at org.apache.catalina.core.ApplicationDispatcher.include
(ApplicationDispatcher.java:472)
at com.tonbeller.wcf.utils.IncludeTag.doStartTag(IncludeTag.java:59)
at org.apache.jsp.testpage_jsp._jspx_meth_wcf_005finclude_005f0
(testpage_jsp.java:286)
at org.apache.jsp.testpage_jsp._jspService(testpage_jsp.java:127)
... 23 more
Caused by: javax.servlet.ServletException:
javax.servlet.jsp.JspException: mondrian.olap.MondrianException:
Mondrian Error:Internal error: Can not find Default Member with name
"[Time.Weekly].[All Time.Weeklys].[1997]" in Hierarchy "Time.Weekly"
at org.apache.jasper.runtime.PageContextImpl.doHandlePageException
(PageContextImpl.java:848)
at org.apache.jasper.runtime.PageContextImpl.handlePageException
(PageContextImpl.java:781)
at org.apache.jsp.WEB_002dINF.queries.mondrian_jsp._jspService
(mondrian_jsp.java:90)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service
(JspServletWrapper.java:374)
... 34 more
Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal
error: Can not find Default Member with name "[Time.Weekly].[All
Time.Weeklys].[1997]" in Hierarchy "Time.Weekly"
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:
782)
at mondrian.olap.Util.newInternal(Util.java:1428)
at mondrian.rolap.RolapHierarchy.init(RolapHierarchy.java:248)
at mondrian.rolap.RolapCubeHierarchy.init(RolapCubeHierarchy.java:
315)
at mondrian.rolap.RolapDimension.init(RolapDimension.java:158)
at mondrian.rolap.RolapCube.init(RolapCube.java:1028)
at mondrian.rolap.RolapCube.(RolapCube.java:295)
at mondrian.rolap.RolapSchema.load(RolapSchema.java:453)
at mondrian.rolap.RolapSchema.load(RolapSchema.java:346)
at mondrian.rolap.RolapSchema.(RolapSchema.java:236)
at mondrian.rolap.RolapSchema.(RolapSchema.java:76)
at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:980)
at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:789)
at mondrian.rolap.RolapConnection.(RolapConnection.java:186)
at mondrian.rolap.RolapConnection.(RolapConnection.java:117)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:110)
at com.tonbeller.jpivot.mondrian.MondrianModel.initialize
(MondrianModel.java:518)
at com.tonbeller.jpivot.olap.model.OlapModelDecorator.initialize
(OlapModelDecorator.java:132)
at com.tonbeller.jpivot.tags.OlapModelProxy$MyState.initialize
(OlapModelProxy.java:77)
at com.tonbeller.jpivot.tags.StackStateManager.initializeAndShow
(StackStateManager.java:76)
at com.tonbeller.jpivot.tags.OlapModelProxy.initializeAndShow
(OlapModelProxy.java:160)
at com.tonbeller.jpivot.tags.OlapModelTag.doEndTag(OlapModelTag.java:
81)
at
org.apache.jsp.WEB_002dINF.queries.mondrian_jsp._jspx_meth_jp_005fmondrianQuery_005f0
(mondrian_jsp.java:136)
at org.apache.jsp.WEB_002dINF.queries.mondrian_jsp._jspService
(mondrian_jsp.java:78)
... 37 more



On Mar 29, 11:34 am, Feris Thia <fe...@phi-integration.com> wrote:
> Can you try to replace your Foodmart.xml with mine attached in this email ?
> See if there still any error ?
>
> On Sun, Mar 29, 2009 at 10:14 PM, jchad...@gmail.com <jchad...@gmail.com>wrote:
>
> > Yes, I'm using MySQL, and I have copied the showtables.jsp file over
> > and here is the output from hitting that URL:
>
> --
> Thanks & Best Regards,
>
> Feris Thia
> Business Intelligence Consultant
> PT. Putera Handal Indotama
> Phone  : +6221-30119353
> Fax      : +6221-5513483
> Mobile : +628176-474-525http://www.phi-integration.comhttp://pentaho.phi-integration.com
>
>  FoodMart.xml
> 42KViewDownload

Feris Thia

unread,
Mar 29, 2009, 11:55:06 AM3/29/09
to phi-mond...@googlegroups.com
Hm... quite strange,

Ok.. can you replace  ' defaultMember="[Time.Weekly].[All Time.Weeklys].[1997]" ' part in your Foodmart.xml into ' defaultMember="[Time.Weekly].[All Time.Weeklys]" ' instead ? 

And what version of Mondrian are you trying on ?

On Sun, Mar 29, 2009 at 10:46 PM, jcha...@gmail.com <jcha...@gmail.com> wrote:
Yes, it still produces the same error.

Here is the whole stack trace



--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483

jcha...@gmail.com

unread,
Mar 29, 2009, 12:01:51 PM3/29/09
to PHI's Mondrian OLAP Group
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException:
javax.servlet.jsp.JspException: org.apache.jasper.JasperException:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
com.tonbeller.jpivot.olap.model.OlapException: Mondrian Error:MDX
object '[Time].[1997]' not found in cube 'Sales'

I'm using the current version of Mondrian available from the Pentaho
website

On Mar 29, 11:55 am, Feris Thia <fe...@phi-integration.com> wrote:
> Hm... quite strange,
> Ok.. can you replace  ' defaultMember="[Time.Weekly].[All Time.Weeklys].[1997]"
> ' part in your Foodmart.xml into ' defaultMember="[Time.Weekly].[All
> Time.Weeklys]" ' instead ?
>
> And what version of Mondrian are you trying on ?
>

Feris Thia

unread,
Mar 29, 2009, 12:06:04 PM3/29/09
to phi-mond...@googlegroups.com
Hm..

Now it returns different error... This tell us that there is no 1997 member in your Time Dimension. Weird...

To resolve this MDX error, open your [MONDRIAN]/WEB-INF/queries/mondrian.jsp and fix the following query :

select
  {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
  {([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])

Into

select
  {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
  {([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales

Once you succeed, try to learn how to filter from JPivot OLAP Navigator to change slicer value. But make sure you are succeed in viewing this cube first :)

On Sun, Mar 29, 2009 at 11:01 PM, jcha...@gmail.com <jcha...@gmail.com> wrote:
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException:
javax.servlet.jsp.JspException: org.apache.jasper.JasperException:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
com.tonbeller.jpivot.olap.model.OlapException: Mondrian Error:MDX
object '[Time].[1997]' not found in cube 'Sales'



--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483

Feris Thia

unread,
Mar 29, 2009, 12:20:34 PM3/29/09
to phi-mond...@googlegroups.com
HI JChadwic,

Have you resolved this after my last reply ? 

On Sun, Mar 29, 2009 at 11:01 PM, jcha...@gmail.com <jcha...@gmail.com> wrote:
Pivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException:
javax.servlet.jsp.JspException: org.apache.jasper.JasperException:
javax.servlet.ServletException: javax.servlet.jsp.JspException:
com.tonbeller.jpivot.olap.model.OlapException: Mondrian Error:MDX
object '[Time].[1997]' not found in cube 'Sales'

--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483

jcha...@gmail.com

unread,
Mar 29, 2009, 12:30:34 PM3/29/09
to PHI's Mondrian OLAP Group
Yes, that got the cube to appear! But I am wondering, why is there no
1997 member in my time dimension? Is there something I can do to
correct this.

I'm not going to be the end user of this system. I am a linux server
admin for my university, and I was tasked with installing mondrian as
the university is interested in using it for their data warehouse
work, and I just need to get the samples all working so the DB folks
can take a look at it.

I can't tell you how grateful I am for your help, I have to go to work
now at my other job, but, may I continue to post here with additional
questions?

On Mar 29, 12:20 pm, Feris Thia <fe...@phi-integration.com> wrote:
> HI JChadwic,
> Have you resolved this after my last reply ?
>

Feris Thia

unread,
Mar 29, 2009, 12:35:23 PM3/29/09
to phi-mond...@googlegroups.com
Hi JChadwic,

On Sun, Mar 29, 2009 at 11:30 PM, jcha...@gmail.com <jcha...@gmail.com> wrote:
Yes, that got the cube to appear! But I am wondering, why is there no
1997 member in my time dimension? Is there something I can do to
correct this.
 
I also wondering... There may be some bug or incorrect configuration. If you may specify more detail on your Linux and Mondrian configuration and  send me personally your foodmart's script - generated from mysqldump - to me. I see if I can troubleshoot the problem.
 

I'm not going to be the end user of this system. I am a linux server
admin for my university, and I was tasked with installing mondrian as
the university is interested in using it for their data warehouse
work, and I just need to get the samples all working so the DB folks
can take a look at it.

I understand..
 


I can't tell you how grateful I am for your help, I have to go to work
now at my other job, but, may I continue to post here with additional
questions?

I'm also feel grateful to help. Yes, you may continue to post your problems and see if we can help.

Btw.. what's your real name ? :)


--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483

jcha...@gmail.com

unread,
Mar 29, 2009, 1:38:42 PM3/29/09
to PHI's Mondrian OLAP Group
Jason is the real name, chadwic is the middle = jchadwic!

Anyways, the person I inherited this project from installed the
FoodMart dataset from the Jaspersoft JasperServer suite. Is it
possible there is a difference in the dimension tables that could be
causing a problem?

I'm fairly sure that my boss won't want to remove that version of Food
Mart since he is still evaluating the JasperServer BI suite, could I
add any differing data to the database to get mondrian working with
the existing db setup?

When I get to the University tomorrow, I will do a dump of the
existing db and send it on to you.

On Mar 29, 12:35 pm, Feris Thia <fe...@phi-integration.com> wrote:
> Hi JChadwic,

Feris Thia

unread,
Mar 29, 2009, 8:29:39 PM3/29/09
to phi-mond...@googlegroups.com
Hi Jason,

On Mon, Mar 30, 2009 at 12:38 AM, jcha...@gmail.com <jcha...@gmail.com> wrote:
Jason is the real name, chadwic is the middle = jchadwic!

Nice to know you Jason !
 


Anyways, the person I inherited this project from installed the
FoodMart dataset from the Jaspersoft JasperServer suite. Is it
possible there is a difference in the dimension tables that could be
causing a problem?

I think that should be okay if you inherited from JasperServer installation as it also use Mondrian as the OLAP engine :)
 


I'm fairly sure that my boss won't want to remove that version of Food
Mart since he is still evaluating the JasperServer BI suite, could I
add any differing data to the database to get mondrian working with
the existing db setup?

When I get to the University tomorrow, I will do a dump of the
existing db and send it on to you.

Will wait for it then to further investigate the problem. 



--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483

jcha...@gmail.com

unread,
Mar 29, 2009, 10:03:31 PM3/29/09
to PHI's Mondrian OLAP Group
Ok, I've had a chance to do a SQL dump of the version of the foodmart
data that was installed from JasperServer.

Had to upload it to my server since campus stuff isn't available off
the internal network.

The link is here:

http://www.unsayablejazer.com/foodmart.sql

Thanks again for all the help you've been.

On Mar 29, 8:29 pm, Feris Thia <fe...@phi-integration.com> wrote:
> Hi Jason,

Feris Thia

unread,
Mar 29, 2009, 10:07:38 PM3/29/09
to phi-mond...@googlegroups.com
Hi Jason,

Wow... you should zipped it first before you upload :p

But it's ok... I'm downloading it now..

On Mon, Mar 30, 2009 at 9:03 AM, jcha...@gmail.com <jcha...@gmail.com> wrote:
Ok, I've had a chance to do a SQL dump of the version of the foodmart
data that was installed from JasperServer.

Had to upload it to my server since campus stuff isn't available off
the internal network.

The link is here:

http://www.unsayablejazer.com/foodmart.sql

Thanks again for all the help you've been.

You're welcome. Hope that I can find something here.


--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483

Feris Thia

unread,
Mar 30, 2009, 12:19:40 AM3/30/09
to phi-mond...@googlegroups.com
Hi Jason,

I know your problem now. Your time dimension table is not reliable now. This is what I found from your time_by_day table

mysql> select the_date, the_year from time_by_day limit 0,10;
+---------------------+----------+
| the_date            | the_year |
+---------------------+----------+
| 1997-01-01 00:00:00 |     2006 |
| 1997-01-02 00:00:00 |     2006 |
| 1997-01-03 00:00:00 |     2006 |
| 1997-01-04 00:00:00 |     2006 |
| 1997-01-05 00:00:00 |     2006 |
| 1997-01-06 00:00:00 |     2006 |
| 1997-01-07 00:00:00 |     2006 |
| 1997-01-08 00:00:00 |     2006 |
| 1997-01-09 00:00:00 |     2006 |
| 1997-01-10 00:00:00 |     2006 |
+---------------------+----------+
10 rows in set (0.00 sec)


You see that the_year column should represent numeric year of the_date column. But instead of getting 1997, it returns 2006. So there is never a member of [1997] in your Time Dimension.

Correct your data and you should have all the examples running well.

On Mon, Mar 30, 2009 at 9:03 AM, jcha...@gmail.com <jcha...@gmail.com> wrote:
Ok, I've had a chance to do a SQL dump of the version of the foodmart
data that was installed from JasperServer.

Had to upload it to my server since campus stuff isn't available off
the internal network.

The link is here:

http://www.unsayablejazer.com/foodmart.sql

Thanks again for all the help you've been.

--
Thanks & Best Regards,

Feris Thia
Business Intelligence Consultant
PT. Putera Handal Indotama
Phone  : +6221-30119353
Fax      : +6221-5513483
Reply all
Reply to author
Forward
0 new messages