CRIANDO UM CUBO NO SSAS (ANALYSIS SERVICES) E CONSUMINDO NO POWER BI

Summary

In this tutorial, users are guided through the process of using SQL Server Analysis Services (SSAS) to create a data cube, deploy it, and integrate it with Power BI. Starting with a connection to an SQL Server database, the user imports data, creates a cube with dimensions and measures, and deploys it to an SSAS server. The final step involves connecting Power BI to the SSAS cube, validating the calculations, and ensuring data accuracy. Best practices are also highlighted, such as transforming data before importing and creating measures directly in the cube to ensure proper governance and avoid errors.

Takeaways

• 😀 Start by creating an Analysis Services project in Visual Studio and naming it appropriately (e.g., Cubo Comercial).
• 😀 Ensure that the project is connected to a compatible SQL Server (e.g., SQL Server 2019) for deployment.
• 😀 Always test the database connection before proceeding with project creation to ensure seamless integration.
• 😀 Import necessary tables (e.g., Cliente, Loja, Produto, FatoVendas) from the SQL Server database into the SSAS project.
• 😀 Use the 'Transform Data' option in Power Query to verify data integrity and check for column issues before importing.
• 😀 Create measures in SSAS, such as summing sales value (Valor) or quantity sold (Quantidade Vendida), to build your cube's calculations.
• 😀 Deploy the SSAS cube to the server by selecting the appropriate deployment option in Visual Studio, and ensure no errors during deployment.
• 😀 In Power BI, connect to the SSAS cube using the 'Analysis Services' connector and select the live connection option.
• 😀 In Power BI, only the cube will appear as a data source (not individual tables). Ensure you select the cube to access the data.
• 😀 Verify the correctness of your measures by dragging them into a Power BI report and confirming they aggregate correctly.
• 😀 Best practice is to create measures in SSAS rather than relying on Power BI's dynamic table functions for accurate and efficient reporting.

Q & A

What is the main purpose of the tutorial in the video?

The main purpose of the tutorial is to demonstrate how to create an OLAP cube using SQL Server Analysis Services (SSAS), deploy it to a server, and connect it to Power BI for testing and visualization of measures.

What tool is used to create the SSAS project in the tutorial?

The SSAS project is created using Visual Studio, a development environment commonly used for SQL Server Analysis Services (SSAS) projects.

What type of database is connected to in the tutorial?

The tutorial connects to an SQL Server database, specifically using a model known as a star schema with fact and dimension tables.

What is a star schema and how is it used in this tutorial?

A star schema is a type of data warehouse schema that consists of a central fact table surrounded by dimension tables. In this tutorial, the fact table 'Fato Vendas' and the dimension tables like 'Cliente', 'Loja de Produto' are used to create the OLAP cube.

Why is it important to transform the data before importing it into SSAS?

Transforming the data before importing it helps ensure that the data types are correct, removes unnecessary columns, and ensures the data is clean and ready for analysis. This step is common in real-world scenarios to avoid issues later.

What measures are created in the SSAS cube in this tutorial?

Two measures are created in the SSAS cube: 'Total RS' (total sales value) and 'Quantidade Vendida' (total quantity sold). These are calculated by summing the respective columns from the 'Fato Vendas' table.

How is the SSAS cube deployed in the tutorial?

The SSAS cube is deployed by selecting the 'Deploy' option in Visual Studio. The cube is then deployed to the SSAS server, where it can be accessed for further analysis.

What happens when the cube is successfully deployed?

Once the cube is successfully deployed, it becomes available on the SSAS server. It can then be accessed in Power BI for testing and visualization.

How does Power BI connect to the SSAS cube?

Power BI connects to the SSAS cube using the 'Live' connection mode. This means Power BI accesses the cube directly without importing the data, ensuring real-time data querying.

Why is it important to test the measures in Power BI after connecting to the cube?

Testing the measures in Power BI ensures that they are calculating correctly and returning the expected results. It is a key step in verifying the accuracy of the cube and the measures created within it.