using a filtered cubeview as ti datasource

[scrummyd]

I am looking to populate a cube using ti with the data source being from a filtered cube view which is filtered on top 25. My problem is that ti does not seem to load the data from the filtered view.

Totally appreciate any help

[Martin Ryan]

I haven't tried using a filtered view as a data source, but suggest you try using the "ViewConstruct" TI command in the prolog to rebuild the view immediately before processing it.

Martin

[scrummyd]

ViewConstruct still not curing.

Ultimately I am trying to create a report (one excel file) which will show 107 top25 views dependant on category. I thought it better to try and populate the top 25 values into a cube for simple DBR formulas.

mmmmm.....

[ScottW]

If building a report is your aim then pulling filtered views into a flattened cube doesn't seem necessary. First question are the views permanent or transitory? (by that I mean constructed and destroyed by TI and not accessible to users).

If the views are permanent and you don't mind these objects cluttering the system then why not use dynamic subsets based on the same filtered cube view to drive the same reporting instead. If you have a standard naming convention then one template should give you access to each of the 107 category reports, just combine a dropdown list with some simple Excel formulas to get your subset name. The subset should get you the filtered top 25 rowset and build the column headers however you like

Or why not build the reports with MDX in Excel (will require some VBA so won't work on the web). Again if each of the categories has a uniform report format then one template should give you all 107 reports.

The MDX route would be my pick but depending on where you are currently at with TM1 you might find dynamic subsets simpler to implement.

[scrummyd]

never tried MDX via excel. Is there a VBA example you could provide on how to call the data then roll through the 25 lines?

[scrummyd]

also my MDX statement is well over 255 characters (that I know TI does not like)

[scrummyd]

is there such an example?

Has this one stumped you all?

[neilben]

I'm sure there was an MDX example posted on the old Applix Forum somewhere.

have a look

[ScottW]

is there such an example? ... Has this one stumped you all?

I would say not, perhaps many consultants, myself included, might usually feel a worked MDX example would be giving too much away. However, in the spirit of Christmas giving, ...

Note that using MDX in Excel uses a separate connection to the TM1 server than the perspectives client. This connection will only be available if during the Perspectives install the "TM1 OLEDB provider" option was checked under "TM1 Client." Select Custom not standard during install otherwise this is generally not the default.

You can return a cellset with MDX and read the query values into Excel cells. However by far the easiest and most flexible approach for a "top X" report is to have your MDX generate a view that has the Top X dimension on rows and all other dimensions with a single point selection. This will produce a one dimensional array cellcet from which you can derive the rowcet. In the Excel report the net effect is like a copy paste values of the filtered element list into the row headers and a recalc (as no need to change any of the formula references.)

The example below doesn't have full error handling and is customised to give the top X children of the next level down in the product dimension for whatever element is selected for a top X of 1 to 100 for a 7 dim "Sales" cube with dimensions year, period, product, measure, region, company & customer (starting from cell A10.) Obviously you would need a template set up with appropriate name ranges and DBRW formulas for this exact code sample to work but this should be more than enough to get the picture.

Code: Select all

Option Explicit

Public db As ADODB.Connection

Global Const sConnection As String = "DATA SOURCE=tm1serv;PROVIDER=TM1OLAP"
Global Const sCube As String = "SalesCube"
Global Const sUser As String = "SalesCubeReader"
Global Const sPass As String = "UsrPwd"


Function OLAP_Connection(OLAP_Connection As Boolean)
    
    OLAP_Connection = False
    Set db = New ADODB.Connection
    
    db.Open sConnection, sUser, sPass
    
    If db Is Nothing Then
        MsgBox "Unable to make a connection to TM1OLAP."
        Exit Function
    Else
        OLAP_Connection = True
    End If
            
    db.Close
    
End Function

Sub cmdReturnTopX_Click()
    Dim iX As Integer
    Dim rs As Cellset
    Dim sQry As String
    Dim sEle As String
    Dim sEles() As String
    Dim icounter As Long
    Dim iCount As Integer, iStartRow As Integer
    
    Dim sCompany As String
    Dim sCustomer As String
    Dim sLocation As String
    Dim sProducts As String
    Dim sMeasure As String
    Dim sYear As String    
    Dim sPeriod As String
    Dim sCount As Integer
       
    IF Not OLAP_Connection Then
        Exit Sub
    End If

    sCompany = ActiveSheet.Range("Company").Value
    sCustomer = ActiveSheet.Range("Customer").Value
    sLocation = ActiveSheet.Range("Location").Value
    sProducts = ActiveSheet.Range("Products").Value
    sMeasure = ActiveSheet.Range("Measure").Value
    sYear = ActiveSheet.Range("Year").Value
    sPeriod = ActiveSheet.Range("Period").Value    
    sCount = ActiveSheet.Range("Number").Value
       
    iCount = sCount
    'Take some parameters off the spreadsheet and use them in the query.
    'The words in CAPS are MDX Functions
    sQry = "SELECT {[Sales_M].[" & sMeasure & " ]} ON COLUMNS, "
    sQry = sQry & "TOPCOUNT([Item].[ " & sProducts & " ].CHILDREN, " & iCount & ", [Sales_M].[ " & sMeasure & " ]) ON ROWS "
    sQry = sQry & "FROM [ " & sCube & " ] "
    sQry = sQry & "WHERE ( [Company].[" & sCompany & "],[Customer].[ " & sCustomer & " ], [Region].[ " & sLocation & " ], "
    sQry = sQry & "[Year].[ " & sYear & " ], [Period].[ " & sPeriod & " ] ) "
    
    'Open a new ADO connection
    Set db = New ADODB.Connection
    
    'Trim sQry
    sQry = Trim(sQry)
    
    'Open a connection to TM1 however you choose to supply credentials (probably not fixed ID as in this example.)
    'MDX does not use the normal connection to TM1 so even if you are connected in Excel
    'You will still need to connect through the TM1OLAP Provider.  You will need to change
    'the way this is done so the security is handled better i.e use a form.

    db.Open sConnection, sUser, sPass
    
    'Open a CellSet to store the results of the query.
    Set rs = New Cellset
    
    'Open the query that was constructed above
    With rs
        .Open sQry, db
    End With
   
    icounter = 0
    ReDim sEles(icounter)
    
    'MDX returns duplicate  elements if unbalanced hierarchies are used.
    'This bit of code just redims the array and provides an array with no duplicates.
    'We need to use the Axes and Positions properties to work through the Cellset results.
    
    For iX = 0 To rs.Axes(1).Positions.Count - 1
        sEle = rs.Axes(1).Positions(iX).Members(0).Caption
        sEles(icounter) = sEle
        icounter = icounter + 1
        ReDim Preserve sEles(icounter)
    Next iX
        
    'Clear the current list of TopX elements.
    ActiveSheet.Range("TopXProducts").ClearContents

    'Fill the row headers with the TopX elements
    iStartRow = 10
    For iX = iStartRow To (iStartRow + icounter) - 1
        Range("A" & iX).Value = sEles(iX - iStartRow)
    Next
      
    Rows(10 & ":109").EntireRow.Hidden = False
    Rows(iX & ":109").EntireRow.Hidden = True
 
    'Recalc the active sheet so the DBRWs correctly reference the filtered topX list
    Application.Run "TM1RECALC1"
        
    db.Close
    Set rs = Nothing

End Sub

[jim wood]

Thanks Scott. Looks pretty good. May be something for the usefull code section?

[scrummyd]

Thanks Scott W


and ...

have a good christmas.