The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
@sudha, since there's not much context, I'm making the following assumptions in my reply. Please provide more details if these are not correct.
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])
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Fabric update to learn about new features.
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |