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_NAME ...
Need Help
  • 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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago
lbendlinYou're a rockstar, thank you so much.

After checking with the database admin, it turns out that the data in the BLOBs is LE (little endian). or BE (big endian).So, following your direction, I changed the line ( for all, DATA_ARRAY1,DATA_ARRAY2, CFG_FILE):
 

 

List.Transform(
    Binary.Split([DATA_ARRAY1],4),
    each BinaryFormat.Record([val = BinaryFormat.SignedInteger32])(_)[val])

 

To be:

 

List.Transform(
    Binary.Split([DATA_ARRAY1], 4),
    each BinaryFormat.ByteOrder(
        BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
        ByteOrder.LittleEndian
    )(_)[val]
)

 


And it's working perfectly now! I can see the correct numbers. But there remain three issues:

1) It's essential to identify to which serial number does the array belong so I need the serial number to be in the result (the seconds will be nice to have, it will reset to 0 for every new serial number):

 


2) I am not sure what's the best way to store the data becuase considering that I have around 3 millions unique serial numbers, each with 3 arrays with 1000 rows each, this could reach a billion data point if I follow the structure in the above image. ( I can ask this question in another post if that's better)

3) I also noticed that the code returns 3700 elements but I was expecting only 1087 elements (rows). So I asked him about this and he said that the 3700 is the the maximum elements of the array. There is DATA_POINTS column in the database that tells =exactly how many elements of data are stored in each array for DATA_ARRAY1 and DATA_ARRAY2 for every serial number.  So for the sample serial number we have, the number of data points is 1087. How can I incorporate this in the code?

lbendlin's avatar
lbendlin
Super User
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks lbendlin, here is the revised code where I also added an index column (Seconds) that resets with every new serial number:

     

    let
 Source =....,
 GSDATA = .......,
 Table1 = .........,
 #"Kept First Rows" = Table.FirstN(Table1, 10),
 #"Removed Other Columns" = Table.SelectColumns(
   #"Kept First Rows",
   {"SERIAL_NUM", "TEST_DATE", "DATA_ARRAY1", "DATA_ARRAY2", "CFG_FILE", "DATA_POINTS"}
 ),
 #"Added Custom3" = Table.AddColumn(
   #"Removed Other Columns",
   "ALL",
   each Table.AddIndexColumn(
     Table.Combine(
       List.Transform(
         List.Zip(
           {
             List.FirstN(
               List.Transform(
                 Binary.Split([CFG_FILE], 4),
                 each BinaryFormat.ByteOrder(
                   BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
                   ByteOrder.LittleEndian
                 )(_)[val]
               ),
               [DATA_POINTS]
             ),
             List.FirstN(
               List.Transform(
                 Binary.Split([DATA_ARRAY1], 4),
                 each BinaryFormat.ByteOrder(
                   BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
                   ByteOrder.LittleEndian
                 )(_)[val]
               ),
               [DATA_POINTS]
             ),
             List.FirstN(
               List.Transform(
                 Binary.Split([DATA_ARRAY2], 4),
                 each BinaryFormat.ByteOrder(
                   BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
                   ByteOrder.LittleEndian
                 )(_)[val]
               ),
               [DATA_POINTS]
             )
           }
         ),
         (x) => Table.FromRows({x}, {"CFG", "DATA1", "DATA2"})
       )
     ),
     "Secondes",
     0,
     1
   )
 ),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Added Custom3", "ALL", {"CFG", "DATA1", "DATA2", "Secondes"}, {"CFG", "DATA1", "DATA2", "Secondes"})
in
    #"Expanded ALL"

     

    Result: