Question 9

Explanation

The question involves developing a Power BI report for BrightLens Manufacturing, combining sales data from a shared semantic model (Analysis Services Tabular) and inventory data from a cloud-based database into a single report to analyze sales and inventory performance. The challenge is integrating these distinct data sources efficiently while leveraging their native capabilities. Let’s explore the best approach.

• Requirements Breakdown:

  • Sales data: Stored in an Analysis Services Tabular model (a shared semantic model), typically hosted on-premises or in Azure, optimized for analytics.

  • Inventory data: Stored in a cloud-based database (e.g., Azure SQL Database), likely transactional.

  • Combined report: A single report showing both sales and inventory performance, implying data integration in Power BI.

• Solution Steps:
  The best method is to use a live connection for the Analysis Services Tabular model and import the inventory data into Power BI Desktop:

  • Live Connection to Semantic Model: In Power BI Desktop, select "Get Data" > "Analysis Services," enter the server details, and choose "Connect Live." This links directly to the Tabular model without importing data, leveraging its prebuilt measures and relationships. The Microsoft documentation on live connections states, "A live connection queries the Analysis Services model in real time, ideal for shared semantic models."

  • Import Inventory Data: Next, use "Get Data" to connect to the cloud-based database (e.g., via SQL Server connector for Azure SQL), select the inventory tables, and choose "Load" to import them into Power BI’s in-memory model. The import mode documentation notes, "Import mode loads data into Power BI for flexible modeling."

  • Combine in Power BI: Create relationships between the sales (live connection) and inventory (imported) data using common keys (e.g., ProductID) in the "Model" view. Use DAX to write measures combining sales and inventory metrics (e.g., SalesQty - InventoryQty). Build visuals in a single report showing both datasets.

• Why This Works:

  • Live Connection: Analysis Services Tabular models are optimized for performance and often contain complex logic (measures, hierarchies). A live connection preserves this without duplicating data, aligning with best practices for shared models.

  • Import for Inventory: Importing cloud-based inventory data allows flexibility to transform and model it in Power BI, which is necessary since it’s not part of the semantic model.

  • Single Report: Combining both in Power BI Desktop meets the requirement for a unified view, unlike separate reports. The composite models documentation supports this, stating, "Composite models allow combining live connections with imported data."

• Why Not Other Options?:

  • DirectQuery for Both: DirectQuery is supported for Analysis Services and cloud databases, but combining two DirectQuery sources from different systems in one model is complex and often unsupported or slow due to query translation issues. The DirectQuery limitations note, "Mixing multiple DirectQuery sources can lead to performance challenges."

  • Export to Excel: Exporting both datasets to Excel loses the real-time nature of the semantic model, adds manual steps, and limits scalability, contradicting Power BI’s purpose.

  • Separate Reports: Building separate reports and linking via dashboards doesn’t provide a combined view in a single report, failing the requirement. The dashboards documentation indicates dashboards aggregate visuals, not integrate data.