Workbench Give Cube error.

[muttou]

Hi i m making the cube useing the Work bench Analysis using the Monderian Scheema help on Foodmart database.

<Schema>

<Cube name="Sales">

<Table name="sales_fact_1997"/>

<Dimension name="Gender" foreignKey="customer_id">

- 18 -

<Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="customer_id">

<Table name="customer"/>

<Level name="Gender" column="gender" uniqueMembers="true"/>

</Hierarchy>

</Dimension>

<Dimension name="Time" foreignKey="time_id">

<Hierarchy hasAll="false" primaryKey="time_id">

<Table name="time_by_day"/>

<Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/>

<Level name="Quarter" column="quarter" uniqueMembers="false"/>

<Level name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/>

</Hierarchy>

</Dimension>

<Measure name="Unit Sales" column="unit_sales" aggregator="sum"

formatString="#,###"/>

<Measure name="Store Sales" column="store_sales" aggregator="sum"

formatString="#,###.##"/>

<CalculatedMember name="Profit" dimension="Measures" formula="[Measures].

[store Sales]-[Measures].[store Cost]">

<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>

</CalculatedMember>

</Cube>

</Schema>

and after write the query into the MDX Query in Work bench it gives me the error message like

 

Mondrian Error:Failed to parse query 'SELECT {[Measures].[unit Sales], [Measures].[store Sales]} ON COLUMNS,

{[Time].[1997].[Q1].descendants} ON ROWS

FROM [sales]

WHERE [Gender].[F]'

 

Mondrian Error:Error while parsing MDX statement 'SELECT {[Measures].[unit Sales], [Measures].[store Sales]} ON COLUMNS,

{[Time].[1997].[Q1].descendants} ON ROWS

FROM [sales]

WHERE [Gender].[F]'

 

Mondrian Error:MDX object '[Time].[1997].[Q1].[descendants]' not found in cube 'Sales'

 

thanks for solving my porblem and give me proper solution to aviod write MDX query and Schema ... more when add another database using ODBC it doesnt shows the Tables in Workbench and Penthao Cube Designer as well..... Thanks for the Support......Hope for the positive reply.......

Post edited by: muttou, at: 2007/09/19 13:06

[csbac]

Hi!

Does it work if you use the simpler query

 

Code:

SELECT {[Measures].[unit Sales], [Measures].[store Sales]} ON COLUMNS,
{[Time]} ON ROWS
FROM [sales]

?

 

Sebastian

[jshih]

There are two problems with this schema:

 

1. The schema needs a name. Otherwise, it cannot be loaded by the schema editor, e.g., <Schema name="FoodMart">

 

2. 'Store Costs' is used in the CalculatedMember, but not defined as a measure. So, add: <Measure name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00"/>

 

And after the above changes, the query suggested by Sebastian can be run. If you want to fix the original query, change the ‘descendants’ to ‘children’, i.e.,

 

SELECT {[Measures].[unit Sales], [Measures].[store Sales]} ON COLUMNS,

{[Time].[1997].[Q1].children} ON ROWS

FROM [sales]

WHERE [Gender].[F]

[muttou]

dear thanks for the reply with enough examples..... thanks.

[muttou]

dear thanks for the reply with enough examples..... thanks.