SapHana.Database

What is Power Query?

Power Query is a data transformation and cleansing tool that allows you to extract data from various sources, clean and transform it, and load it into a destination. Power Query is available as an add-in for Excel, as a standalone tool in Power BI, and as a built-in tool in SQL Server Integration Services (SSIS).

Power Query has a rich set of functions that allow you to perform various data transformations such as filtering, sorting, grouping, merging, pivoting, splitting, and unpivoting. The M function is one of the most powerful functions in Power Query that allows you to write custom code to manipulate data.

What is the SapHana.Database function?

The SapHana.Database function is a Power Query M function that allows you to extract data from SAP HANA databases. SAP HANA is an in-memory database that is used to store and retrieve large amounts of data quickly. The SapHana.Database function allows you to connect to a SAP HANA database, specify a SQL statement to extract data, and load the data into Power Query.

To use the SapHana.Database function, you need to provide the following parameters:

– Server: The name of the SAP HANA server.

– Database: The name of the SAP HANA database.

– Query: The SQL statement to extract data.

– Options: Any additional options such as user ID and password.

The SapHana.Database function returns a table that contains the data extracted from the SAP HANA database. You can then use the rich set of Power Query functions to transform the data as needed.

The M Code Behind the SapHana.Database Function

The M code behind the SapHana.Database function is a bit complex, but it is well documented and easy to understand. Here is the M code behind the SapHana.Database function:

let

Source = (server as text, database as text, query as text, options as record) =>

let

url = “jdbc:hana://” & server & “:3” & options[Port] & “;databaseName=” & database,

driver = “com.sap.db.jdbc.Driver”,

queryTimeout = 0,

properties = [

#”user” = options[UserID],

#”password” = options[Password],

#”driver” = driver,

#”url” = url,

#”queryTimeout” = queryTimeout

],

conn = JavaNew(“java.sql.DriverManager”).getConnection(properties),

stmt = conn#”[email protected]createStatement”(),

rs = stmt#”[email protected](query)”,

metadata = rs#”[email protected]”,

columns = metadata#”[email protected]”,

data = List.Generate(

() => [Row = rs#”[email protected]()”, Done = rs#”[email protected]”],

each not [Done],

each [Row = rs#”[email protected]()”, Done = rs#”[email protected]”],

each [Row][Row]

),

result = Table.FromRecords(data, columns)

in

result

in

Source