US and Worldwide: +1 (866) 660-7555
Results 1 to 1 of 1

Thread: Date key and time key behaviour strange with measure max value

  1. #1
    Join Date
    Aug 2013
    Posts
    118

    Default Date key and time key behaviour strange with measure max value

    i defined a schema in which fact table contains date key yyymmdd and time key HHmmss and measures with dimension keys

    i defined a measure to get max value say meausre1

    when i select the a day and dimension and measure 1 it give the max value say 1000

    when select hour as well it gives every hour max value 0 to 23

    but the value i am getting for day is of 0th hour(1000) but not actual max value which is of 23 rd hour(9000)

    and when i drill down to minutes every hour shows same minutes and values


    Please have a look at attached screen

    defined date and time as shared dimension as below used as dimension usage with Foreign key with date key and time key


    <Schema name="Electrical_DW_Parameters">
    <Dimension type="TimeDimension" visible="true" highCardinality="false" name="Date">
    <Hierarchy name="Date" visible="true" hasAll="true" primaryKey="date_key">
    <Table name="erm_dim_dat">
    </Table>
    <Level name="Year" visible="true" column="year4" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Month" visible="true" column="year_month_abbreviation" ordinalColumn="month_number" type="String" uniqueMembers="true" levelType="TimeMonths" hideMemberIf="Never">
    </Level>
    <Level name="Day" visible="true" column="date_us" type="String" uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Dimension type="TimeDimension" visible="true" highCardinality="false" name="Time">
    <Hierarchy name="Time" visible="true" hasAll="true" primaryKey="time_key">
    <Table name="erm_dim_time">
    </Table>
    <Level name="Hour of the Day" visible="true" column="hours24" type="String" uniqueMembers="true" levelType="TimeHours" hideMemberIf="Never">
    </Level>
    <Level name="Minutes" visible="true" column="minutes" type="String" uniqueMembers="true" levelType="TimeMinutes" hideMemberIf="Never">
    </Level>
    <Level name="Seconds" visible="true" column="seconds" type="String" uniqueMembers="true" levelType="TimeSeconds" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" highCardinality="false" name="All Meters">
    <Hierarchy name="Meters" visible="true" hasAll="true">
    <Table name="erm_dim_mtr">
    </Table>
    <Level name="Meter" visible="true" column="ID" nameColumn="Meter" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Cube name="Device_DW_Parameters" visible="true" cache="true" enabled="true">
    <Table name="erm_dvc_raw_dt">
    </Table>
    <DimensionUsage source="All Meters" name="Meters" visible="true" foreignKey="ERM_DVC_MTR_CD" highCardinality="false">
    </DimensionUsage>
    <DimensionUsage source="Date" name="Date" visible="true" foreignKey="ERM_DVC_DT_KEY" highCardinality="false">
    </DimensionUsage>
    <DimensionUsage source="Time" name="Time" visible="true" foreignKey="ERM_DVC_TM_KEY" highCardinality="false">
    </DimensionUsage>
    <Measure name="Active Energy Delivered(kWh)" column="ERM_DVC_ACTV_ENRGY_DLVRD" formatString="#.##" aggregator="max">
    </Measure>
    <Measure name="Active Energy Received(kWh)" column="ERM_DVC_ACTV_ENRGY_RCVD" formatString="#.##" aggregator="max">
    </Measure>
    <Measure name="Active Power L1(kW)" column="ERM_DVC_ACTV_PWR_LN1" formatString="#.##" aggregator="max">
    </Measure>
    <Measure name="Active Power L2(kW)" column="ERM_DVC_ACTV_PWR_LN2" formatString="#.##" aggregator="max">
    </Measure>
    <Measure name="Active Power L3(kW)" column="ERM_DVC_ACTV_PWR_LN3" formatString="#.##" aggregator="max">
    </Measure>
    <Measure name="Active Power(kW)" column="ERM_DVC_ACTV_PWR_TLAVRG" formatString="#.##" aggregator="max">
    </Measure>

    </Cube>
    </Schema>

    Untitled1.jpg
    Attached Images Attached Images
    Last edited by suresh84c; 06-04-2014 at 03:27 PM.
    Thank U For Your Time
    Suresh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •