1. Explore and Analyze Data
In this video, we’ll talk about various tools available to explore, profile, and analyze data in Fabric.
2. Data Analysis
Once you have loaded your data to a lakehouse or warehouse, you can start to explore and analyze the data, and build reports.
Analysis of the data usually falls within four categories: Descriptive, Diagnostic, Predictive and Prescriptive.
3. Descriptive Analytics
Descriptive analytics shows what happened in the past. For example, when you analyze historical data you are observing and describing what happened.
4. Diagnostic Analytics
Diagnostic analytics attempts to explain why something happened.
For example, when analyzing historical sales data you might notice a decrease in sales in the last period. Drilling down by location can help you to identify a sales location that is not performing well.
5. Predictive Analytics
Predictive analytics attempts to predict what might happen in the future. For example, a trend line in a sales chart might help you forecast sales for the next periods.
6. Prescriptive Analytics
Finally, prescriptive analytics attempts to suggest actions that you might take to achieve an objective.
For example, analyzing sales data by location might suggest where you should focus your marketing efforts to increase sales in the future.
7. Data Profiling
The Power Query editor provides basic data profiling information. The column quality view shows the number of valid, error and empty values in a column.
8. Data Profiling
The column value distribution view shows the frequency and distribution of values in a column.
9. Data Profiling
The column profile view shows a more detailed look at the data in a column with a statistics summary and a value distribution chart.
10. SQL query tools
SQL is a key interface to query data in a lakehouse or warehouse. Fabric provides a SQL Query Editor and a Visual Query Editor.
11. SQL query tools
Outside of Fabric, you can connect to a lakehouse SQL analytics endpoint or a warehouse by specifying the SQL connection string in a query tool.
You can use tools such as SQL Server Management Studio, Azure Data Studio, Power BI Desktop, or any tool that supports OLE DB or ODBC SQL providers.
12. SQL query editor
The SQL query editor is a text editor for writing SQL queries.
13. Visual query editor
In contrast, the Visual query editor provides an easy to use visual interface that creates the SQL queries behind the scenes.
The interface looks like a simpler version of the Dataflow Power Query editor.
The Visual query editor is suitable for people familiar with Dataflows or who want to create quick queries without writing SQL.
14. Connecting using other SQL tools
To connect to a Lakehouse using a SQL tool outside of Fabric, you need the connection string of the SQL Analytics endpoint, which can be found in the lakehouse Settings page.
15. Connecting using other SQL tools
Likewise, you can find the SQL connection string for a warehouse in its Settings page.
16. Connecting from SQL Server Management Studio (SSMS)
Once you have the SQL connection string, you can use it to connect to a lakehouse or warehouse using SQL Server Management Studio.
17. Connecting from Azure Data Studio
You can use the same SQL connection string in Azure Data Studio.
18. XMLA endpoint
Fabric workspaces also have an XMLA endpoint. XMLA is an open standard protocol that allows client tools like SQL Server Management Studio and Power BI Desktop to connect and query semantic models.
19. XMLA endpoint
The URL of the XMLA endpoint includes the tenant and workspace name.
20. Let's practice!
Now, let's do a couple of exercises to practice using these tools.