You are building a Power BI report that uses data from an Azure SQL database named erp1.
You import the following tables.
You need to perform the following analyses:
✑ Orders sold over time that include a measure of the total order value
Orders by attributes of products sold
The solution must minimize update times when interacting with visuals in the report.
What should you do first?
To efficiently analyze orders over time and by product attributes while minimizing update times, the data needs to be properly combined to optimize performance and relationships. The most effective approach is to merge the Orders table with the Order Line Items table. By doing this, you create a single comprehensive fact table that includes detailed order information, such as order values and product IDs. This allows for more efficient calculations and visualization interactions. Therefore, merging the Orders query and the Order Line Items query in Power Query is the appropriate first step.
I'm very sure it's D. It's the Header/Detail Schema, and the most optimal way is to flatten the header into the detail table.
D. doesn't have a common field. The answer has to be A
There is no way D doesnt have a common field. There wouldn't be an order line if there was no Order ID in it. so just because it is not stated doesnt mean it dont exist.
I agree that it's not clearly stated in the question that Order and Order Line tables have common field (for example: order ID) If there is no common fields, there is no way to implement the requirements (calculating order value from Order line).
There is no way D doesnt have a common field. There wouldn't be an order line if there was no Order ID in it. so just because it is not stated doesnt mean it dont exist.
I was first going with A but from the explanations I got from his source and re-reading the question between the lines, it is obvious that the answer is D.
Totally agree, in Star Schema, we should only have one FACT table of ONE object (here is order). So, in this example, we should combine Order and Order Detail into one FACT table.
My option here was A. Nothing in the question makes me think I need any High level Info on Orders (i.e. Orders table). In the 'Order Line Item' I have everything I need to calculate 'Total Order Value' (i.e. Quantity * Price) plus the Product ID, which I'd use to get the Product Attributes required. In my opinion and based on the requirements, I don't think we even need/should to load 'Orders' table, as this would not be a requirement for the task in hand, thus we would be loading extra info that in not needed.
Should be A, because we need to get " Orders sold over time that include a measure of the total order value Orders by attributes of products sold" Order line detail for quantities ordered, and product for product's attribute
I think you're forgetting about the "over time" part of the objective. You cannot show a distribution of sales over time without having a date column which does not seem to be present in Products or Order Line Items.
Exactly
The date column is also not specified in the Orders, so this argument doesn't make sense.
What do you mean? High-level info about orders includes date of the order.
Exactly
Price is also an attribute to the product, which is present in Order line detail. The key word here is a product sold. The sold items are present only in the Order line detail. So A is INCORRECT
It is not a good idea, but in this case it is the only way to aggregate the order value per product attributes.
What do you mean? High-level info about orders includes date of the order.
The answer is D based on the question! "What should you do first?" 1st we should merge the Order & Order Lines items. Next, merge the Order line items & Product. If you feel you have clear understanding after reading above solution, Please give a upvote. Thanks, Karthik
This one is fairly controversial and comes down to reading skills really. As other commentators have stated there are three key concepts required; date information, order value (price*quantity), and 'attributes of products sold'. We know the date and order value are present in Order Line Items. The only question is where are we most likely to find 'attributes of products sold'. The obvious answer is already the Products table, but this is further reinforced by the fact that the answer also indicates there is a matching key between Order Details and Product. There is no such explicitly stated matching key between orders and order line items. The only correct answer to this is A. Anybody saying D has not properly absorbed and thought about the information available.
The correct answer is D. To perform the analyses of orders sold over time and orders by attributes of products sold in a Power BI report while minimizing update times when interacting with visuals, we should do, From Power Query, merge the Orders query and the Order Line items query. Because: By merging the Orders query and the Order Line items query in Power Query, we combine the relevant data into a single table. After merging the tables in Power Query, you can establish relationships between the tables, ensuring that they are related properly in the data model. Once tables are merged and relationships are set up, we can then create measures in DAX to calculate the total order value and analyze orders by attributes of products sold. This calculation will perform more efficient because we have reduced the data complexity through merging and proper data modeling.
Hi, can you explain again why we have to merge two tables? I'm not sure for star scheme, should it be the case as long as the relationship between two tables has been established, and that would be fine?
he cannot, it's a chatGpt copy/paste answer
he cannot, it's a chatGpt copy/paste answer
Orders with line details as Fact table, Products will be a Dimension
Answer given is correct. If you merged the product table you would have a huge amount of recurring product values for each order (this goes against the idea of normalisation)
Hmm im not sure now. It could be either. Due to it not clearly showing which table has the date column or information.
by attributes of products sold
It's D, product table is a dimension table which must not contain any fact records
I think the answer is A For these two requirements: - Orders sold over time that include a measure of the total order value - Orders by attributes of products sold are only the Products and the Order Line Items tables needed. As the Order Line Items table has the column ProductID, these tables can be merged (joined) together.
D is the right answer i think because it is clearly mentioned orders sold over time so orders quantity and orders price is mentioned in order line but date will be in order table so we need to merge order and order line to get the result.
Since the question is : What would you do first? so it makes sense to first join Order details with Order table. Then use that merged table as a join/lookup table for products. So the first thing we did is Option D. Thats why it is correct!
The answer is D. We need to merge the "orders" table and the "order line details" table as the first step to simplify the data and make it easier to analyze. After which we can use the products table to perform the analysis (total order value orders by attributes of product sold).
This is a badly worded question. Answer could be either A or D, really depends on what common columns are in each table.
I chose A previously, but I think D is a better choice. Product table is a dim table, which means it won't update as frequently as fact table. So merge two fact tables would be a good idea to minimize update times.
Here me out, in able to fulfill both requirements, A and D are correct. D fulfill the first requirement, and A fulfill the second requirement. But the question ask "what should you do FIRST". That was why the answer should be D. Because it's the first thing the requirement ask us to do.
D. From Power Query, merge the Orders query and the Order Line Items query. By merging these queries, you create a single table that contains all the necessary information about orders and their line items. This simplifies the data model and reduces the overhead of handling multiple tables and relationships, leading to more efficient interactions with visuals in your report.
D. Since Order and Order Lines tables are parts of the fact table, so combine them first and make the combination the centre of the star schema, then join the DIM table Product.
Order Line Items contain the Product ID. thats the only link Correct Answer A
"Orders by attributes of products sold"
It doesn't ask for product information in the result and the objective is to minimize the read, (less tables), price is in order details and time is in order, D is correct
It DOES ask for product information in the result. Please read this requirement: "Orders by attributes of products sold"
D sounds about right, assuming the tables have a common column Order ID. Order Line items doesn't seem to have the time details. If it does, it would be self sufficient to fulfil the requirement..
D seems correct. Order and Order line details provide detailed order information and should be merged as new. The new merged table can then be connected to the product query
why we are not writing dax for it??
Must minimize refresh time for the visuals, DAX slows it down
It is A, because D, does not make sense to merge ( Dimensional Table : Orders, with Fact Table: Order Line )
After reading PinkZebra's answer, it is D
A - because Total order value is a measure (which should be clculated from orderline), so why merge order? :)
This is exactly my thoughts. From the question and request, I don't see the point of merging with Orders table. All request will be answered by merging Products and Order Line Items. No need to merge with Orders
Answer is D , if we merged product with order detail we will increase the size by repeating the product data on each row in the order detail which is not good at all
Orders table has high-level order info, while OrderLines has more granular order info. The only way to link OrderLines (which also contains ProductID and price of each order) to Orders would be via an FK reference i.e. OrderID.
How you get product attribute from D option? is there right information you have in 2nd table regarding product?
D - Consolidate the order and order line items query first - you will need information from both tables
Hello everyone, to make it clear we must to know: --- PBI will do the best aggregation base on Star Schema model, we now have 1 Fact table (Order Line Items) and 2 Dim tables (Products, Orders). Orders has common field with Products (ProductID), and pretty sure time series field (OrderDate); Orders Line Items has Price and Quanity. --- We need summarize some values like "price" and "quantity" over-time by attributes product. But we only have common field in Dim table (Orders) so we need to merge Dim (Orders) and Fact (Order Line Items) to new single Fact table to design the right Star Schema model. => So that D is correct
D is right, Merging the Orders query with the Order Line Items query in Power Query creates a more efficient data model. This reduces the number of relationships and joins that need to be processed during report interactions, leading to faster update times. With the merged data, calculations such as total order value and orders by product attributes become simpler and more efficient. This reduces the need for complex DAX calculations that can slow down report performance
Why not D: Merging these two tables would help calculate the total order value but does not associate product attributes (e.g., categories) with orders. Without merging Order Line Items and Products, you cannot analyze orders by product attributes effectively.
Answer is D. Tried and Tested.
A. All fields are covered with OrderLine and product query.
It is definitely A
Answer is D: Order Line is the dim table between Product and Order.
D - we start from the first column
Should be D
Seems D is Correct, as Product Query have only Product information.
Its "D" , make sense, and already explained
it seems D is correct, the given explanation is also point on "D"
D is what we do first, and we can do A in the following step.
It seems, only the “Order” Table has Date Information, while only “Products” Table has “attributes of products sold”. Quantity of Products sold is needed from “Order Line Items”. Personally, I would merge Orders and Order Line to only retrieve the Dates, disable loading “Orders” and create a relationship between “Order Line Items” and “Products”. (Reduces data volume, as writing the entire Product Catalogue Data in every order line will create huge amounts of duplicate data). So Answer D.
I understand that denormalizing this model a little and merging Orders and Order Line Items. This fact table relates to the Product dimension ( Star Schema.) D. In Power Query, merge the Orders query and the Order Line query
D seems to make more sense to me.
As a first step this looks correct
Answer should be D
D should be the right answer because from the order line items you have the product ID where all other information concerning the product can be obtained
It’s the only option that fits the given options because you need the attributes of the product table and the quality of the order lines. The information about total orders and orders over time should be accessible in the Order Table. This analysis does not need any Product information. So first we have to merge the tables before e we can start building our visuals.
The Requirements are: Orders sold over time that include a measure of the total order value(Details are in Orders AND Order Line) Orders by attributes of products sold(Details are in Orders) Products could only contain Product Details only which is not needed on the requirements.
SEMI-SQL: Select Sum(ol.TotalAmount) From OrderLine ol Inner Join Product p On p.Id = ol.Id Order By p.Attribute1, p.Attribute2...
Answer is A: Orders by attributes of products sold Orders = measure of order price from orders line query (total number of orders) Attribute= product sold (name of products) will come from product query
A: Because we need to merge Product Attributes (ProductName) with Price and date, order details such as date and price are in the OrderIDDetails and ProductNames in the Product table
Ordernes y Order Line Items
I agree with D, Product query should stand alone because It's the product catalog.
I think Order line items has the diff products associated with the particular order. So we can order ID since we have that info in orderline Items. Extra piece of info that we need is products attribute that will come from products table
GPT: Merging the Orders query and the Order Line Items query in Power Query will allow you to create a single query that combines the necessary data from the different tables. This will make it easier and more efficient to perform the required analyses, as you will have all the information you need in one place.
I think D is correct option bcoz, performing product or multiplication at power query level will optimize the solutions.
I agree with D , it's header and detail tables that can be merge
The question is about the order "Sold". That information is on the Order, It can't be on the product. So we need to merge Order and Order line tables.
How the hell are you supposed to answer this question without knowing what exactly is on the tables? The table descriptions for the Products and Orders tables are very vague. How do you know which one contains the product attributes? Am I missing something here?
I'm seeing it as a star schema question. You've got two fact tables and one dimension table. You merge the fact tables so that you only have one fact table and a proper star schema.
I think it should be A. In first scenario we need information like order date to show orders over time, the column is supposed to be in the order table. We can have get it even though it is not connected to Orderline or product table. In the next scenario we need to show orders by product attributes, for which we need to connect orderline table with products table.
@Moderator , pls don't approve this. Adding a new one.
Should be A, because we need to get " Orders sold over time that include a measure of the total order value Orders by attributes of products sold"
D is the correct answer!
I believe the answer should be D. The question asks about the amount of the order over time. The first table only contains the Product Catalog. it doesn't have anything to do with the requirements in the question.
The main advantage of D over A is that Merging Orders with Order Line Items creates a fact table in a star schema, instead of having two fact table.
Might be option A. How about the orders by attribute without accessing the Product datasource ?
The Order Line Items table contains only the following columns: Product ID Quantity Price Details It does not include a date column or other high-level order information. The Orders table, on the other hand, likely contains essential information such as: Order Date (for time-based analysis) Order ID (to link with Order Line Items) Customer Details (if needed) so Answer is D
I choose A. The Products table contains product attributes (e.g., category, brand), but it cannot directly connect to the Orders table since there’s no shared key. The Order Line Items table acts as a bridge between the Products table (via Product ID) and the Orders table (via Order ID). By merging Products with Order Line Items, the resulting table includes both product attributes and order-level details, enabling comprehensive analysis. For analysis 1 (orders sold over time with total order value): Use the merged table to calculate total order value (Quantity × Price) for each order and aggregate by the order date from the Orders table. For analysis 2 (orders by product attributes): Filter and group the merged table by product attributes (e.g., category or brand) to analyse sales performance for specific product groups.
The order table's description says "contain high level information about order". Nothing else. And most importantly, the requirement is 1) order quantity, 2) order value and 3) order by product attributes - nothing else valuable can be acquired from the order table? Therefore if the requirement is to minimize update time of visual, don't bother connecting order and order line item table. Answer should be A
A because Orders by attributes of products sold meant that you have to join fact with prod. If choose D you still have to merge with prod. It not minimize update times when interacting with visuals