[Mondrian] aggregated results

kal stevens kalstevens at gmail.com
Thu Aug 14 18:18:05 EDT 2008


I have the following cube and two MDX queries.

the folder mdx query is returning an aggregated result of "" for campaign,
and the Group query is returning null for the campaign.

I want folder to also return null.

Could someone help me out in determining where the relevant differences
are?  or if you have any recommendations on how I should proceed in
debugging this issue.



-------------------------------------------------CUBE
----------------------------------------------------

    <Cube name="Kwid_Cube">
        <Table name="keyword_instance_fact_ at CUSTOMER_ID@_ at CLIENT_ID@"/>
        <Dimension name="Type" foreignKey="keyword_type">
            <Hierarchy hasAll="true" primaryKey="keyword_type">
                <InlineTable alias="keyword_types">
                    <ColumnDefs>
                        <ColumnDef name="keyword_type" type="String"/>
                    </ColumnDefs>
                    <Rows>
                        <Row>
                            <Value column="keyword_type">BROAD</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_type">PHRASE</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_type">EXACT</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_type">CONTENT</Value>
                        </Row>
                    </Rows>
                </InlineTable>
                <Level name="Type" column="keyword_type" type="String"
levelType="Regular" uniqueMembers="true"/>
            </Hierarchy>
        </Dimension>
        <Dimension name="Status" foreignKey="keyword_status">
            <Hierarchy hasAll="true" primaryKey="keyword_status">
                <InlineTable alias="keyword_statuses">
                    <ColumnDefs>
                        <ColumnDef name="keyword_status" type="String"/>
                    </ColumnDefs>
                    <Rows>
                        <Row>
                            <Value column="keyword_status">ACTIVE</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_status">INACTIVE</Value>
                        </Row>
                        <Row>
                            <Value
column="keyword_status">DISAPPROVED</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_status">DELETED</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_status">PAUSED</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_status">PENDING</Value>
                        </Row>
                        <Row>
                            <Value column="keyword_status">SUBMITTED</Value>
                        </Row>
                    </Rows>
                </InlineTable>
                <Level name="Status" column="keyword_status" type="String"
levelType="Regular" uniqueMembers="true"/>
            </Hierarchy>
        </Dimension>
        <Dimension foreignKey="keyword_instance_dim_id" name="KIs">
            <Hierarchy hasAll="true" allMemberName="All KIs"
primaryKey="keyword_instance_dim_id">
                <Table name="keyword_instance_dim_ at CUSTOMER_ID@_ at CLIENT_ID@"
alias="keyword_instance_dim_ at CUSTOMER_ID@_ at CLIENT_ID@"
                       forcedIndex="i_full_dimension">
                </Table>
                <Level column="publisher_id" levelType="Regular"
name="Publisher" type="Numeric" uniqueMembers="true"/>
                <Level column="client_account_id" levelType="Regular"
name="ClientAccount" type="Numeric" uniqueMembers="false"/>
                <Level column="publisher_campaign_id" levelType="Regular"
name="Campaign" type="Numeric" uniqueMembers="true">
                    <Property name="Status"
column="publisher_campaign_status"/>
                    <Property name="Campaign Name"
column="publisher_campaign_name" type="String"/>
                </Level>
                <Level column="publisher_group_id" levelType="Regular"
name="Group" type="Numeric" uniqueMembers="false">
                    <Property name="Group Name"
column="publisher_group_name" type="String"/>
                    <Property name="Status"
column="publisher_group_status"/>
                </Level>
                <!-- Cannot utilize uniqueMembers for KWs due to zero
keyword_instance_ids being replicated -->
                <Level column="keyword_instance_id" levelType="Regular"
name="Keyword" type="Numeric" uniqueMembers="false">
                    <Property name="Status" column="keyword_status"/>
                    <Property name="Keyword Type" column="keyword_type"/>
                    <Property name="Keyword" column="keyword"
type="String"/>
                    <Property name="Search Text" column="search_text"
type="String"/>
                </Level>
            </Hierarchy>
        </Dimension>
        <Dimension foreignKey="keyword_instance_dim_id" name="Folder">
            <Hierarchy hasAll="true" allMemberName="All Folder"
