MDX Query fails in Petaho BA server

Question

My database connection is MySQL through JDBC, standard innodb. From this, I created a very simple data source with two dimensions and one measure.

The two dimensions are:

• location (string)
• ddate (date/time, at least in Mysql)

The measure is the same "location" element, set to aggregate on count

I am trying to get a fairly simple count of items by location, by day of the week (either mon-sun or 1-7 is fine)

I have tried dozens of variations on the following, including "with member...as..." for extracting the day of week, all met with failure.

SELECT
NON EMPTY Hierarchize({ datepart("d",cdate([diDate.hDate].[mDate]) }) ON COLUMNS,
NON EMPTY {Hierarchize({[diLocation.hLocation].[mLocation].Members})} ON ROWS
FROM [k_olap]

Any help would be greatly appreciated. I've been banging my head against this for hours & hours, and it seems like it should be simple & straightforward for just a single portion of the date without needing to build a full time dimension in the schema, which Pentaho BA doesn't facilitate in the web server.

Here's the XML schema generated by Pentaho BA from the MySQL data source:

<Schema name="k_olap">
  <Dimension name="diDate">
    <Hierarchy name="hDate" hasAll="true" primaryKey="ID">
      <Table name="post" schema="kn"/>
      <Level name="mDate" uniqueMembers="false" column="ddate">
      </Level>
    </Hierarchy>
  </Dimension>
  <Dimension name="diLocation">
    <Hierarchy name="hLocation" hasAll="true" primaryKey="ID">
      <Table name="post" schema="kn"/>
      <Level name="mLocation" uniqueMembers="false" column="location" type="String">
      </Level>
    </Hierarchy>
  </Dimension>
  <Cube name="k_olap">
    <Table name="post" schema="kn"/>
    <DimensionUsage name="diDate" source="diDate" foreignKey="ID"/>
    <DimensionUsage name="diLocation" source="diLocation" foreignKey="ID"/>
    <Measure name="mesLocation" column="location" aggregator="count" formatString="Standard"/>
  </Cube>
</Schema>

Answer 1

Create a proper date dimension and hierarchy which has the day of week as a column in the table, then use that.

See here:

http://type-exit.org/adventures-with-open-source-bi/2010/07/a-simple-date-dimension-for-mondrian-cubes/