MDX : Top ten of a dimension based on measure

Question

Here's my OLAP cube schema

<?xml version="1.0"?>
<Schema name="mySchema">
    <Cube name="myCube">
        <Table name="fact_access_logs_views"/>
        <Dimension name="Countries" foreignKey="country_code_id">
            <Hierarchy hasAll="true" primaryKey="country_code_id">
                <Table name="dim_country_code"/>
                <Level name="CountryCodes" column="CountryCode" uniqueMembers="false"/>
            </Hierarchy>
        </Dimension>
        <Dimension name="Channels" foreignKey="channel_id">
            <Hierarchy hasAll="true" primaryKey="channel_id">
                <Table name="dim_channel"/>
                <Level name="Channels" column="shortname_chn" uniqueMembers="false"/>
            </Hierarchy>
        </Dimension>
        <Dimension name="Time" foreignKey="access_time_id">
            <Hierarchy hasAll="true" primaryKey="access_time_id">
                <Table name="dim_time_access"/>
                <Level name="Year" column="Year" uniqueMembers="false"/>
                <Level name="Month" column="Month" uniqueMembers="false"/>
                <Level name="Date" column="Date" uniqueMembers="false"/>
                <Level name="Hour" column="Hour" uniqueMembers="false"/>
            </Hierarchy>
        </Dimension>
        <Measure name="View Count" column="id" aggregator="count" formatString="#,###"/>
    </Cube>
</Schema>

what I am trying to do is find out top ten countries based on view count and further within each country, the top ten channels viewed. Please help me construct an MDX query. I have tried to follow this https://msdn.microsoft.com/en-us/library/ms145579 but ended up getting errors while viewing the cube.

Answer 1

You can achieve this using Top Count, Generate and CrossJoin.

WITH 
    SET TEMP AS 
       'Generate(TopCount([Countries].[CountryCodes].Members, 
        10, 
        [Measures].[View Count]), 
        Crossjoin(
            {[CountryCodes].CurrentMember}, 
            TopCount(
                [Channels].[Channels].Members, 
                10, [Measures].[View Count])
            )
        )'

select {[Measures].[View Count]} ON COLUMNS,
[TEMP] ON ROWS
from [myCube]