primaryKey="keyword_instance_dim_id">
                <Table name="keyword_instance_dim_ at CUSTOMER_ID@_ at CLIENT_ID@"
alias="keyword_instance_dim_ at CUSTOMER_ID@_ at CLIENT_ID@"
                       forcedIndex="i_folder_dimension"/>
                <Level column="folder_id" levelType="Regular" name="Folder"
type="Numeric" uniqueMembers="true"/>
                <Level column="publisher_id" levelType="Regular"
name="Publisher" type="Numeric" uniqueMembers="false"/>
                <Level column="client_account_id" levelType="Regular"
name="ClientAccount" type="Numeric" uniqueMembers="true"/>
                <Level column="publisher_group_id" levelType="Regular"
name="Group" type="Numeric" uniqueMembers="false">
                    <Property name="Group Name"
column="publisher_group_name" type="String"/>
                    <Property name="Status"
column="publisher_group_status"/>
                    <Property name="Campaign"
column="publisher_campaign_name" type="String"/>
                </Level>
                <!-- Using uniqueMembers here because zero keyword is not
displayed ever in a folder driven grid -->
                <Level column="keyword_instance_id" levelType="Regular"
name="Keyword" type="Numeric" uniqueMembers="false">
                    <Property name="Status" column="keyword_status"
type="String"/>
                    <Property name="Keyword Type" column="keyword_type"
type="String"/>
                    <Property name="Keyword" column="keyword"
type="String"/>
                    <Property name="Search Text" column="search_text"
type="String"/>
                </Level>
            </Hierarchy>
        </Dimension>
        <!-- The time dimension has multiple hierarchies -->
        <!-- I'm noticing bugs with using the sample table for each, as well
as using a SQL stmt, so breaking them out -->
        <Dimension name="Time" foreignKey="time_id" type="TimeDimension">
            <Hierarchy hasAll="true" allMemberName="All Times"
primaryKey="time_id">
                <Table name="time_by_day_epoch"/>
                <Level name="Year" column="the_year" uniqueMembers="true"
levelType="TimeYears" type="Numeric"/>
                <Level name="Month" column="month_of_year"
uniqueMembers="false" levelType="TimeMonths" type="Numeric"/>
                <Level name="Day" column="day_of_month"
uniqueMembers="false" type="Numeric" levelType="TimeDays"/>
                <Level name="Epoch" column="epoch" uniqueMembers="true"
type="Numeric" levelType="TimeDays">
                    <Property name="Year" column="the_year" type="Numeric"/>
                    <Property name="Month" column="month_of_year"
type="Numeric"/>
                    <Property name="Day" column="day_of_month"
type="Numeric"/>
                </Level>
            </Hierarchy>
            <Hierarchy name="Weekly" hasAll="true" allMemberName="All Times"
primaryKey="time_id">
                <Table name="time_by_day_epoch"/>
                <Level name="Year" column="weekly_the_year" type="Numeric"
uniqueMembers="true" levelType="TimeYears"/>
                <Level name="Week" column="week_of_year"
uniqueMembers="false" type="Numeric" levelType="TimeWeeks"/>
                <Level name="Epoch" column="epoch" uniqueMembers="true"
type="Numeric" levelType="TimeDays"/>
            </Hierarchy>
        </Dimension>
</Cube>








