Skip to main content
M
MarcR
Contributor
November 25, 2021

Read/ Analyze contents of Binary columns

  • November 25, 2021
  • 4 replies
  • 0 views

Hi there, 

For a client i want to do a detailed analysis of the data (numbers) in the database. We want to investigate the percentage of numbers < 0.01.

However, when i use the Data records i see the following:

MarcR_1-1637836132266.png

But in this view i cannot filter on values in the Value1, Value2 etc. columns.

 

In the binary table itself i see as contents of the CellAmounts the following:

MarcR_0-1637836117856.png

When i convert this to a string i see a binary code that i cannot read.

Does anybody know how to write the select statement in SQL to see the Value fields in the CellAmounts column so i can filter on them?

 

Thanks in advance.

    4 replies

    S
    Sai_Maganti
    Newcomer
    November 25, 2021

    Marc

    Not sure about the binary table but try this SQL equivalent to your data record table. If you want to show the monthly value in columns just use the inner query. Put this SQL in a data adapter => Grid => dashboard etc.. and make sure you put in real values for cube name etc.. in the WHERE clause.

    SELECT datacube
    ,entity
    ,scenario
    ,account
    ,ud1
    ,ud2
    ,ud3
    ,ud4
    ,ud5
    ,ud6
    ,ud7
    ,ud8
    ,mvalue
    ,mvaluename
    FROM (
    SELECT c.name AS datacube
    ,e.name AS entity
    ,s.name AS scenario
    ,a.name AS account
    ,u1.name AS ud1
    ,u2.name AS ud2
    ,u3.name AS ud3
    ,u4.name AS ud4
    ,u5.name AS ud5
    ,u6.name AS ud6
    ,u7.name AS ud7
    ,u8.name AS ud8
    ,dr.M1Value AS v1
    ,dr.M2Value AS v2
    ,dr.M3Value AS v3
    ,dr.M4Value AS v4
    ,dr.M5Value AS v5
    ,dr.M6Value AS v6
    ,dr.M7Value AS v7
    ,dr.M8Value AS v8
    ,dr.M9Value AS v9
    ,dr.M10Value AS v10
    ,dr.M11Value AS v11
    ,dr.M12Value AS v12
    FROM datarecord2021 dr --data record table
    LEFT JOIN cube c ON c.cubeid = dr.cubeid
    LEFT JOIN member s ON s.memberid = dr.scenarioid
    LEFT JOIN member a ON a.memberid = dr.accountid
    LEFT JOIN member e ON e.memberid = dr.entityid
    LEFT JOIN member u1 ON u1.memberid = dr.ud1id
    LEFT JOIN member u2 ON u2.memberid = dr.ud2id
    LEFT JOIN member u3 ON u3.memberid = dr.ud3id
    LEFT JOIN member u4 ON u4.memberid = dr.ud4id
    LEFT JOIN member u5 ON u5.memberid = dr.ud5id
    LEFT JOIN member u6 ON u6.memberid = dr.ud6id
    LEFT JOIN member u7 ON u7.memberid = dr.ud6id
    LEFT JOIN member u8 ON u8.memberid = dr.ud7id
    WHERE c.name = '<cubename>'
    AND e.name = '<entityname>'
    AND s.name = '<scenarioname>'
    ) m
    unpivot(mvalue FOR mvaluename IN (
    v1
    ,v2
    ,v3
    ,v4
    ,v5
    ,v6
    ,v7
    ,v8
    ,v9
    ,v10
    ,v11
    ,v12
    )) u

    Best

    Sai

      M
      MarcRAuthor
      Contributor
      November 25, 2021

      Thanks for your reply Sai, 

      The challenge is we have a weekly application profile and then the data is not stored in the DataRecord tables but in the BinaryData tables. And those have the binary column i need to read. 

      I do have another solution which is not ideal but working. I run an FDX query from a business rule with a filter on the data values. That is working but it feels a bit extreme for a fairly simple query.

      C
      OneStream Employee
      ChristianWOneStream Employee
      OneStream Employee
      November 25, 2021

      I just wanted to propose a fdx query for this request. Accessing the data table directly is not a sustainable way of doing it.

      S
      Sai_Maganti
      Newcomer
      November 26, 2021

      Ah thanks and noted Christian. Are you able to provide some samples of FDX BR queries? I tried to find some help in the documentation and there is nothing much.

      C
      OneStream Employee
      ChristianWOneStream Employee
      OneStream Employee
      November 26, 2021
      MarcR wrote:

      In the binary table itself i see as contents of the CellAmounts the following:

      MarcR_0-1637836117856.png

      When i convert this to a string i see a binary code that i cannot read.

      Does anybody know how to write the select statement in SQL to see the Value fields in the CellAmounts column so i can filter on them?

      Just a thought (I still don't think it is a good solution): Did you try to convert it to a decimal(28,9)?

      M
      MarcRAuthor
      Contributor
      November 26, 2021

      Hi Christian, I used the FDX, but am still curious if we can get the value out 😉

      Usually in SQL binary is used for documents and images. In this case i think the column contains a table. Therefore you cannot open it as a decimal (or a varchar). There should be an option to loop through the fields in the column and get their value. But its not urgent anymore, issue is solved but my curiosity is not satisfied 😉

      C
      OneStream Employee
      ChristianWOneStream Employee
      OneStream Employee
      November 26, 2021

      Hi Marc

      Out of curiosity, I played around with it and I could identify some patterns, but the byte order was different (left to right instead of right to left) so I assume, the conversion takes place in .Net and not in SQL. As a result, I doubt, there is a simple select statement for the job.

      Cheers

      Christian

        Terms & ConditionsAccessibility statement