<div dir="ltr">I have the following cube and two MDX queries.<br><br>the folder mdx query is returning an aggregated result of "" for campaign, and the Group query is returning null for the campaign.<br><br>I want folder to also return null.<br>
<br>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.<br><br><br><br>-------------------------------------------------CUBE ----------------------------------------------------<br>
<br> <Cube name="Kwid_Cube"><br> <Table name="keyword_instance_fact_@CUSTOMER_ID@_@CLIENT_ID@"/><br> <Dimension name="Type" foreignKey="keyword_type"><br>
<Hierarchy hasAll="true" primaryKey="keyword_type"><br> <InlineTable alias="keyword_types"><br> <ColumnDefs><br> <ColumnDef name="keyword_type" type="String"/><br>
</ColumnDefs><br> <Rows><br> <Row><br> <Value column="keyword_type">BROAD</Value><br> </Row><br>
<Row><br> <Value column="keyword_type">PHRASE</Value><br> </Row><br> <Row><br> <Value column="keyword_type">EXACT</Value><br>
</Row><br> <Row><br> <Value column="keyword_type">CONTENT</Value><br> </Row><br> </Rows><br>
</InlineTable><br> <Level name="Type" column="keyword_type" type="String" levelType="Regular" uniqueMembers="true"/><br> </Hierarchy><br>
</Dimension><br> <Dimension name="Status" foreignKey="keyword_status"><br> <Hierarchy hasAll="true" primaryKey="keyword_status"><br> <InlineTable alias="keyword_statuses"><br>
<ColumnDefs><br> <ColumnDef name="keyword_status" type="String"/><br> </ColumnDefs><br> <Rows><br>
<Row><br> <Value column="keyword_status">ACTIVE</Value><br> </Row><br> <Row><br> <Value column="keyword_status">INACTIVE</Value><br>
</Row><br> <Row><br> <Value column="keyword_status">DISAPPROVED</Value><br> </Row><br>
<Row><br> <Value column="keyword_status">DELETED</Value><br> </Row><br> <Row><br> <Value column="keyword_status">PAUSED</Value><br>
</Row><br> <Row><br> <Value column="keyword_status">PENDING</Value><br> </Row><br> <Row><br>
<Value column="keyword_status">SUBMITTED</Value><br> </Row><br> </Rows><br> </InlineTable><br> <Level name="Status" column="keyword_status" type="String" levelType="Regular" uniqueMembers="true"/><br>
</Hierarchy><br> </Dimension><br> <Dimension foreignKey="keyword_instance_dim_id" name="KIs"><br> <Hierarchy hasAll="true" allMemberName="All KIs" primaryKey="keyword_instance_dim_id"><br>
<Table name="keyword_instance_dim_@CUSTOMER_ID@_@CLIENT_ID@" alias="keyword_instance_dim_@CUSTOMER_ID@_@CLIENT_ID@"<br> forcedIndex="i_full_dimension"><br>
</Table><br> <Level column="publisher_id" levelType="Regular" name="Publisher" type="Numeric" uniqueMembers="true"/><br> <Level column="client_account_id" levelType="Regular" name="ClientAccount" type="Numeric" uniqueMembers="false"/><br>
<Level column="publisher_campaign_id" levelType="Regular" name="Campaign" type="Numeric" uniqueMembers="true"><br> <Property name="Status" column="publisher_campaign_status"/><br>
<Property name="Campaign Name" column="publisher_campaign_name" type="String"/><br> </Level><br> <Level column="publisher_group_id" levelType="Regular" name="Group" type="Numeric" uniqueMembers="false"><br>
<Property name="Group Name" column="publisher_group_name" type="String"/><br> <Property name="Status" column="publisher_group_status"/><br>
</Level><br> <!-- Cannot utilize uniqueMembers for KWs due to zero keyword_instance_ids being replicated --><br> <Level column="keyword_instance_id" levelType="Regular" name="Keyword" type="Numeric" uniqueMembers="false"><br>
<Property name="Status" column="keyword_status"/><br> <Property name="Keyword Type" column="keyword_type"/><br> <Property name="Keyword" column="keyword" type="String"/><br>
<Property name="Search Text" column="search_text" type="String"/><br> </Level><br> </Hierarchy><br> </Dimension><br>
<Dimension foreignKey="keyword_instance_dim_id" name="Folder"><br> <Hierarchy hasAll="true" allMemberName="All Folder" primaryKey="keyword_instance_dim_id"><br>
<Table name="keyword_instance_dim_@CUSTOMER_ID@_@CLIENT_ID@" alias="keyword_instance_dim_@CUSTOMER_ID@_@CLIENT_ID@"<br> forcedIndex="i_folder_dimension"/><br>
<Level column="folder_id" levelType="Regular" name="Folder" type="Numeric" uniqueMembers="true"/><br> <Level column="publisher_id" levelType="Regular" name="Publisher" type="Numeric" uniqueMembers="false"/><br>
<Level column="client_account_id" levelType="Regular" name="ClientAccount" type="Numeric" uniqueMembers="true"/><br> <Level column="publisher_group_id" levelType="Regular" name="Group" type="Numeric" uniqueMembers="false"><br>
<Property name="Group Name" column="publisher_group_name" type="String"/><br> <Property name="Status" column="publisher_group_status"/><br>
<Property name="Campaign" column="publisher_campaign_name" type="String"/><br> </Level><br> <!-- Using uniqueMembers here because zero keyword is not displayed ever in a folder driven grid --><br>
<Level column="keyword_instance_id" levelType="Regular" name="Keyword" type="Numeric" uniqueMembers="false"><br> <Property name="Status" column="keyword_status" type="String"/><br>
<Property name="Keyword Type" column="keyword_type" type="String"/><br> <Property name="Keyword" column="keyword" type="String"/><br>
<Property name="Search Text" column="search_text" type="String"/><br> </Level><br> </Hierarchy><br> </Dimension><br>
<!-- The time dimension has multiple hierarchies --><br> <!-- I'm noticing bugs with using the sample table for each, as well as using a SQL stmt, so breaking them out --><br> <Dimension name="Time" foreignKey="time_id" type="TimeDimension"><br>
<Hierarchy hasAll="true" allMemberName="All Times" primaryKey="time_id"><br> <Table name="time_by_day_epoch"/><br> <Level name="Year" column="the_year" uniqueMembers="true" levelType="TimeYears" type="Numeric"/><br>
<Level name="Month" column="month_of_year" uniqueMembers="false" levelType="TimeMonths" type="Numeric"/><br> <Level name="Day" column="day_of_month" uniqueMembers="false" type="Numeric" levelType="TimeDays"/><br>
<Level name="Epoch" column="epoch" uniqueMembers="true" type="Numeric" levelType="TimeDays"><br> <Property name="Year" column="the_year" type="Numeric"/><br>
<Property name="Month" column="month_of_year" type="Numeric"/><br> <Property name="Day" column="day_of_month" type="Numeric"/><br>
</Level><br> </Hierarchy><br> <Hierarchy name="Weekly" hasAll="true" allMemberName="All Times" primaryKey="time_id"><br> <Table name="time_by_day_epoch"/><br>
<Level name="Year" column="weekly_the_year" type="Numeric" uniqueMembers="true" levelType="TimeYears"/><br> <Level name="Week" column="week_of_year" uniqueMembers="false" type="Numeric" levelType="TimeWeeks"/><br>
<Level name="Epoch" column="epoch" uniqueMembers="true" type="Numeric" levelType="TimeDays"/><br> </Hierarchy><br> </Dimension><br>
</Cube><br><br><br><br><br><br><br><br><br>---------------------------------------------------------Folder MDX query-------------------------------------------------<br>WITH <br>MEMBER [Measures].[id] AS [Folder].CurrentMember.Name <br>
MEMBER [Measures].[publisher] AS Ancestor([Folder].CurrentMember, [Folder].[Publisher]).Name <br>MEMBER [Measures].[campaign] AS Ancestor([Folder].CurrentMember, [Folder].[Group]).Properties("Campaign") <br>
MEMBER [Measures].[group] AS Ancestor([Folder].CurrentMember, [Folder].[Group]).Name <br>MEMBER [Measures].[title] AS [Folder].CurrentMember.Properties("Group Name") <br>MEMBER [Time].[range] AS [Time].[Epoch].[1218499200] <br>
MEMBER [Time].[base_range] AS [Time].[Epoch].[1217894400] <br>MEMBER [Measures].[impressions_delta_absolute] AS ([Time].[range], [Measures].[impressions]) - ([Time].[base_range], [Measures].[impressions]) <br>
MEMBER [Measures].[impressions_delta_percent] AS 100 * ((([Time].[range], [Measures].[impressions]) - ([Time].[base_range], [Measures].[impressions])) / ([Time].[base_range], [Measures].[impressions])) <br>MEMBER [Measures].[pub_clicks_delta_absolute] AS ([Time].[range], [Measures].[pub_clicks]) - ([Time].[base_range], [Measures].[pub_clicks]) <br>
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])) <br>MEMBER [Measures].[pub_cost_delta_absolute] AS ([Time].[range], [Measures].[pub_cost]) - ([Time].[base_range], [Measures].[pub_cost]) <br>
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])) <br>MEMBER [Measures].[conversions_delta_absolute] AS ([Time].[range], [Measures].[conversions]) - ([Time].[base_range], [Measures].[conversions]) <br>
MEMBER [Measures].[conversions_delta_percent] AS 100 * ((([Time].[range], [Measures].[conversions]) - ([Time].[base_range], [Measures].[conversions])) / ([Time].[base_range], [Measures].[conversions])) <br>MEMBER [Measures].[revenue_delta_absolute] AS ([Time].[range], [Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue]) <br>
MEMBER [Measures].[revenue_delta_percent] AS 100 * ((([Time].[range], [Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue])) / ([Time].[base_range], [Measures].[revenue])) <br>MEMBER [Measures].[gross_profit_delta_absolute] AS ([Time].[range], [Measures].[gross_profit]) - ([Time].[base_range], [Measures].[gross_profit]) <br>
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])) <br>MEMBER [Measures].[profit_delta_absolute] AS ([Time].[range], [Measures].[profit]) - ([Time].[base_range], [Measures].[profit]) <br>
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 ) <br>
MEMBER [Measures].[total_available_count] AS Format(COUNT(FILTER(Descendants([Folder].[Folder].[126], [Folder].[Group]), CAST([Folder].[Group].CurrentMember.Key AS NUMERIC) > 0 )), "#####") <br>MEMBER [Measures].[total_result_count] AS Format(COUNT([Requested]), "#####") <br>
MEMBER [Folder].[Aggregated] AS [Folder].[Folder].[126] <br>MEMBER [Folder].[Content] AS [Folder].[Folder].[0] <br>MEMBER [Folder].[Unattributed] AS [Folder].[Folder].[0] <br><br>SELECT {[Measures].allMembers} ON COLUMNS, { <br>
[Folder].[Aggregated], <br> [Folder].[Content], <br> [Folder].[Unattributed], <br> LimitSet(Order([Requested], [Measures].[revenue], BDESC), 0, 1)<br>
} <br>ON ROWS FROM Kwid_Cube WHERE {([Time].[range] )}<br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br>-------------------------------------------------------------Group Mdx Query-----------------------------------------------------<br>
<br>WITH <br>MEMBER [Measures].[id] AS [KIs].CurrentMember.Name <br>MEMBER [Measures].[publisher] AS Ancestor([KIs].CurrentMember, [KIs].[Publisher]).Name <br>MEMBER [Measures].[campaign] AS Ancestor([KIs].CurrentMember, [KIs].[Campaign]).Name <br>
MEMBER [Measures].[group] AS Ancestor([KIs].CurrentMember, [KIs].[Group]).Name <br>MEMBER [Measures].[title] AS [KIs].CurrentMember.Properties("Group Name") <br>MEMBER [Time].[range] AS [Time].[Epoch].[1218499200] <br>
MEMBER [Time].[base_range] AS [Time].[Epoch].[1217894400] <br>MEMBER [Measures].[impressions_delta_absolute] AS ([Time].[range], [Measures].[impressions]) - ([Time].[base_range], [Measures].[impressions]) <br>
MEMBER [Measures].[impressions_delta_percent] AS 100 * ((([Time].[range], [Measures].[impressions]) - ([Time].[base_range], [Measures].[impressions])) / ([Time].[base_range], [Measures].[impressions])) <br>MEMBER [Measures].[pub_clicks_delta_absolute] AS ([Time].[range], [Measures].[pub_clicks]) - ([Time].[base_range], [Measures].[pub_clicks]) <br>
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])) <br>MEMBER [Measures].[pub_cost_delta_absolute] AS ([Time].[range], [Measures].[pub_cost]) - ([Time].[base_range], [Measures].[pub_cost]) <br>
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])) <br>MEMBER [Measures].[conversions_delta_absolute] AS ([Time].[range], [Measures].[conversions]) - ([Time].[base_range], [Measures].[conversions]) <br>
MEMBER [Measures].[conversions_delta_percent] AS 100 * ((([Time].[range], [Measures].[conversions]) - ([Time].[base_range], [Measures].[conversions])) / ([Time].[base_range], [Measures].[conversions])) <br>MEMBER [Measures].[revenue_delta_absolute] AS ([Time].[range], [Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue]) <br>
MEMBER [Measures].[revenue_delta_percent] AS 100 * ((([Time].[range], [Measures].[revenue]) - ([Time].[base_range], [Measures].[revenue])) / ([Time].[base_range], [Measures].[revenue])) <br>MEMBER [Measures].[gross_profit_delta_absolute] AS ([Time].[range], [Measures].[gross_profit]) - ([Time].[base_range], [Measures].[gross_profit]) <br>
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])) <br>MEMBER [Measures].[profit_delta_absolute] AS ([Time].[range], [Measures].[profit]) - ([Time].[base_range], [Measures].[profit]) <br>
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 ) <br>
MEMBER [Measures].[total_available_count] AS Format(COUNT(FILTER([KIs].[Group].Members, CAST([KIs].[Group].CurrentMember.Key AS NUMERIC) > 0 )), "#####") <br>MEMBER [Measures].[total_result_count] AS Format(COUNT([Requested]), "#####") <br>
MEMBER [KIs].[Aggregated] AS [KIs].[All KIs] <br>MEMBER [KIs].[Content] AS [KIs].[Campaign].[0] <br>MEMBER [KIs].[Unattributed] AS [KIs].[Campaign].[0] <br><br>SELECT {[Measures].allMembers} ON COLUMNS, { <br>
[KIs].[Aggregated], <br> [KIs].[Content], <br> [KIs].[Unattributed], <br> LimitSet(Order([Requested], [Measures].[revenue], BDESC), 0, 20)<br>
} <br>ON ROWS FROM Kwid_Cube WHERE {([Time].[range] )}<br><br><br><br><br><br></div>