AnsweredAssumed Answered

filter by dimension snowflake schema

Question asked by Misha Smirnov on Sep 11, 2018
Latest reply on Sep 12, 2018 by Ricardo Diaz

Hello!

I have the problem and I don't know how to solve this..

I have a db, this is part of it:

db_schema.png

And I've created a Mondrian schema for this (snowflake). This is a part of my schema:

<Cube name="fact_document_control" visible="true" cache="true" enabled="true">
    <Table name="fact_document_control">
    </Table>
    <DimensionUsage source="org_struct_fake" name="org_struct_fake" caption="&#1054;&#1088;&#1075;&#1089;&#1090;&#1088;&#1091;&#1082;&#1090;&#1091;&#1088;&#1072;" visible="true" foreignKey="org_struct_id" highCardinality="false">
    </DimensionUsage>
    <DimensionUsage source="exec_state" name="exec_state" caption="&#1057;&#1086;&#1089;&#1090;&#1086;&#1103;&#1085;&#1080;&#1077; &#1080;&#1089;&#1087;&#1086;&#1083;&#1085;&#1077;&#1085;&#1080;&#1103; &#1076;&#1086;&#1082;&#1091;&#1084;&#1077;&#1085;&#1090;&#1086;&#1074;" visible="true" foreignKey="exec_state_id" highCardinality="false">
    </DimensionUsage>
    <Dimension type="StandardDimension" visible="true" foreignKey="document_id" highCardinality="false" name="control_state" caption="&#1055;&#1086;&#1089;&#1090;&#1072;&#1085;&#1086;&#1074;&#1082;&#1072; &#1085;&#1072; &#1082;&#1086;&#1085;&#1090;&#1088;&#1086;&#1083;&#1100;">
        <Hierarchy name="control_state" visible="true" hasAll="true" allMemberName="all" allMemberCaption="&#1042;&#1089;&#1077;" allLevelName="&#1042;&#1089;&#1077;" primaryKey="id" primaryKeyTable="document" caption="&#1055;&#1086;&#1089;&#1090;&#1072;&#1085;&#1086;&#1074;&#1082;&#1072; &#1085;&#1072; &#1082;&#1086;&#1085;&#1090;&#1088;&#1086;&#1083;&#1100;">
            <Join leftAlias="document" leftKey="control_state_id" rightAlias="dim_control_state" rightKey="id">
                <Table name="document" alias="document">
                </Table>
                <Table name="dim_control_state" alias="dim_control_state">
                </Table>
            </Join>
            <Level name="control_state" visible="true" table="dim_control_state" column="id" nameColumn="name" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="&#1055;&#1086;&#1089;&#1090;&#1072;&#1085;&#1086;&#1074;&#1082;&#1072; &#1085;&#1072; &#1082;&#1086;&#1085;&#1090;&#1088;&#1086;&#1083;&#1100;">
            </Level>
        </Hierarchy>
    </Dimension>
    <DimensionUsage source="document_date" name="document_date" caption="&#1044;&#1072;&#1090;&#1072; &#1089;&#1086;&#1079;&#1076;&#1072;&#1085;&#1080;&#1103;" visible="true" foreignKey="document_id" highCardinality="false">
    </DimensionUsage>
    <Measure name="count" column="document_id" aggregator="distinct-count" caption="&#1050;&#1086;&#1083;-&#1074;&#1086;" visible="true">
    </Measure>
</Cube>
<Dimension type="TimeDimension" visible="true" highCardinality="false" name="document_date" caption="&#1044;&#1072;&#1090;&#1072; &#1089;&#1086;&#1079;&#1076;&#1072;&#1085;&#1080;&#1103;">
    <Hierarchy name="document_date" visible="true" hasAll="true" allMemberName="all" allMemberCaption="&#1042;&#1089;&#1077;" allLevelName="&#1042;&#1089;&#1077;" primaryKey="id" primaryKeyTable="document" caption="&#1044;&#1072;&#1090;&#1072; &#1089;&#1086;&#1079;&#1076;&#1072;&#1085;&#1080;&#1103;">
        <Join leftAlias="document" leftKey="cdate" rightAlias="dim_date" rightKey="id">
            <Table name="document" alias="document">
            </Table>
            <Table name="dim_date" alias="dim_date">
            </Table>
        </Join>
        <Level name="year" visible="true" table="dim_date" column="year" nameColumn="year" type="Integer" internalType="int" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never" caption="&#1043;&#1086;&#1076;">
        </Level>
        <Level name="semester" visible="true" table="dim_date" column="semester" type="Integer" internalType="int" uniqueMembers="false" levelType="TimeHalfYears" hideMemberIf="Never" caption="&#1055;&#1086;&#1083;&#1091;&#1075;&#1086;&#1076;&#1080;&#1077;" captionColumn="semester_name">
        </Level>
        <Level name="quarter" visible="true" table="dim_date" column="quarter" type="Integer" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never" caption="&#1050;&#1074;&#1072;&#1088;&#1090;&#1072;&#1083;" captionColumn="quarter_name">
        </Level>
        <Level name="month" visible="true" table="dim_date" column="month" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never" caption="&#1052;&#1077;&#1089;&#1103;&#1094;" captionColumn="month_name">
        </Level>
        <Level name="day" visible="true" table="dim_date" column="day_of_month" type="Integer" internalType="int" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never" caption="&#1044;&#1077;&#1085;&#1100;" captionColumn="day_of_week">
            <Property name="id" column="id" type="String">
            </Property>
        </Level>
    </Hierarchy>
</Dimension>

In this, I've created dimension and dimension usage in my cube. I've joined table document with table dim_date to be able to filter my measure based on table "fact_document_control" by dim_date (for example filter by year, month and so on).

Working query:

select NON EMPTY {[Measures].[count]} ON COLUMNS,
  NON EMPTY {[org_struct_fake].[all]} ON ROWS
from [fact_document_control]
where {[document_date].[2018].[1].[1].[1].[9]:[document_date].[2018].[2].[3].[7].[1]}

But if I don't have row in table document with date (column "cdate" fk to dim_date) which is selected in "where clause", I won't get data from query.

I understand that in my application I can allow user to pass only "good" dates, but I really want be able to pass all dates.

Please tell what should I read, where to look or how to change my query (or schema).

Thanks!

P.S. Sorry if I've chosen incorrect theme.

Outcomes