Vertica
Power BI is a powerful business intelligence tool that allows users to connect, analyze, and visualize data from various sources. One of the sources that can be connected to Power BI is Vertica, a high-performance analytical database. In this article, we will explore how to connect to the Vertica data source from inside Power BI using Power Query M Language.
What is Power Query M Language?
Power Query M Language is a functional, case-sensitive language used to extract, transform, and load data in Power BI. It is used to create queries that can be used to connect to various data sources, transform data, and load it into Power BI. Power Query M Language is used to create custom functions, which can be reused in other queries.
Prerequisites
Before we begin, make sure you have the following:
– A Power BI account
– Access to a Vertica database
– Vertica ODBC driver installed on your computer
– A basic understanding of Power Query M Language
Connecting to Vertica from Power BI
To connect to Vertica from Power BI, follow these steps:
1. Open Power BI Desktop and click on “Get Data” from the Home menu.
2. Select “ODBC” from the list of available data sources.
3. In the “ODBC” dialog, select the “Vertica” data source and click “Connect.”
4. In the “Vertica ODBC Driver Setup” dialog, enter the connection details for your Vertica database, including the server name, port number, and database name. You can also use Windows authentication or specify a username and password for the connection.
5. Click “Connect” to connect to the Vertica database.
Querying Vertica Data in Power BI
Once you have connected to the Vertica database, you can use Power Query M Language to query and transform the data. Here are some examples of how to use Power Query M Language to query Vertica data:
Example 1: Get all data from a table
To get all the data from a table in Vertica, use the following M Language code:
let
Source = Odbc.DataSource(“dsn=Vertica“, [HierarchicalNavigation=true]),
dbo_TableName = Source{[Schema=”dbo”,Item=”TableName”]}[Data]
in
dbo_TableName
Replace “Vertica” with the name of your ODBC data source, “dbo” with the name of your schema, and “TableName” with the name of your table.
Example 2: Filter data by a specific value
To filter data in Vertica based on a specific value, use the following M Language code:
let
Source = Odbc.DataSource(“dsn=Vertica“, [HierarchicalNavigation=true]),
dbo_TableName = Source{[Schema=”dbo”,Item=”TableName”]}[Data],
FilteredRows = Table.SelectRows(dbo_TableName, each ([ColumnName] = “FilterValue”))
in
FilteredRows
Replace “Vertica” with the name of your ODBC data source, “dbo” with the name of your schema, “TableName” with the name of your table, “ColumnName” with the name of the column you want to filter on, and “FilterValue” with the value you want to filter on.
Example 3: Join two tables
To join two tables in Vertica, use the following M Language code:
let
Source = Odbc.DataSource(“dsn=Vertica“, [HierarchicalNavigation=true]),
dbo_Table1 = Source{[Schema=”dbo”,Item=”Table1″]}[Data],
dbo_Table2 = Source{[Schema=”dbo”,Item=”Table2″]}[Data],
JoinedTables = Table.Join(dbo_Table1, “JoinColumn”, dbo_Table2, “JoinColumn”, JoinKind.LeftOuter)
in
JoinedTables
Replace “Vertica” with the name of your ODBC data source, “dbo” with the name of your schema, “Table1” and “Table2” with the names of your tables, and “JoinColumn” with the name of the column you want to join on.
Conclusion
In this article, we explored how to connect to the Vertica data source from inside Power BI using Power Query M Language. We covered the prerequisites, steps to connect to Vertica, and examples of how to query Vertica data using Power Query M Language. With this information, you can now start exploring the power of Power BI and Vertica together.
