Need alternative data types for Multivalued and Array data type

sudhav — Thu, 07 Sep 2023 09:11:00 GMT

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 type i need to use in Fabric(if i need to define datatypes manually), can someone let me know.

TIA

Re: Need alternative data types for Multivalued and Array data type

DennesTorres — Mon, 11 Sep 2023 08:43:10 GMT

Hi,

I don't have this specific experience, but tables in Fabric will not support arrays or multivalued columns. You may need to make a first import of the data as JSON, which supports this, and later transform the JSON in a flat model which fits your needs.

The flat model may result in more than a single table, creating a relationship between the main record and each related information on the multivalued columns.

Kind Regards,

Dennes

Re: Need alternative data types for Multivalued and Array data type

jjgb — Tue, 26 Sep 2023 21:17:10 GMT

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.

You're trying to use multivalued types in relational tables and not within analytical / calculation views.
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}})

#"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])

topic Need alternative data types for Multivalued and Array data type in Dataflow

Need alternative data types for Multivalued and Array data type

Re: Need alternative data types for Multivalued and Array data type

Re: Need alternative data types for Multivalued and Array data type