Forum Discussion

RehaanAbdulla's avatar
RehaanAbdulla
New Contributor III
2 years ago

How to write from a SQL query into a cubeview

Good Day 

I am new to OneStream so forgive me if this question and the answer should be known but I have created a SQL table with values and I would like to take those values and write them to a cubeview. I am using a business rule and was wondering how would I do this. I am aware I need to make a sql connection so my initial code is:

Dim sql As New Text.StringBuilder
sql.AppendLine(" SELECT * From x_BatchControl")

Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString, True)

Looking for any help on this. 

Thanks 

Rehaan

Connectors
Finance
  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    Hi RehaanAbdulla , how about dropping that SQL into a Data Adapter, then assigning that Data Adapter to a SQL Table Editor or Grid view component, then you can display the resulting table in one of those Dashboard components.  Not sure you are demonstrating a solid business case trying to squeeze a data table into a Cube View.  The name Cube View is what it is, it is a view of the Cube data.  Not a view of relational batch records.  I could be wrong, but if it were me I'd go a different route for the display of that batch record data.

    Hope this helps.

    • RehaanAbdulla's avatar
      RehaanAbdulla
      New Contributor III
      2 years ago

      Hi MikeG  

      So we have created it as a SQL Table and made it into a grid view. However the reason for me wanting to move the the data on the SQL table to the cube view is that each month they would make changes on the SQL table and then it must show up on the cube view or if they dont make any changes then if could be transferred to the cube view as well. 

      Each month the cube view will not carry the values it had the previous month and so the SQL table will carry it however the users will need to see it on the cube view.

       

      Regards

      Rehaan

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    2 years ago

    Import the data. If it needs to be in a cubeview that implies it is sharing at least some of the dimensions. My recommendation would be to import it to an account(s) per usual.

    Edit: you can still leverage very granular data here by importing aggregated values, then using Cube View Navigation Links to display some sort of DataGrid, PivotGrid, BI Viewer, etc. in a dashboard as MikeG suggests