We are proud to share our inclusion in the 2024 Gartner Magic Quadrant for Data Integration Tools. We believe this recognition reflects the differentiated business outcomes CData delivers to our customers.
Get the Report →Use the Connect Server to Access Google Cloud Storage Data in Microsoft Power Pivot
Use the Connect Server to connect to live Google Cloud Storage data in the Power Pivot business intelligence tool.
This article will explain how to use the Connect Server to provide Google Cloud Storage data as OData services and then consume the data in Microsoft Excel's Power Pivot business intelligence tool. Follow the steps below to retrieve Google Cloud Storage data in Power Pivot.
Connect to Google Cloud Storage from Power Pivot
To work with live Google Cloud Storage data in Power Pivot, we need to connect to Google Cloud Storage from Connect Server, provide user access to the new virtual database, and create OData endpoints for the Google Cloud Storage data.
Add a Connect Server User
Create a User to connect to Google Cloud Storage 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 Google Cloud Storage 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 "Google Cloud Storage" from Available Data Sources
- Enter the necessary authentication properties to connect to Google Cloud Storage.
Authenticate with a User Account
You can connect without setting any connection properties for your user credentials. After setting InitiateOAuth to GETANDREFRESH, you are ready to connect.
When you connect, the Google Cloud Storage OAuth endpoint opens in your default browser. Log in and grant permissions, then the OAuth process completes
Authenticate with a Service Account
Service accounts have silent authentication, without user authentication in the browser. You can also use a service account to delegate enterprise-wide access scopes.
You need to create an OAuth application in this flow. See the Help documentation for more information. After setting the following connection properties, you are ready to connect:
- InitiateOAuth: Set this to GETANDREFRESH.
- OAuthJWTCertType: Set this to "PFXFILE".
- OAuthJWTCert: Set this to the path to the .p12 file you generated.
- OAuthJWTCertPassword: Set this to the password of the .p12 file.
- OAuthJWTCertSubject: Set this to "*" to pick the first certificate in the certificate store.
- OAuthJWTIssuer: In the service accounts section, click Manage Service Accounts and set this field to the email address displayed in the service account Id field.
- OAuthJWTSubject: Set this to your enterprise Id if your subject type is set to "enterprise" or your app user Id if your subject type is set to "user".
- ProjectId: Set this to the Id of the project you want to connect to.
The OAuth flow for a service account then completes.
- 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 Google Cloud Storage OData Endpoints in Connect Server
After connecting to Google Cloud Storage, create OData Endpoints for the desired table(s).
- Click OData -> Tables -> Add Tables
- Select the Google Cloud Storage 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 Google Cloud Storage 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 Google Cloud Storage data in Power Pivot.
Free Trial & More Information
If you are interested in connecting to your Google Cloud Storage 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.