none
Error with exemple of create SUBCUBE Adventure Work

    Question

  • Hello,

    I am trying to use subcube so i am using the code below from microsoft example but it give me error.

    CREATE SUBCUBE [Adventure Works] AS

       SELECT [Geography].[Country].&[Canada] ON 0
       FROM [Adventure Works]

    SELECT [Geography].[Country].[Country].MEMBERS ON 0
       FROM [Adventure Works]

    Executing the query ...
    Query (5, 1) Parser: The syntax for 'SELECT' is incorrect.
    Execution complete

    I am using Sql server 2012 standard edition.

    All the best

    Vincent

    Friday, July 04, 2014 5:14 AM

Answers

  • They are 2 mdx. How about execute each one ?

    1.

    CREATE SUBCUBE [Adventure Works] AS
       SELECT [Geography].[Country].&[Canada] ON 0
       FROM [Adventure Works]

    2.

    SELECT [Geography].[Country].[Country].MEMBERS ON 0
       FROM [Adventure Works]


    Friday, July 04, 2014 7:02 AM
  • You can't execute both of them together.

    execute each of them.  like ...

    Connection Open

    Execute  CREATE SUBCUBE

    Select MDX Command

    Execute  DROP SUBCUBE

    Connection Close

    Friday, July 04, 2014 7:59 AM
  • Hi Vincent,

    As you can see on that link
    The following example creates a subcube that restricts the apparent cube space to members that exist with the country of Canada. It then uses the
    MEMBERS function to return all members of the Country level of the Geography user-defined hierarchy - returning only the country of Canada.

    CREATE SUBCUBE [Adventure Works] AS
    SELECT [Geography].[Country].&[Canada] ON 0
        FROM [Adventure Works]
    
    SELECT [Geography].[Country].[Country].MEMBERS ON 0
        FROM [Adventure Works]
    

    So there are 2 MDX queries, you need execute the first one to create the subcube.
    CREATE SUBCUBE [Adventure Works] AS
    SELECT [Geography].[Country].&[Canada] ON 0
        FROM [Adventure Works]

    And then execute the second query to select data from the subcube.
    SELECT [Geography].[Country].[Country].MEMBERS ON 0
        FROM [Adventure Works]

    In this case, you can get the results like below.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, July 07, 2014 7:08 AM
  • Do you need create subcube ?

    How about use subquery?   like...

    SELECT [Geography].[Country].[Country].MEMBERS ON 0
    FROM(
         SELECT [Geography].[Country].&[Canada] ON 0
         FROM [Adventure Works]
        )

    or  you can use ROLE to limit access.

    Tuesday, July 08, 2014 1:46 AM
  • Oh yeh,Never try that.I will have a look.

    For now i creating a TSQL stored procedure that run openquery and return a DataTable

    Vincent

    Wednesday, July 09, 2014 12:52 AM

All replies

  • They are 2 mdx. How about execute each one ?

    1.

    CREATE SUBCUBE [Adventure Works] AS
       SELECT [Geography].[Country].&[Canada] ON 0
       FROM [Adventure Works]

    2.

    SELECT [Geography].[Country].[Country].MEMBERS ON 0
       FROM [Adventure Works]


    Friday, July 04, 2014 7:02 AM
  • Well the goal is to execute both of them together the first one create the subcube then the second one use the subcube
    Friday, July 04, 2014 7:26 AM
  • You can't execute both of them together.

    execute each of them.  like ...

    Connection Open

    Execute  CREATE SUBCUBE

    Select MDX Command

    Execute  DROP SUBCUBE

    Connection Close

    Friday, July 04, 2014 7:59 AM
  • Hi Vincent,

    As you can see on that link
    The following example creates a subcube that restricts the apparent cube space to members that exist with the country of Canada. It then uses the
    MEMBERS function to return all members of the Country level of the Geography user-defined hierarchy - returning only the country of Canada.

    CREATE SUBCUBE [Adventure Works] AS
    SELECT [Geography].[Country].&[Canada] ON 0
        FROM [Adventure Works]
    
    SELECT [Geography].[Country].[Country].MEMBERS ON 0
        FROM [Adventure Works]
    

    So there are 2 MDX queries, you need execute the first one to create the subcube.
    CREATE SUBCUBE [Adventure Works] AS
    SELECT [Geography].[Country].&[Canada] ON 0
        FROM [Adventure Works]

    And then execute the second query to select data from the subcube.
    SELECT [Geography].[Country].[Country].MEMBERS ON 0
        FROM [Adventure Works]

    In this case, you can get the results like below.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, July 07, 2014 7:08 AM
  • Thanks,

    Could i run two query inside the same openquery statement ?

    I use TSQL as a controller to talk to my cube and expose the cube to the client.I like to run subcube query before the client query to limit access but don't have to rewrite query with subcube inside the where clause.

    Vincent

    Monday, July 07, 2014 7:13 AM
  • Do you need create subcube ?

    How about use subquery?   like...

    SELECT [Geography].[Country].[Country].MEMBERS ON 0
    FROM(
         SELECT [Geography].[Country].&[Canada] ON 0
         FROM [Adventure Works]
        )

    or  you can use ROLE to limit access.

    Tuesday, July 08, 2014 1:46 AM
  • Subquery is what i am doing for the moment,but it have limitations.

    Role can be tricky to implement because of double hoop issues and impersonation (There is no windows authentication for user because their are outside the domain.

    Vincent

    Tuesday, July 08, 2014 1:49 AM
  • How about create 

    SSAS Stored Procedure, return DataTable ?

    Wednesday, July 09, 2014 12:44 AM
  • Oh yeh,Never try that.I will have a look.

    For now i creating a TSQL stored procedure that run openquery and return a DataTable

    Vincent

    Wednesday, July 09, 2014 12:52 AM