Forum Discussion
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_NAME | DATA_TYPE | COMMENTS |
| DATA_ARRAY1 | BLOB | Binary data |
| DATA_ARRAY2 | BLOB | Binary data |
| FILE | BLOB | Binary 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.
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
- lbendlinSuper User
What are you planning to do with the lists of 3700 integers after you extract them from the binary field?
- AnonymousNot applicable
Hi lbendlin , every list of integers will be associated with a unique serial number, so I will need to plot these integers in a line chart and I will need to perform some simple calculations on them like getting the average or standard deviation.
- lbendlinSuper User
Split your binary by positions, split into rows, interpret the result as integer.
Would be good if you could post a couple of samples.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523