This article is for Hosted clients who want to connect to their hosted Estimates Database (DESTINIData) for the purposes of creating a custom Power BI dashboard.
Prerequisites for Database Access
1. Whitelisting, Server Name, Database Name
To gain direct access to your database in the Hosted environment requires having your Company's IP address whitelisted for server access. Please contact Support to request an IP whitelisting.
As part of our standard procedure, when we whitelist your IP, we will also issue you the server name and your database name (the server name and database can also be found in your standard Power BI dashboard issued by Beck Tech, via the Data Source menu).
Due to security considerations, we cannot whitelist personal home IP addresses.
2. Credentials:
When our standard Power BI dashboards are deployed to a client, we also issue a username and password that is used to set up a refresh schedule in Power BI Online. You should have a record of those credentials, and they will be required here. If you do not, please contact Support.
3. SQL Server Management Studio (SSMS):
This guide demonstrates database access using SSMS.
Accessing the Database To See the Tables and Views
- Open SSMS
- On the login window, enter the server name and credentials that were provided.
2. Click the Options tab and Enter the database name and click Connect
Power BI Desktop: Updating Credentials
If you have been whitelisted as detailed above, you will also have the ability to open the .pbix dashboard file in Power BI Desktop, where you will be able to quickly view the dashboard or make edits in the Power Query Editor.
When opening in Power BI Desktop, you may be prompted to enter credentials. Here are the steps to do so:
1. Open the .pbix file in Power BI Desktop and click on click on the 'Transform Data' button on the top Ribbon.
2. The Power Query Editor will open and you will see a prompt: "Please specify how to connect'. Click the 'Edit Credentials' button.
3. Select 'Database' on the left menu. Then input the username and password you were issued with your PBI files. Lastly, in the 'Select which Level' at the bottom, choose the option that shows both the server AND your database name (eg. servername.database.windows.net;your_destinidata_ database_name_destinidata).
4. Click 'Connect'.
DESTINIData SQL Views and Descriptions
For a list of SQL views and their definitions, please see the article; Custom Power BI Building: SQL Views List.