Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Accessing Binary Columns from Oracle Database

Hello,

I recently got access to an Oracle DB and it's connected now to Power BI. The problem is that 3 columns in the database are binary columns and I can't view their data.

COLUMN_NAMEDATA_TYPECOMMENTS
DATA_ARRAY1BLOBBinary data
DATA_ARRAY2BLOBBinary data
FILEBLOBBinary data


And this is how it appears on Power Query:


And here is what I have when I click on the Binary:



I got some insights from The DB Admin and I am quoting him in the following:
- "The binary data in DATA_ARRAY1 and DATA_ARRAY2 are integer arrays with 3700 elements. Defined as: int data1[3700], int data2[3700] they are not an array type in the database.they are BLOB."
- He used a C# code to break that binary image into the correct format.  (Not sure if we will need it to decode the data)
- "The binary image will have to be broken into 32 bit lengths for each element of the array. The binary image has all elements for the entire array just all stuck together. If you had a hex editor (or binary) and looked at that binary image you could see the individual elements of the array."
- "If power BI has the ability to allow you to save that binary image into a file then you could open that file up with a hex editor and see how the data is arranged. I think an integer array is saved in the image in order. element 0 of the array should be bits 1 -  32. and element 1 should be bits 33 - 64, etc.."


I haven't worked with Binary Data before and am not familiar with this type of manipulation. I am seeking your help either to solve the problem or to understand the process better. Any assistance you can provide in guiding me through the steps or informing me about the necessary information I should get from the DB Admin would be greatly appreciated.

  • lbendlin's avatar
    lbendlin
    2 years ago

    1. just add the serial number as a column before you transform the blobs - it will automatically be expanded with the other columns.

    2. as long as you keep the table narrow Power BI has no issues with billions of rows. If you want to be cute you can consider incremental refresh.

    3. That was not part of the sample data - But what you can do is cut the BLOBs  into chunks of 4xDATA_POINTS first and then take the first list item for each serial number, and process that.

13 Replies