# Connecting from Power BI (deprecated) {% hint style="danger" %} This way of loading rule solver data to Power BI is no longer recommended. We have provided the [Business Intelligence API](https://docs.decisionrules.io/doc/api/bi-api) which can be used to achieve the same goal in a much easier way. Check out the [Connect Power BI to BI API](https://docs.decisionrules.io/doc/business-intelligence/power-bi-connectivity/connect-power-bi-to-business-intelligence-api) tutorial. {% endhint %} ## How to Connect PowerBI and DecisionRules To aproach DecisionRules data (Rule Engine statistics, Rule Input/Output data etc.) seated in MongoDB database we recomend to use/setup BI Connector and MongoDB ODBC Driver for BI Connector. ![](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2Fek024HQYd6ydrLIPe17c%2Fimage.png?alt=media\&token=ed2d18d5-46be-4ee8-8e99-d3aa47a2a8f0) The MongoDB ODBC Driver for BI Connector provides connectivity between a SQL client and the MongoDB Connector for BI. The ODBC driver enables users to create a Data Source Name (DSN) and connect a variety of BI tools to the BI Connector. It includes the BI Connector authentication plugin, which implements the client side of MongoDB-supported authentication mechanisms. ### **Step 1 - MongoDB** In section Security - Database Access **create new** **User**, e.g. "dbReportUser" with Role "readAnyDatabase". In section Security - Network Access also **setup IP address** of host that will aproach your MongoDB database (value 0.0.0.0/0 means Any host). ### **Step 2 - BI Connector** The MongoDB Connector for BI allows you to use your BI tool of choice to visualize, discover, and report against MongoDB data using standard SQL queries. There are two options how to put it between ODBC Driver and MongoDB: #### A) MongoDB BI Connector running in Cloud If you run your DecisionRules on MongoDB Atlas (MongoDB Cloud Services), just edit configuration of MongoDB database (in Additional Settings - Advanced Settings) and **switch on** the option "**Enable Business Inteligence Connector**". ![](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2F8llJciI3ycwkA5GQnY6j%2Fimage.png?alt=media\&token=2db32cd2-681d-4ab7-b919-059c3502a020) Then choose option **Connect** - **Connect Business Intelligence Tool** and see the essential parameters for MongoDB ODBC Driver. ![](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2Fg2KIMvhX71DUB7O2Us3L%2Fimage.png?alt=media\&token=1fc27db0-1d9e-4c9f-905f-4c3bb5b6e183) #### **B)** MongoDB BI Connector running localy We prefer to run the MongoDB BI Connector localy (it means on your local server/PC). It does not matter if your DecisionRules MongoDB is installed in your Cloud or on your local server, you have to first **download and install** (Prerequisite: [Visual C++ Redistributable for Visual Studio 2015](https://www.microsoft.com/en-us/download/details.aspx?id=48145) has been installed on your host) the suitable **BI Connector for MongoDB** from: {% embed url="" %} **Run the downloaded *****.msi***** file** and follow the wizard instructions to install the files. The binaries install into a *bin* directory (e.g. *C:\Program Files\MongoDB\Connector for BI\2.14\bin*) inside the installation directory. You can delete the old binaries. To help you get started MongoDB BI Connector (*mongosqld.exe* file), a sample of [configuration file](https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#std-label-config-format) named *example-mongosqld-config.yml* is included with the installation package. To learn how to start BI Connector with a configuration file, refer to the *mongosqld* documentation section on the [Configuration File](https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#std-label-config-format). **To start BI Connector localy with necessary parameters** for connecting DecisionRules MongoDB **use following example of configuration file**: {% file src="" %} **Inside this file you have to edit variable "mongodb: net: uri"** by string you will find in your MongoDB, where you have tu choose option **Connect** - **Connect your application** and select DRIVER "Node.js" and its VERSION "2.2.12 or later". **The underlined text** (see picture bellow) "mongodb://.....27017" **is the value you have to writedown into the variable "mongodb: net: uri"** (in the file "mongosqld-config-for-DecisionRules.yml" file). Do not forget to **edit also variable "username" and "password"** that you have created in Step 1. ![](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FbSw6tvfF4ep6pSUWcNuW%2Fimage.png?alt=media\&token=b545893d-c085-4277-863c-4464f68dcb14) Last very important variable in "mongosqld-config-for-DecisionRules.yml" file is "schema: path". For DecisionRules MongoDB you can **use following template of database objects definition**: {% file src="" %} You are now ready to launch the BI Connector, but remember, if your MongoDB instance uses authentication, your BI Connector instance must also use authentication. The user that connects to MongoDB via the *mongosqld* program must have permission to read from all the namespaces you wish to sample data from. **In Command Prompt go to the directory where the BI Connector has been installed** (and where you put also the "mongosqld-config-for-DecisionRules.yml" and "schemaDecisionRules.drdl" files) **and write "mongosqld --config mongosqld-config-for-DecisionRules.yml"** and **press Enter**. You should see something like this: ![](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FeWbgFPAfmhGoFOcopUnf%2Fimage.png?alt=media\&token=16a094c0-92bb-4ee1-a345-4db5d053dc5e) ### **Step 3 - ODBC Driver** **Download and install** the suitable **ODBC Driver** for MongoDB from: {% embed url="" %} Start the Microsoft ODBC Data Sources program (choose the program version, 64-bit or 32-bit, which is appropriate for your system and ODBC driver version), select **System DSN**, then click **Add** and **choose** the **MongoDB ODBC** ... **Unicode Driver** from the list of available drivers. Click **OK**. **The following ODBC parameters are required:** * **Data Source Name:** A name of your choice * **TCP/IP Server:** The Hostname specified in the MongoDB Connect BI Tool dialog (MongoDB BI Connector running in Cloud) or IP address (*bindIp* variable) defined in *momgosqld* configuration file (MongoDB BI Connector running localy) * **Port:** The Port number specified in the MongoDB Connect BI Tool dialog (in MongoDB BI Connector running in Cloud the default is 27015, in MongoDB BI Connector running localy it is defined by variable *port* in n *momgosqld* configuration file) * **Database:** The name of the DecisionRules database (the default is Decision) * **User:** Enter either the user specified in the MongoDB Connect BI Tool dialog (in our example it is dbReportUser). The user is specified in the following format: Username?source=AuthDB where AuthDB is the authentication database for the user:\ \- If AuthDB=admin you can omit "?source=admin" string\ \- If you are using Username and Password (SCRAM-SHA-1) authentication, the expected authenticating database is "admin"\ \- If you are using LDAP (PLAIN) authentication, the expected authenticating database is "$external" (EXAMPLE: myTestUser?source=$external) * **Password:** The password that corresponds to the specified User (in our example it is dbReportUser). Click **Test** to validate the ODBC connection. If the connection is successful, click **OK** to add the DSN. If the connection fails, check to make sure your database user is correctly authenticated for the database named in the connection. ![Using MongoDB BI Connector running in Cloud](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2F3bQG0wUDEunU5jFIqFyX%2FConnect_to_MongoDB_from_PowerBI_step10_inCloud.png?alt=media\&token=a62cce54-6ca4-422a-a5f3-f538f746aca0) ![Using MongoDB BI Connector running localy](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FuC6E4I582eizsK5EkrQo%2FConnect_to_MongoDB_from_PowerBI_step10_onPremise.png?alt=media\&token=9107c6cc-e235-49ae-bb99-4641a5cf0a11) ### Step 4 - Power BI Connection I you named your System DSN "DecisionRules\_MongoDB" or "MongoDB\_via\_localBIconnector" (as mentioned above) you can immediately use our **Power BI Template Files** - see bellow: {% file src="" %} {% file src="" %} **Just download one of these files** (depending of your BI Connector solution) **and open it**. If you will see following Warning: There are pending changes in your queries that haven't been applied. Choose "**Apply Changes**". In the **Model** section you should see the following tables and their relationships: ![](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FyTJDer1wE6mXlo4vFJ9C%2Fimage.png?alt=media\&token=4845209e-0e0d-4a4d-bd69-83d0c1a5e75d) In the **Report** section you should see the prepared visualizations like "Dashboard", "API Calls per Time", "API Calls per Rule" or report of particular rule called "Client Profitability 2" (in this case of reporting a specific rule you will not see any data, this report and its data source are in template only for your inspiration how you can connect and visualize data about a particular rule). See bellow several expamples from the eppTec Power BI Template File: ![Dashboard overall](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FxWF9atCOFjsKy41r8Q5b%2Fimage.png?alt=media\&token=27b55ae6-9679-4836-bbb0-d7ef59acbe37) ![Dashboard for particular User](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FwZ3FXOGmwchTWpMiHt3z%2Fimage.png?alt=media\&token=98631516-cf05-47c6-81de-c365cc1ff5eb) ![All API Calls in particulat Time Period](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FeJHVpW0wwERwfkNFu3P9%2Fimage.png?alt=media\&token=b2d1bf2f-2410-4e09-b5b2-f3ef7e9de409) ![The most called Rules](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FVjeRNqLM5NbQDyOJy3sj%2Fimage.png?alt=media\&token=b513a2a0-2cac-445e-9184-7d89fe4a46ea) ![Example of reporting output values for a particular Rule](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2FfchzJMSRjZM7aPk5ylJy%2Fimage.png?alt=media\&token=44f371f6-ffc1-4e07-802d-a54d9ff67056) ![Example of reporting dependencies between output and input values for a particular Rule](https://437457296-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN4F4-qybg8XDATvios%2Fuploads%2Fw5lE7zUYmbxjpiRaQbjG%2Fimage.png?alt=media\&token=cbc09bdf-a028-476c-95c5-9e54355e3cb6)