SapHana.Database

D

T

The M Code Behind the Power Query M function 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


The M code consists of a function called Source that takes four parameters: server, database, query, and options. The function returns a table that contains the data extracted from the SAP HANA database.

The M code first constructs the JDBC connection string using the server, database, and port options. It then creates a JDBC connection object and a statement object using the DriverManager and creates a result set object using the SQL statement. The M code then extracts the metadata and column information from the result set and generates a list of records representing the data extracted from the result set.

Finally, the M code creates a Power Query table from the list of records and the column information extracted from the result set. The resulting table can then be used in other Power Query functions to transform and clean the data.

The M code behind the Power Query M function SapHana.Database is a bit complex, but it is well documented and easy to understand. The SapHana.Database function allows you to extract data from SAP HANA databases, which is a powerful feature for those who work with SAP HANA databases. By understanding the M code behind the SapHana.Database function, you can gain a deeper understanding of how Power Query works and how you can use it to manipulate data from different sources.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)