https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-600/page_22_img_1.jpg

Data Engineers: Read All SQL analytics endpoint data

Data Analysts: Read All Apache Spark

Data Scientists: Read All SQL analytics endpoint data

The permissions for the data store in the AnalyticsPOC workspace should align with the principle of

least privilege:

Data Engineers need read and write access but not to datasets or reports.

Data Analysts require read access specifically to the dimensional model objects and the ability to

create Power BI reports.

Data Scientists need read access via Spark notebooks. These settings ensure each role has the

necessary permissions to fulfill their responsibilities without exceeding their required access level.

https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-600/page_43_img_2.jpg

The relationship between OrderItem and Product must be based on: Both the CompanyID and the

ProductID columns

The Company entity must be: Denormalized into the Customer and Product entities

In a dimensional model, the relationships are typically based on foreign key constraints between the

fact table (OrderItem) and dimension tables (Product, Customer, Date). Since CompanyID is present

in both the OrderItem and Product tables, it acts as a foreign key in the relationship. Similarly,

ProductID is a foreign key that relates these two tables. To enable analysis by date, product, and

customer, the Company entity would need to be denormalized into the Customer and Product

entities to ensure that the relevant company information is available within those dimensions for

querying and reporting purposes.

Reference =

Dimensional modeling

Star schema design

A replica of dbo.Sales is created in the test schema by copying the metadata only. - Yes

Additional schema changes to dbo.FactSales will also apply to test.FactSales. - No

Additional data changes to dbo.FactSales will also apply to test.FactSales. - No

The CREATE TABLE AS CLONE statement creates a copy of an existing table, including its data and any

associated data structures, like indexes. Therefore, the statement does not merely copy metadata; it

also copies the dat

a. However, subsequent schema changes to the original table do not automatically propagate to the

cloned table. Any data changes in the original table after the clone operation will not be reflected in

the clone unless explicitly updated.

Reference =

CREATE TABLE AS SELECT (CTAS) in SQL Data Warehouse

Both a dataflow (A) and a Stored procedure activity in a pipeline (B) are capable of creating and

populating a date dimension table. A dataflow can perform the transformation needed to create the

date dimension, and it aligns with the preference for using low-code tools for data ingestion when

possible. A Stored procedure could be written to generate the necessary date dimension data and

executed within a pipeline, which also adheres to the technical requirements for the PoC.

The df.explain() method does not meet the goal of evaluating data to calculate statistical functions. It

is used to display the physical plan that Spark will execute. Reference = The correct usage of the

explain() function can be found in the PySpark documentation.

A lakehouse (C) should be recommended for the AnalyticsPOC workspace. It combines the

capabilities of a data warehouse with the flexibility of a data lake. A lakehouse supports semi-

structured and unstructured data and allows for T-SQL and Python read access, fulfilling the technical

requirements outlined for Litware. Reference = For further understanding, Microsoft's

documentation on the lakehouse architecture provides insights into how it supports various data

types and analytical operations.

The correct order for the DAX expression would be:

DEFINE VAR _SalesSince = DATE ( 2023, 12, 01 )

EVALUATE

FILTER (

SUMMARIZE ( Store, Store[Name], Store[OpenDate] ),

Store[OpenDate] >= _SalesSince )

In this DAX query, you're defining a variable _SalesSince to hold the date from which you want to

filter the stores. EVALUATE starts the definition of the query. The FILTER function is used to return a

table that filters another table or expression. SUMMARIZE creates a summary table for the stores,

including the Store[Name] and Store[OpenDate] columns, and the filter expression Store[OpenDate]

>= _SalesSince ensures only stores opened on or after December 1, 2023, are included in the results.

Reference =

DAX FILTER Function

DAX SUMMARIZE Function

You should set Repeat for the schedule to Weekly (C). This allows you to specify the pipeline to run

on specific days of the week, in this case, every four hours on Mondays and Fridays. Reference =

Scheduling options for data pipelines are available in the Azure Data Factory documentation, which

includes details on configuring recurring triggers.

For the Copy data activity, from the Destination tab, setting Table action to Overwrite (B) will ensure

that Table1 is replaced with the schema and rows of the external data, meeting the requirements of

replacing both the schema and data of the destination table. Reference = Information about Copy

data activity and table actions in Azure Data Factory, which can be applied to data pipelines in Fabric,

is available in the Azure Data Factory documentation.