View Full Version : question re: dimensions..

09-24-2003, 02:21 AM
I have a table with a column containing a date. I need to have a dimension for the year. Is is possible to set up the connection with the dimension (in the cube) with the key as (pseudocode follows) foreignKey="YEAR_PART(date)", which would link to the primaryKey="yearnumber" in the dimension?? Is there some other way to do this?

09-24-2003, 05:53 AM
I considered adding machinery to mondrian to deal with cases like this, and decided it was better -- simpler and more efficient -- that the RDBMS handle it. Either add a 'year' column to your table, or, if your RDBMS is up to it, create a view ('create view time_with_year as select *, YEAR_PART(date) as year from time' and a functional index on YEAR_PART(date).
Or you can base your dimension on a query construct:
<SQL>select *, YEAR_PART(date) as year from time</SQL>
But then you will suffer because the 'year' access-path is not indexed.

09-24-2003, 09:52 AM
Talk about timing!
I was just about to roll up my sleeves and create a DateSource MemberReader as per the Mondrian documentation. This approach with the view/index is so much simpler, it's ridiculous.
What follows here is a complete solution for Postgres - as Julian points out, it should be easy to convert this for other RDBMSs.
CREATE VIEW public.timeprocedurefact AS SELECT to_char((procedurefact.date)::timestamp with time zone, 'YYYY'::text) AS "year", ('Q'::text || to_char((procedurefact.date)::timestamp with time zone, 'Q'::text)) AS quarter, to_char((procedurefact.date)::timestamp with time zone, 'Month'::text) AS month_name, to_char((procedurefact.date)::timestamp with time zone, 'MM'::text) AS "month", ('Week '::text || to_char((procedurefact.date)::timestamp with time zone, 'W'::text)) AS week_in_month, date_part('day'::text, procedurefact.date) AS day_of_month, to_char((procedurefact.date)::timestamp with time zone, 'Dy DDth'::text) AS day_name, * FROM procedurefact;
The Cube using the Hierarchy:
<Cube name="CostsByProcedure">
<Table name="timeprocedurefact"/>
<Dimension name="Time">
<Hierarchy hasAll="true" allMemberName="All Periods">
<Level name="Year" column="year" uniqueMembers="true"/>
<Level name="Quarter" column="quarter" uniqueMembers="false"/>
<Level name="Month" column="month" nameColumn="month_name" uniqueMembers="false"/>
<Level name="Week" column="week_in_month" uniqueMembers="false"/>
<Level name="Day" column="day_of_month" nameColumn="day_name" uniqueMembers="false"/>
<DimensionUsage name="Procedure Type" source="Procedure Type" foreignKey="proceduretypeid" />
<DimensionUsage name="Value Type" source="Value Type" foreignKey="valuetypeid" />
<Measure name="Actual" column="actualvalue"
aggregator="avg" formatString="#,###.##"/>
<Measure name="Estimated" column="estimatedvalue"
aggregator="avg" formatString="#,###.##"/>
This displays very nicely in JPivot.
Thanks all for a very rich tool that really has changed the game for myself, my company and my clients.

10-12-2003, 06:16 AM
oh..thank you.... I got myself all tangled up doing that.
Any idea how you would create a similar dimension where some of the levels come from other columns in the table? just add the select part from the other column?
(e.g year/month part from a date, period (1-13 for accounting purposes) from another column - period, but in the same table of course.)
(I'm still trying to get the hang of postgres views..)

10-12-2003, 10:46 AM
Yes, you have understood the pattern. Add columns derived by SQL functions into the view, and then refer to these new columns in the Mondrian schema.