Read/ Analyze contents of Binary columns

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

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.

---

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

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 😉