---------------------------------------------------------Folder MDX
query-------------------------------------------------
WITH
MEMBER [Measures].[id]                 AS [Folder].CurrentMember.Name
MEMBER [Measures].[publisher]             AS
Ancestor([Folder].CurrentMember, [Folder].[Publisher]).Name
MEMBER [Measures].[campaign]             AS Ancestor([Folder].CurrentMember,
[Folder].[Group]).Properties("Campaign")
MEMBER [Measures].[group]             AS Ancestor([Folder].CurrentMember,
[Folder].[Group]).Name
MEMBER [Measures].[title]             AS
[Folder].CurrentMember.Properties("Group Name")
MEMBER [Time].[range]                 AS [Time].[Epoch].[1218499200]
MEMBER [Time].[base_range]             AS [Time].[Epoch].[1217894400]
MEMBER [Measures].[impressions_delta_absolute]     AS ([Time].[range],
[Measures].[impressions]) - ([Time].[base_range], [Measures].[impressions])
MEMBER [Measures].[impressions_delta_percent]     AS 100 *
((([Time].[range], [Measures].[impressions]) - ([Time].[base_range],
[Measures].[impressions])) / ([Time].[base_range],
[Measures].[impressions]))
MEMBER [Measures].[pub_clicks_delta_absolute]     AS ([Time].[range],
[Measures].[pub_clicks]) - ([Time].[base_range], [Measures].[pub_clicks])
MEMBER [Measures].[pub_clicks_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[pub_clicks]) - ([Time].[base_range], [Measures].[pub_clicks])) /
([Time].[base_range], [Measures].[pub_clicks]))
MEMBER [Measures].[pub_cost_delta_absolute]     AS ([Time].[range],
[Measures].[pub_cost]) - ([Time].[base_range], [Measures].[pub_cost])
MEMBER [Measures].[pub_cost_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[pub_cost]) - ([Time].[base_range], [Measures].[pub_cost])) /
([Time].[base_range], [Measures].[pub_cost]))
MEMBER [Measures].[conversions_delta_absolute]     AS ([Time].[range],
[Measures].[conversions]) - ([Time].[base_range], [Measures].[conversions])
MEMBER [Measures].[conversions_delta_percent]     AS 100 *
((([Time].[range], [Measures].[conversions]) - ([Time].[base_range],
[Measures].[conversions])) / ([Time].[base_range],
[Measures].[conversions]))
MEMBER [Measures].[revenue_delta_absolute]     AS ([Time].[range],
[Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue])
MEMBER [Measures].[revenue_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue])) /
([Time].[base_range], [Measures].[revenue]))
MEMBER [Measures].[gross_profit_delta_absolute] AS ([Time].[range],
[Measures].[gross_profit]) - ([Time].[base_range],
[Measures].[gross_profit])
MEMBER [Measures].[gross_profit_delta_percent]     AS 100 *
((([Time].[range], [Measures].[gross_profit]) - ([Time].[base_range],
[Measures].[gross_profit])) / ([Time].[base_range],
[Measures].[gross_profit]))
MEMBER [Measures].[profit_delta_absolute]     AS ([Time].[range],
[Measures].[profit]) - ([Time].[base_range], [Measures].[profit])
MEMBER [Measures].[profit_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[profit]) - ([Time].[base_range], [Measures].[profit])) /
([Time].[base_range], [Measures].[profit]))  SET [Requested] AS
FILTER(Descendants([Folder].[Folder].[126], [Folder].[Group]),
CAST([Folder].[Group].CurrentMember.Key AS NUMERIC) > 0 )
MEMBER [Measures].[total_available_count]     AS
Format(COUNT(FILTER(Descendants([Folder].[Folder].[126], [Folder].[Group]),
CAST([Folder].[Group].CurrentMember.Key AS NUMERIC) > 0 )), "#####")
MEMBER [Measures].[total_result_count]         AS Format(COUNT([Requested]),
"#####")
MEMBER [Folder].[Aggregated]             AS [Folder].[Folder].[126]
MEMBER [Folder].[Content]             AS [Folder].[Folder].[0]
MEMBER [Folder].[Unattributed]             AS [Folder].[Folder].[0]

SELECT {[Measures].allMembers} ON COLUMNS, {
                        [Folder].[Aggregated],
                        [Folder].[Content],
                        [Folder].[Unattributed],
                        LimitSet(Order([Requested], [Measures].[revenue],
BDESC), 0, 1)
                    }
ON ROWS FROM Kwid_Cube WHERE {([Time].[range] )}

















-------------------------------------------------------------Group Mdx
Query-----------------------------------------------------

