Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Use the Connect Server to Access Business b-ridge Data in Microsoft Power Pivot
Use the Connect Server to connect to live Business b-ridge data in the Power Pivot business intelligence tool.
This article will explain how to use the Connect Server to provide Business b-ridge data as OData services and then consume the data in Microsoft Excel's Power Pivot business intelligence tool. Follow the steps below to retrieve Business b-ridge data in Power Pivot.
Connect to Business b-ridge from Power Pivot
To work with live Business b-ridge data in Power Pivot, we need to connect to Business b-ridge from Connect Server, provide user access to the new virtual database, and create OData endpoints for the Business b-ridge data.
Add a Connect Server User
Create a User to connect to Business b-ridge from Power Pivot through Connect Server.
- Click Users -> Add
- Configure a User
- Click Save Changes and make note of the Authtoken for the new user
Connect to Business b-ridge from Connect Server
CData Connect Server uses a straightforward, point-and-click interface to connect to data sources and generate APIs.
- Open Connect Server and click Connections
- Select "Business b-ridge" from Available Data Sources
- Enter the necessary authentication properties to connect to Business b-ridge.
When connecting to Business b-ridge, CompanyKey, ProjectKey, and SubscriptionKey are required.
To obtain the SubscriptionKey, follow the steps below:
- Log in to Business b-ridge API Protal and go to "Profile" in the "Your name" menu.
- In your subscription section click "Main Key" to retrieve their respective values.
Authenticate to Business b-ridge Account
Set the following connection properties to connect:
- CompanyKey: Set the Business b-ridge Company Key of the connection destination.
- ProjectKey: Set the Business b-ridge Project Key of the connection destination.
- SubscriptionKey: Set the value "Subscription Key".
- Click Save Changes
- Click Privileges -> Add and add the new user (or an existing user) with the appropriate permissions (SELECT is all that is required for Reveal).
Add Business b-ridge OData Endpoints in Connect Server
After connecting to Business b-ridge, create OData Endpoints for the desired table(s).
- Click OData -> Tables -> Add Tables
- Select the Business b-ridge database
- Select the table(s) you wish to work with and click Next
- (Optional) Edit the resource to select specific fields and more
- Save the settings
(Optional) Configure Cross-Origin Resource Sharing (CORS)
When accessing and connecting to multiple domains from an application, there is a possibility of violating the limitations of cross-site scripting. In that case, configure the CORS settings in OData -> Settings.
- Enable cross-origin resource sharing (CORS): ON
- Allow all domains without '*': ON
- Access-Control-Allow-Methods: GET, PUT, POST, OPTIONS
- Access-Control-Allow-Headers: Authorization
Save the changes to the settings.
Import Business b-ridge Tables in Power Pivot
Follow the steps below to import tables that can be refreshed on demand:
- In Excel, click Data -> Get Data -> From Other Sources -> From OData Feed
- Fill out the OData feed URL (e.g. http://localhost:8080/odata.rsc)
- On the next screen, select Basic and configure the username and password. Password should be your auth token.
-
Select the tables that you would like to import into Excel
-
After selecting your table(s) and clicking Load, you should see the data appear in an Excel spreadsheet
-
Now, click the Power Pivot tab on the ribbon, and then select Add to Data Model. This can now work with your Business b-ridge data in Power Pivot.
Free Trial & More Information
If you are interested in connecting to your Business b-ridge data (or data from any of our other supported data sources) from Power Pivot, sign up for a free trial of CData Connect Server today! For more information on Connect Server and to see what other data sources we support, refer to our CData Connect page.