Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Connect to Live Business b-ridge Data in MicroStrategy through CData Connect Server
Create a virtual database for Business b-ridge in CData Connect Server and connect to it from MicroStrategy.
MicroStrategy is an analytics and mobility platform that enables data-driven innovation. When you pair MicroStrategy with CData Connect Server, you gain database-like access to live Business b-ridge data from MicroStrategy, expanding your reporting and analytics capabilities. In this article, we walk through creating a virtual database for Business b-ridge in Connect Server and connecting to Connect Server in MicroStrategy to create a simple visualization of Business b-ridge data.
As a hosted integration platform, Connect Server is ideal for working with cloud-based BI and analytics tools. With no servers to configure or data proxies to set up, you can simply use the web-based UI to create a virtual database for Business b-ridge and connect from MicroStrategy to start performing analytics based on live Business b-ridge data.
Connect to Business b-ridge from MicroStrategy
To work with live Business b-ridge data in MicroStrategy, we need to create a virtual database for Business b-ridge in Connect Server.
Create a Virtual SQL Server Database for Business b-ridge Data
CData Connect Server uses a straightforward, point-and-click interface to connect to data sources and generate APIs.
-
Login to 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.
With the virtual database created, you are ready to connect to Business b-ridge data from MicroStrategy.
Connect to and Visualize Business b-ridge Data Using MicroStrategy
You can connect to Business b-ridge in MicroStrategy by adding a data source based on the native SQL Server functionality. Once you have created a data source, you can build dynamic visualizations of Business b-ridge data in MicroStrategy.
- Open MicroStrategy and select your account.
-
Click Add External Data, select Databases, and use Select Tables as the Import Option.
- In the Import from Tables wizard, click to add a new Data Source.
- Select "SQL Server" in the Database menu and select "SQL Server 2017" in the Version menu.
-
Sat the connection properties as follows:
- Server Name: e.g. connect_server_url
- Port Number: 1433
- Database Name: the name of your Business b-ridge connection (e.g. BusinessBridge1)
- User: a Connect Server user
- Password: a Connect Server password
- Data Source Name: a name for the new external data source, like "CData Connect Business b-ridge"
-
Expand the menu for the new data source and choose "Edit Catalog Options"
-
Edit the "SQL statement retrieve columns ..." query to include TABLE_SCHEMA = '#?Schema_Name?#' in the WHERE clause, and click Apply and then OK (the complete query is below).
SELECT DISTINCT TABLE_SCHEMA NAME_SPACE, TABLE_NAME TAB_NAME, COLUMN_NAME COL_NAME, (CASE WHEN (DATA_TYPE LIKE '%char' AND (CHARACTER_SET_NAME='utf8' OR CHARACTER_SET_NAME='usc2')) THEN CONCAT('a',DATA_TYPE) ELSE DATA_TYPE END) DATA_TYPE, CHARACTER_MAXIMUM_LENGTH DATA_LEN, NUMERIC_PRECISION DATA_PREC, NUMERIC_SCALE DATA_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (#TABLE_LIST#) AND TABLE_SCHEMA='#?Schema_Name?#' ORDER BY 1,2,3
- Select the new data source and select the Namespace that corresponds to your virtual Business b-ridge database (like BusinessBridge1).
-
Drag tables into the pane to insert then.
Note: Since we create a live connection, we can insert whole tables and utilize the filtering and aggregation features native to the MicroStrategy products to customize our datasets.
-
Click Finish, choose the option to connect live, save the query, and choose the option to create a new dossier. Live connections are possible and effective, thanks to high-performance data processing native to CData Connect Cloud.
-
Choose a visualization, choose fields to display, and apply any filters to create a new visualization of Business b-ridge data. Data types are discovered automatically through dynamic metadata discovery. Where possible, the complex queries generated by the filters and aggregations will be pushed down to Business b-ridge, while any unsupported operations (which can include SQL functions and JOIN operations) will be managed by the CData SQL engine embedded in Connect Cloud.
- Once you have finished configuring the dossier, click File -> Save.
Using CData Connect Server with MicroStrategy, you can easily create robust visualizations and reports on Business b-ridge data. For more information on creating virtual databases for Business b-ridge (and more than 200 other data sources), visit the Connect Server page. Sign up for a free trial and start working with live Business b-ridge data in MicroStrategy.