WITH
MEMBER [Measures].[id]                 AS [KIs].CurrentMember.Name
MEMBER [Measures].[publisher]             AS Ancestor([KIs].CurrentMember,
[KIs].[Publisher]).Name
MEMBER [Measures].[campaign]             AS Ancestor([KIs].CurrentMember,
[KIs].[Campaign]).Name
MEMBER [Measures].[group]             AS Ancestor([KIs].CurrentMember,
[KIs].[Group]).Name
MEMBER [Measures].[title]             AS
[KIs].CurrentMember.Properties("Group Name")
MEMBER [Time].[range]                 AS [Time].[Epoch].[1218499200]
MEMBER [Time].[base_range]             AS [Time].[Epoch].[1217894400]
MEMBER [Measures].[impressions_delta_absolute]     AS ([Time].[range],
[Measures].[impressions]) - ([Time].[base_range], [Measures].[impressions])
MEMBER [Measures].[impressions_delta_percent]     AS 100 *
((([Time].[range], [Measures].[impressions]) - ([Time].[base_range],
[Measures].[impressions])) / ([Time].[base_range],
[Measures].[impressions]))
MEMBER [Measures].[pub_clicks_delta_absolute]     AS ([Time].[range],
[Measures].[pub_clicks]) - ([Time].[base_range], [Measures].[pub_clicks])
MEMBER [Measures].[pub_clicks_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[pub_clicks]) - ([Time].[base_range], [Measures].[pub_clicks])) /
([Time].[base_range], [Measures].[pub_clicks]))
MEMBER [Measures].[pub_cost_delta_absolute]     AS ([Time].[range],
[Measures].[pub_cost]) - ([Time].[base_range], [Measures].[pub_cost])
MEMBER [Measures].[pub_cost_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[pub_cost]) - ([Time].[base_range], [Measures].[pub_cost])) /
([Time].[base_range], [Measures].[pub_cost]))
MEMBER [Measures].[conversions_delta_absolute]     AS ([Time].[range],
[Measures].[conversions]) - ([Time].[base_range], [Measures].[conversions])
MEMBER [Measures].[conversions_delta_percent]     AS 100 *
((([Time].[range], [Measures].[conversions]) - ([Time].[base_range],
[Measures].[conversions])) / ([Time].[base_range],
[Measures].[conversions]))
MEMBER [Measures].[revenue_delta_absolute]     AS ([Time].[range],
[Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue])
MEMBER [Measures].[revenue_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue])) /
([Time].[base_range], [Measures].[revenue]))
MEMBER [Measures].[gross_profit_delta_absolute] AS ([Time].[range],
[Measures].[gross_profit]) - ([Time].[base_range],
[Measures].[gross_profit])
MEMBER [Measures].[gross_profit_delta_percent]     AS 100 *
((([Time].[range], [Measures].[gross_profit]) - ([Time].[base_range],
[Measures].[gross_profit])) / ([Time].[base_range],
[Measures].[gross_profit]))
MEMBER [Measures].[profit_delta_absolute]     AS ([Time].[range],
[Measures].[profit]) - ([Time].[base_range], [Measures].[profit])
MEMBER [Measures].[profit_delta_percent]     AS 100 * ((([Time].[range],
[Measures].[profit]) - ([Time].[base_range], [Measures].[profit])) /
([Time].[base_range], [Measures].[profit]))  SET [Requested] AS
FILTER([KIs].[Group].Members, CAST([KIs].[Group].CurrentMember.Key AS
NUMERIC) > 0 )
MEMBER [Measures].[total_available_count]     AS
Format(COUNT(FILTER([KIs].[Group].Members,
CAST([KIs].[Group].CurrentMember.Key AS NUMERIC) > 0 )), "#####")
MEMBER [Measures].[total_result_count]         AS Format(COUNT([Requested]),
"#####")
MEMBER [KIs].[Aggregated]             AS [KIs].[All KIs]
MEMBER [KIs].[Content]                 AS [KIs].[Campaign].[0]
MEMBER [KIs].[Unattributed]             AS [KIs].[Campaign].[0]

SELECT {[Measures].allMembers} ON COLUMNS, {
                        [KIs].[Aggregated],
                        [KIs].[Content],
                        [KIs].[Unattributed],
                        LimitSet(Order([Requested], [Measures].[revenue],
BDESC), 0, 20)
                    }
ON ROWS FROM Kwid_Cube WHERE {([Time].[range] )}
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20080814/a1da40a3/attachment.html 


More information about the Mondrian mailing list