Forum Discussion

sudhav's avatar
sudhav
Helper V
2 years ago

Need alternative data types for Multivalued and Array data type

Hi Team, I need to import data from SAP to Fabric using Fabric. where some columns datatype is Array type and some tcolumns data type is Multivalued. instead of Array and Multivalued, which data ty...
Dataflows
Need Help
jjgb's avatar
jjgb
Microsoft Employee
2 years ago

Anonymous, since there's not much context, I'm making the following assumptions in my reply. Please provide more details if these are not correct.

  1. You're trying to use multivalued types in relational tables and not within analytical / calculation views.
  2. You're using the SAP HANA Connector with custom SQL queries.

 

The multivalued types are exposed by the HANA Connector as binary values, which when converted to text start with some non-printable characters. In my test, distinct values are delimited by a form feed character, #(000C) in the example below.

You could use something similar to the query below to extract the values. It won't fold to HANA.
If the number of items in the array varies by row, then you’d need to split the column into a list and then decide how to translate the list into columns / rows, or translate the list into a structured format like JSON or CSV.

In these examples my table is called Employee and it has a Phone column defined in HANA as:

Phone VARCHAR(15) ARRAY WITHOUT DUPLICATES

let

    PHONE = Value.NativeQuery(SapHana.Database("server", [Implementation="2.0"]),

"select #(lf)""ID"",#(lf)""FIRSTNAME"",#(lf)""LASTNAME"",#(lf)""PHONE""#(lf) from ""HANAUSER"".""EMPLOYEE""", null, [EnableFolding=true]),

    PHONE1 = PHONE{0}[PHONE],

    #"Imported Text" = Table.FromColumns({Lines.FromBinary(PHONE1,null,null,1252)}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Imported Text", "Column1", Splitter.SplitTextByDelimiter("#(000C)", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}})

in

    #"Changed Type"

 

Another option is to use a SAP HANA specific function, such as UNNEST in the example below to turn multivalued columns into rows, for example:

 

Value.NativeQuery(SapHana.Database("server", [Implementation="2.0"]),

"SELECT DISTINCT Phones.Number FROM UNNEST(""HANAUSER"".""EMPLOYEE"".Phone) AS Phones (Number)", null, [EnableFolding=true])