Forum Discussion

Marco's avatar
Marco
Contributor II
2 years ago

What is the table from which the information of the QView comes from? (sql question)

I'm running a SQL query to display the data as if it were a QView, but I realized that the table I was using didn't contain the values it should.

        SELECT
            EntityId,
            UD1Id,
            UD4Id,
            TimeId,
            ICId,
            Amount,
            FlowId,
            ConsId
        FROM
            DataEntryAuditCell
        WHERE
            ScenarioId = (
                SELECT
                    MemberId
                FROM
                    Member
                WHERE
                    Name = '|WFScenario|'
            )
            AND CubeId = 4
            AND ICId != -999
            AND AccountId = 17826015
            AND Amount != 0
            AND FlowId IN (1048639,-999)
            AND UD1Id != -999
            AND UD4Id != -999

The reason for using SQL is to facilitate user access to that specific information, as a QView wouldn't display everything according to the required permissions, which in this case doesn't apply as it's very specific information.

So it would be helpful if you could tell me which table or tables I can use to achieve the same result as I would see when using a QView.

Finance

6 Replies

Sort By
  • JackLacava's avatar
    JackLacava
    Community Manager
    2 years ago

    I guess by "QView" you mean a Cube View?

    The reasons for differences between raw SQL values and Cube View displayed values can be many - formatting , DynamicCalc intersections, ConditionalInput rules, access rules...

    I think your problem really is this: "a QView wouldn't display everything according to the required permissions, which in this case [you think it] doesn't apply" You're trying to bypass OneStream security, instead of working with it. The right way to go about this is figuring out why that is, and address it the right way. If you can't touch anything in that cube setup, for example, you could copy the necessary data to a separate cube and apply different security there.

    • Marco's avatar
      Marco
      Contributor II
      2 years ago

      Yes, a cube view, but I want to know if there is a way to do it even from a business rule, that is, a business rule that gives me the data of a cube view or the way that the functionality of Excel does.

      • JackLacava's avatar
        JackLacava
        Community Manager
        2 years ago

        You can get data from cubeviews in a few different ways. What you choose will depend on what you want the output to look like.

        1. If you're building a Dashboard or Report, there are two types of Data Adapter you can use - Cube View and Cube View MD (I'd recommend the latter). You wire that to a Grid or a Pivot Grid and you're done, no code necessary.
        2. If you need to extract data to a text file, in a BR you can use Brapi.Import.Data.FdxExecuteCubeView or FdxExecuteCubeViewTimePivot. They are the fastest way to retrieve the same numbers you'd get from a CV, but they do require you to pass quite a few parameters.
        3. A hybrid approach, slightly slower than 2 but easier to manage for non-coders, is to set up a Data Adapter like in 1, and then call it from a rule using a basic call of BRApi.Dashboards.Process.GetAdoDataSetForAdapter. This way you can easily change CV and parameters later on, without having to touch the rule.
        4. If you need to extract data to Excel, you can use approach 2 or 3 from a Table View, or you can use BRApi.CubeViews.Process.ExportCubeViewGridsToExcelFile.
    • Marco's avatar
      Marco
      Contributor II
      2 years ago

      I created the cube view and yes, I encountered that security issue, so how could I go about displaying the information to someone who couldn’t see it?