Using OmniSci as Data Source for Power BI
Microsoft Power BI is a popular business analytics solution that supports a wide range of data sources from SQL databases, web platforms such as Google Analytics and Salesforce, and Azure cloud. In this tutorial, we walk you through step-by-step instructions on how to use the world's fastest GPU-powered database - OmniSci - as a data source for Power BI.
OmniSci natively supports ODBC (Open Database Connectivity), and Power BI does as well, allowing communication with OmniSci through the Windows platform ODBC Driver Manager. The OmniSci-specific ODBC driver processes ODBC function calls, submits the SQL requests to OmniSci and return the results to Power BI. Note: The OmniSci ODBC driver is provided as part of OmniSci Enterprise Edition; contact your OmniSci Sales Representative for more details. . To see all of the Tier 1 (Core) ODBC functions supported by OmniSci, refer to our documentation..
Installing the OmniSci ODBC Software
Download the OmniSci ODBC Software using the URL provided by your OmniSci Sales Representative. The downloaded installation package has a filename format as OmniSciInstall_<major_version>.<minor_version> as shown below:
Run the installer program as a Windows Administrator and confirm to proceed with the installation.
By default the software will be installed under C:\Program Files\OmniSci\ODBC.
At the end of a successful installation, you get the following message:
ODBC Driver DSN Setup
Open the Administrator Tool and open the ODBC Data Sources (64-bit).
Select Add on the ODBC Data Source Administrator (64-bit):
Select the OmniSci Driver:
This will bring up the form to setup the Data Source Name (DSN) for OmniSci database. The form shown below is for the default settings for OmniSci version 4.6. Please make sure that the name of the database, the user name and password are correct for your environment. Use port 6274 for connecting to the database. After the form is filled, select Test to make sure you can connect to the OmniSci database.
If the settings in the DSN form are correct then you should be able to connect to OmniSci database and see the following success message:
Querying an OmniSci Table from Power BI
Open the Power BI application and select Get data.
From the list of connectors, choose Other -> ODBC.
You should be able to see the OmniSci DSN that was created in the previous step, select OK.
Enter a valid user name and password for the database, and press Connect.
The navigator window now shows the list of tables under the selected mapd database. Select the table flights_2008_7M which is usually added to the database as part of OmniSci installation. The Navigator window displays a preview of the flights table. At this point, you can edit the query before loading the table, by selecting Edit or you can Load the table.
You now have access to all the fields of the flights table, and you can create a visualization by selecting the chart type and dragging the desired fields from the Fields list.
Using the flights dataset, I created a dashboard with a bar chart showing the number of records for each airline and a scatter plot showing the obvious correlation between the departure delay along the X axis (depdelay) and arrival delay along the Y axis (arrdelay).
When you select Get Data -> Other -> ODBC, instead of getting the entire table, you can also get the desired columns using a SQL query as shown below:
The SQL query executes on OmniSci backend database, and a preview of the results is shown:
Upon selecting LOAD, all of the data defined by the query is imported into the Power BI cache. Now you are ready to build charts on the loaded data. If the data changes on the underlying tables on OmniSci, the changes are not reflected in the charts. You have to select Refresh to trigger a reimport of the data to reflect changes on the Power BI charts.
This basic tutorial shows how to work with OmniSci using Power BI, so I am not diving into the advanced integration points that both platforms offer. For example, I am thinking of creating an interactive Power BI dashboard alongside OmniSci's Choropleth chart that renders millions of polygons using GPU technology. If you have interesting ideas on how best to leverage OmniSci's fast SQL database and extreme rendering capabilities with Power BI visualization, then stop by our community forum to discuss.