Help -Separate Dimensions of Year,Month,Week & Day

[maheswari]

Hello All,
Am a newbie to OLAP. I created a separate dimesions for Year,Month,Week & Day. Here in Dimension Week, its taking all the weeks for all Months. Actually i wanted to take only the particular week numbers for the month. can some one help me?

Code:

My Schema is :***************<Schema name="openreports"><Dimension name="Store"><Hierarchy hasAll="true" allMemberName="All Store" primaryKey="id"><Table name="shop"/><Level name="Name" column="name" type="String" uniqueMembers="true"/></Hierarchy></Dimension><Dimension name="Time Year"><Hierarchy hasAll="true" allMemberName="Year" primaryKey="id"><Table name="time"/><Level name="Year" column="year" type="Numeric" uniqueMembers="true"/></Hierarchy></Dimension><Dimension name="Time Month"><Hierarchy hasAll="true" allMemberName="Month" primaryKey="id"><Table name="time"/><Level name="Month" column="month" uniqueMembers="false" type="Numeric"/></Hierarchy></Dimension><Dimension name="Time Week"><Hierarchy hasAll="true" allMemberName="Week" primaryKey="id"><Table name="time"/><Level name="Week" column="week" type="Numeric" uniqueMembers="false"/></Hierarchy></Dimension><Dimension name="Time Day"><Hierarchy hasAll="true" allMemberName="Day" primaryKey="id"><Table name="time"/><Level name="Day" column="day" uniqueMembers="false" type="Numeric"/></Hierarchy></Dimension><Cube name="Sales"><Table name="sales"/><DimensionUsage name="Store" source="Store" foreignKey="store_id"/><DimensionUsage name="Time Year" source="Time Year" foreignKey="time_id"/><DimensionUsage name="Time Month" source="Time Month" foreignKey="time_id"/><DimensionUsage name="Time Week" source="Time Week" foreignKey="time_id"/><DimensionUsage name="Time Day" source="Time Day" foreignKey="time_id"/><Measure name="Gross" column="gross" aggregator="sum"/><Measure name="Qty" column="qty" aggregator="sum"/></Cube></Schema>MDX Query is :***************select {[Measures].[Gross],[Measures].[Qty]} on COLUMNS,{([store].[All Store],[Time Year].[Time Year],[Time Month].[Time Month],[Time Week].[Time Week],[Time Day].[Time Day])} on ROWSFROM [sales]  

[rodriguezum]

if you want ot aggregate weeks in months you have to create a hierarchy wich has the level month and then the level week, you can also define a hierarchie that starts with the level year >> month >> week >> day

 

lets say something like this

 

 

 

<Hierarchy name="AQM" hasAll="true" allMemberName="AQM" primaryKey="DIM_TIME">

<Table name="****" schema="*****">

</Table>

<Level name="YEAR" column="YEAR" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">

</Level>

<Level name="QUARTER" column="QUARTER" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">

</Level>

<Level name="MONTH" column="MONTH_NAME" ordinalColumn="MONTH_OF_YEAR" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">

</Level>

<Level name="WEEK" column="WEEK_OF_MONTH" type="Numeric" uniqueMembers="false" levelType="TimeWeeks" hideMemberIf="Never">

</Level>

<Level name="DAY" column="DAY_FULL_NAME" ordinalColumn="DAY_OF_WEEK" type="Numeric" uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">

</Level>

</Hierarchy>

[maheswari]

Thanks for your reply rodriguezum.!! But what am looking is, i want to create seperate dimension for month,week,day. For example,

Year | Month | Week | Day | Unit Sales

 2008|  10      |  44     | 27   |  100

 

I dont want all these things in the same hierarchy. I want it as a separate dimension. is there a way to do that?

[rodriguezum]

i don't see the advantages in having different dimensions for a time dimension but, you problem is in a month week hierarquie, if you had weeks from 1 to 5 i imagine that would work, since the days are working right?, but since the weeks are unique from 1 to 50 something jasper analysis has no way of combining the months to the respective weeks, i don't know if this works but in your weeks dimension you could have the month week number and the year week number, and in the ordinal column have the month week number and in the column have the year week number, this in the mondrian schema.

 

hope it helps, i'm struggling with jasper analysis, server, ireport also...