Read/ Analyze contents of Binary columns

[MarcR]

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:

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:

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.

[Sai_Maganti]

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

[MarcR]

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.

[ChristianW]

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

[Sai_Maganti]

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.

[ChristianW]

Here is something I used for a custom IC matching report:

'Retrieve the data for the first query
Dim subQueryDataTable As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, $"E#{entityName}.Base", "local", scenarioTypeId, $"S#{scenarioName}", $"T#{timeName}", "YTD", True, "Flow='None'", 4, True)

And here is the explanation for the parameters:

[nizar]

Hi Christian,

can you please give us an exemple with reel value ?

i am strugle with this parametre, didn't find the correct syntaxt to get it.

scenarioTypeId

thanks

[nizar]

Hi Christian,

i am using it like this : 

Dim subQueryDataTable As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, "ContributionMargin", "E#{C010101}.Base", "local",brapi.Finance.Scenario.GetScenarioType(si,0).Id, "S#{Actual}", "T#{2021M10}", "YTD", True, "Flow='None'", 4, True)

can you please tell me what is wrong ?

many tahnks

[ChristianW]

If you like to use the curley brackets {} with strings, you need to add a $ sign infront of the quotes, like this: $"E#{C010101}.Base", $"S#{Actual}" or $"T#{2021M10}". But if you don't use variables at all, there is no need for the curley brackets at all.

Can you try this: Dim subQueryDataTable As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, "ContributionMargin", "E#C010101.Base", "local",brapi.Finance.Scenario.GetScenarioType(si,0).Id, "S#Actual", "T#2021M10", "YTD", True, "Flow='None'", 4, True)

Cheers

[RafMarques]

Hi @ChristianW,

This works perfect!

Is there any change to join the names with descriptions? 

Example: Acc1 - Expenses

Thanks

Raf

[ChristianW]

Hi Raf

Yes, you can loop the rows in the business rule and add the descriptions for the needed dimension members.

Cheers

[RafMarques]

You're right Chris.

I can loop and either add another dataTable field or concatenate after getting the description.

Was wondering if there was an "option" to turn descriptions on/off 🙂 - maybe an enhancement.

Thanks

Raf

[ChristianW]

---

@MarcR wrote:

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

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)?

[MarcR]

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 😉

[ChristianW]

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