Create and share visualizations

Applies to: ✅ SQL database in Microsoft Fabric

In this tutorial, learn how to use multiple tools to analyze data stored in your SQL database in Fabric or the SQL analytics endpoint.

Prerequisites

• Complete all the previous steps in this tutorial.

Find the connection strings to the SQL database

1. To get your server and database name, open your SQL database in Fabric portal view and select the Settings button in the icon bar.

   Note

   The SQL database in Fabric and its SQL analytics endpoint have different connection strings. For this tutorial step, connect to the SQL database. You'll use the SQL analytics endpoint in the next tutorial step.

   

2. Select Connection Strings and you'll see a long string that starts with Data Source... From there, select the text between the characters tcp: through the characters ,1433. Ensure that you select the entire set of characters there and nothing more for the server name.

3. For the database name, select all the characters between the characters Initial Catalog= and ;MultipleActiveResultSets.

You can now use these SQL strings in your connection area for tools such as Power BI or SQL Server Management Studio. For Visual Studio Code with the MSSQL extension, you can paste the entire connection string in the first text box where you make a database connection, so you don't have to select only the server and database names.

Power BI visualization creation

To analyze the data in your SQL analytics endpoint in Power BI, create a semantic model that provides a logical view of your data, including the tables, relationships, and measures used for analysis. After creating the semantic model, customize it to meet your analytical requirements.

1. In your SQL analytics endpoint view, select the New semantic model button in the ribbon.

   

2. From the resulting view, provide a name for your semantic model. Select all tables and the views created in the previous tutorial, including SupplyChain.vTotalProductsByVendorLocation, and select Confirm.

   

3. From the resulting view, zoom in and scroll over until you see the vTotalProductsByVendorLocation object. Select it.

   

4. In the properties, select the Location field, and expand the Advanced properties selection. You might need to scroll to find it. Set the value of Summarize by to None. This ensures that when the field is used, it's a discrete number, not a mathematical summarization of that number.

Create a report

In the semantic model view, use the menu option for New report. Create a report based on the views you created in the SQL analytics endpoint in a previous tutorial step.

1. Select the New report button in the ribbon.

   

2. Expand the vTotalProductsByVendor data object. Select each of the fields you see there. The report takes a moment to gather the results to a text view. You can size this text box if desired.

   

3. Select in a blank area of the report canvas, and then select Location in the Data fields area.

4. Select a value in the box you just created – notice how the first selection of values follows the selection you make in the second box. Select that same value again to clear the selection.

   

5. Select in a blank area of the reporting canvas, and then select the Supplier field.

6. Once again, you can select the name of a supplier and the first selection shows the results of just that supplier.

Save the Power BI item for sharing

You can save and share your report with other people in your organization.

1. Select the Save button in the icon box and name the report suppliers_by_location_report, and ensure you select the correct Workspace for this tutorial.

2. Select the Share button in the icon bar to share the report with people in your organization who have access to the proper data elements.

   

Next step