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

Here is a link to sample data in pbix file, it has the data along with the expected outcome for one Binary ( I am using serial number for every row to make it more clear) : https://drive.google.com/file/d/1Jz6TfP7tUTYTeVqsTTk3eSIuF_xAg4yG/view?usp=sharing

I didn't split the rows, I only used the following line on the original binary columns since I can't copy them as they are :

Binary.ToText([DATA_ARRAY1], BinaryEncoding.Base64)

If you need me to perform any kind of steps on the binary columns before using the Base64 please let me know.

lbendlin's avatar
lbendlin
Super User
2 years ago

It says that the encoding is invalid.

 

The data is cut off at 1024 bytes, maybe that is causing the error.

 

Anyway, since you already have the binary, do the following steps:

 

1. Binary.Split with a page size of 4

2. with the result do Binary.ToList

3. Now you have to check the most significant bit (bit 7 of the first value) - that is your sign, with 1 meaning negative.  All the other bits from the first value and all the bits from the other three values constitute your number.  You can use the Number BitShift functions to add them together.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Here is an example of the mechanics, using the brute force approach rather than two's complement.

     

     

     

     

     

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjQwVtJRMjKFkKZQMlYnWsnUEoeEhREOCUMDEM8AjsFiFoZAtiGKmJEB2GiEWCwA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Byte3 = _t, Byte2 = _t, Byte1 = _t, Byte0 = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Byte3", Int16.Type}, {"Byte2", Int16.Type}, {"Byte1", Int16.Type}, {"Byte0", Int16.Type}}
  ),
  BLOB = Binary.Combine(
    Table.AddColumn(#"Changed Type", "Custom", each #binary({[Byte0], [Byte0], [Byte2], [Byte3]}))[
      Custom
    ]
  ),
  Items = Binary.Split(BLOB, 4),
  #"Converted to Table" = Table.FromList(
    Items,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Converted to Table",
    "Result",
    each
      let
        a = Binary.ToList([Column1]),
        b = ((a{0} * 256 + a{1}) * 256 + a{2}) * 256 + a{3}
      in
        if b > 2147483647 then b - Number.Power(256, 4) else b,
    Int32.Type
  )
in
  #"Added Custom1"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      lbendlin  I believe I now have a better understanding of the mechanics right now, but could you please explain how you obtained the text below? Specifically, how can I apply the code to my original columns?

       

      i45WMjQwVtJRMjKFkKZQMlYnWsnUEoeEhREOCUMDEM8AjsFiFoZAtiGKmJEB2GiEWCwA

       

      The dataset I am working with consists of approximately 3 million unique serial numbers, and for each serial number, I have the two arrays (the order of the array is critical, it's a time series). Would this process still be efficient for such a dataset?

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        That text is just representing the sample data I used

         

        By the way, I was going at this way too cute. There are much simpler ways of achieving your goal

         

         

         

         

         

        ...
    Items = Binary.Split(BLOB,4),
    #"Converted to Table" = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Result2", each let b=BinaryFormat.Record([val = BinaryFormat.SignedInteger32])in b([Column1])[val],Int32.Type)
in
    #"Added Custom"

         

         

         

         

        You can even skip the Table conversion and use List transforms directly at this point. 

         

         

         

        ...

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

         

         

         

         

        Next step would be to use List.Zip to do a side merge between the three blobs,  and finally you would convert the list of lists to a table.

         

        This is all very low level stuff so it should have a good performance.  Worst case you can use a Binary.Buffer on your blobs.