Results 1 to 7 of 7

Thread: Local Cube

  1. #1
    Join Date
    Sep 2002
    Posts
    6

    Question Unanswered: Local Cube

    I am trying to create a Local Cube.I get this error: "Column expression associated with level '_TemplateLevel' is not found"
    Has anybody else encountered this?

    My create statement is:
    PROVIDER=MSOLAP;
    DATA SOURCE=C:\ControllingExe\CT_BusinessLogic\OLAP\TES TY\t5.cub;
    SOURCE_DSN="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
    Initial Catalog=ControllingDTS_v13_GK_PC;Data Source=CTWDR;Connect Timeout=15";

    CREATECUBE="CREATE CUBE [test] (
    Dimension [MAGAZYN]
    DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN,
    LEVEL [Całość MAGAZYN] TYPE ALL,
    LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,
    MEASURE [Miara] Function SUM Format 'Standard' )";
    INSERTINTO=INSERT INTO [test]
    ( [MAGAZYN].[MAGAZYN].NAME,
    [MAGAZYN].[MAGAZYN].KEY,
    [MAGAZYN].[MAGAZYN].parent,
    [Measures].[Miara])
    SELECT [MAGAZYN].[MAGAZYN],[MAGAZYN].[MAGAZYNID],[MAGAZYN].[MAGAZYN_ParentID1],[test].[Miara]
    FROM [test],[MAGAZYN] WHERE [test].[MAGAZYNID] = [MAGAZYN].[MAGAZYNID];

  2. #2
    Join Date
    Sep 2002
    Posts
    3

    Re: Local Cube

    Hi,
    I have this problem, it seems that it gets solved when you remove
    "LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,"
    However I can't seem to view the dimension in my control.
    What are you using to display the cube in?
    Maher

    CREATECUBE="CREATE CUBE [test] (
    Dimension [MAGAZYN]
    DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN,
    LEVEL [Całość MAGAZYN] TYPE ALL,
    LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,
    MEASURE [Miara] Function SUM Format 'Standard' )";
    INSERTINTO=INSERT INTO [test]
    ( [MAGAZYN].[MAGAZYN].NAME,
    [MAGAZYN].[MAGAZYN].KEY,
    [MAGAZYN].[MAGAZYN].parent,
    [Measures].[Miara])
    SELECT [MAGAZYN].[MAGAZYN],[MAGAZYN].[MAGAZYNID],[MAGAZYN].[MAGAZYN_ParentID1],[test].[Miara]
    FROM [test],[MAGAZYN] WHERE [test].[MAGAZYNID] = [MAGAZYN].[MAGAZYNID]; [/SIZE][/QUOTE]

  3. #3
    Join Date
    Sep 2002
    Posts
    3

    Re: Local Cube

    Hi,
    Forget what I have written,
    The cube gets build but it does not store any parent-child relationship.

    If you have figured out the solution, can you please post it.
    Thank you

  4. #4
    Join Date
    Sep 2002
    Posts
    6

    Re: Local Cube

    Originally posted by mmehchi
    Hi,
    Forget what I have written,
    The cube gets build but it does not store any parent-child relationship.

    If you have figured out the solution, can you please post it.
    Thank you
    Hi ,
    I have solved my problem. The correct cube creation statement is:
    PROVIDER=MSOLAP;DATA SOURCE=C:\ControllingExe\CT_ActiveX\OLAP\testy\t7. cub;
    SOURCE_DSN="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ControllingDTS_v13_GK_PC;Data Source=CTWDR;Connect Timeout=15";
    CREATECUBE=
    "CREATE CUBE [test] (
    DIMENSION [MAGAZYN] DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN,
    LEVEL [Całość MAGAZYN] TYPE ALL,
    LEVEL [MAGAZYN] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,
    MEASURE [Miara] Function SUM Format 'Standard' )";
    INSERTINTO=INSERT INTO [test]
    ( [MAGAZYN].NAME,
    '->NOT [MAGAZYN].[MAGAZYN].NAME
    [MAGAZYN].KEY,
    '->NOT [MAGAZYN].[MAGAZYN].KEY
    [MAGAZYN].PARENT,
    '->NOT [MAGAZYN].[MAGAZYN].PARENT
    [Measures].[Miara])
    SELECT
    [MAGAZYN].[MAGAZYN],
    [MAGAZYN].[MAGAZYNID],
    [MAGAZYN].[MAGAZYN_ParentID1],[test].[Miara] FROM [test],[MAGAZYN] WHERE [test].[MAGAZYNID] = [MAGAZYN].[MAGAZYNID];

    I view my local cube in TETA_CONTROLLING aplication.

    Kind Regards
    Dorota Papiernik

  5. #5
    Join Date
    Sep 2002
    Posts
    3
    Hi,
    Thank you for you reply. It basically saved me.
    However how did you get the following syntax:
    MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN
    I could not find any documentation about it.
    Thank you
    Maher Mehchi

  6. #6
    Join Date
    Sep 2002
    Posts
    6

    MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN

    Originally posted by mmehchi
    Hi,
    Thank you for you reply. It basically saved me.
    However how did you get the following syntax:
    MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN
    I could not find any documentation about it.
    Thank you
    Maher Mehchi
    I get this syntax from dBforums archives
    Dorota Papiernik

  7. #7
    Join Date
    Dec 2002
    Posts
    1

    Red face Re: MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN

    can you please help me with this. I think I know the place where its getting messed up. please look for markers /* .... */

    [Fact_OK_WV_KS] is the cube and [Standard_OK_WV_KS] is a shared parent-child dimension. The following are the fields from the database table used for creating this dimension.

    value for Key : StandardID
    value for Name : Goal
    value for Parent : ParentStdID

    Any help is greatly appreciated. Thanx

    Dim strMDX As String
    Dim strCube As String
    Dim sConnection As String
    Dim sLocation As String
    Dim sSourceDSN As String
    Dim sCreateCube As String
    Dim sInsertInto As String

    sCreateCube = ""



    sLocation = "LOCATION= C:\testLocal.cub"

    sSourceDSN = "SOURCE_DSN = ""Provider=MSOLAP;data source=LocalHost;INITIAL CATALOG=test1"""

    strCube = "CREATECUBE=CREATE CUBE testLocal ( "

    strCube = strCube & " DIMENSION [Standard_OK_WV_KS] DIMENSION_STRUCTURE PARENT_CHILD MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_HIDDEN, "
    strCube = strCube & " LEVEL [All Standard] TYPE ALL, "
    strCube = strCube & " LEVEL [Standard_OK_WV_KS] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_BLANK_OR_SELF_OR_MISSING, " 'ROOT_MEMBER_IF ROOT_IF_PARENT_IS_SELF ,"

    strCube = strCube & " DIMENSION District, "
    strCube = strCube & " LEVEL [All District] TYPE ALL, "
    strCube = strCube & " LEVEL [District ID], "
    strCube = strCube & " LEVEL [School ID], "
    strCube = strCube & " LEVEL [Teacher ID], "
    strCube = strCube & " LEVEL [Class ID], "
    strCube = strCube & " DIMENSION [Test Date], "
    strCube = strCube & " LEVEL [All Time] TYPE ALL, "
    strCube = strCube & " LEVEL [Year] TYPE YEAR OPTIONS(SORTBYNAME), "
    strCube = strCube & " LEVEL [Month] TYPE MONTH OPTIONS(SORTBYKEY),"
    strCube = strCube & " LEVEL [Day] TYPE MONTH OPTIONS(SORTBYKEY),"
    strCube = strCube & " MEASURE [Total] FUNCTION COUNT FORMAT '#' TYPE DBTYPE_I8, "
    strCube = strCube & " MEASURE [Correct] FUNCTION SUM TYPE DBTYPE_I8) "

    sInsertInto = ""
    sInsertInto = sInsertInto & "INSERTINTO=INSERT INTO testLocal " & vbCrLf
    sInsertInto = sInsertInto & " (" & vbCrLf

    sInsertInto = sInsertInto & " [Standard_OK_WV_KS].NAME, "
    sInsertInto = sInsertInto & " [Standard_OK_WV_KS].KEY, "
    sInsertInto = sInsertInto & " [Standard_OK_WV_KS].PARENT, "

    sInsertInto = sInsertInto & " [District].[District ID].NAME," & vbCrLf
    sInsertInto = sInsertInto & " [District].[District ID].KEY," & vbCrLf
    sInsertInto = sInsertInto & " [District].[School ID].NAME," & vbCrLf
    sInsertInto = sInsertInto & " [District].[School ID].KEY," & vbCrLf
    sInsertInto = sInsertInto & " [District].[Teacher ID].NAME," & vbCrLf
    sInsertInto = sInsertInto & " [District].[Teacher ID].KEY," & vbCrLf
    sInsertInto = sInsertInto & " [District].[Class ID].NAME," & vbCrLf
    sInsertInto = sInsertInto & " [District].[Class ID].KEY," & vbCrLf

    sInsertInto = sInsertInto & " [Test Date].[Year].NAME," & vbCrLf
    sInsertInto = sInsertInto & " [Test Date].[Year].KEY," & vbCrLf
    sInsertInto = sInsertInto & " [Test Date].[Month].NAME," & vbCrLf
    sInsertInto = sInsertInto & " [Test Date].[Month].KEY," & vbCrLf
    sInsertInto = sInsertInto & " [Test Date].[Day].NAME," & vbCrLf
    sInsertInto = sInsertInto & " [Test Date].[Day].KEY," & vbCrLf
    sInsertInto = sInsertInto & " [Measures].[Total]," & vbCrLf
    sInsertInto = sInsertInto & " [Measures].[Correct] " & vbCrLf

    sInsertInto = sInsertInto & ") "
    sInsertInto = sInsertInto & "SELECT" & vbCrLf

    /* i think the error is in the next 3 lines. Can you please tell me what the values should be for the Parent-child dimensions should be */

    /* the database value for the line below is Goal. This is the description for the Dimension. How do I represent that here */
    sInsertInto = sInsertInto & "[Fact_OK_WV_KS].[Standard_OK_WV_KS!???] ,"
    /* the database value for the line below is StdID. This is the Id for the dimension. How do I represent that here */
    sInsertInto = sInsertInto & "[Fact_OK_WV_KS].[Standard_OK_WV_KS!???] , "
    /* the database value for the line below is ParentStdID. This is the parent ID . How do I represent that here */
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Standard_OK_WV_KS!????] , "

    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:District ID]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:District ID!KEY]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:School ID]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:School ID!KEY]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Teacher ID]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Teacher ID!KEY]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Class ID]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[District:Class ID!KEY]," & vbCrLf

    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Year]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Year!KEY]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Month]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Year!KEY]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Day]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Test Date:Day!KEY]," & vbCrLf

    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Measures:Total]," & vbCrLf
    sInsertInto = sInsertInto & " [Fact_OK_WV_KS].[Measures:Correct] " & vbCrLf
    sInsertInto = sInsertInto & " From [Fact_OK_WV_KS] " & vbCrLf

    sInsertInto = sInsertInto & " WHERE (([Fact_OK_WV_KS].[District:School ID] = '[District].[All District ID].[2029].[1908]'))" & vbCrLf
    'sInsertInto = sInsertInto & " group by (([Standard_OK_WV_KS], [District], [Test Date] ))"
    'sInsertInto = sInsertInto & " OR ([Sales].[Product:Product Family] = '[Product].[All Products].[Non-Consumable]'))" & vbCrLf
    'sInsertInto = sInsertInto & "AND (([Sales].[Customers:Country] = '[Customers].[All Customers].[Mexico]')" & vbCrLf
    'sInsertInto = sInsertInto & " OR ([Sales].[Customers:Country] = '[Customers].[All Customers].[USA]'))" & vbCrLf

    'Response.Write(strCube & "<br>" & sInsertInto)
    'Response.End()
    Dim objConnection As New ADODB.Connection()

    sConnection = sLocation & ";" & vbCrLf & sSourceDSN & ";" & vbCrLf & strCube & ";" & vbCrLf & sInsertInto

    objConnection.ConnectionString = sConnection
    objConnection.Provider = "MSOLAP"
    objConnection.Open()
    'Response.Write("cube created")

    objConnection.Close()

    Thanx again
    Last edited by karthikjs; 03-17-04 at 17:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •