July 23, 2016 at 8:22 am
I want to split the tabular model column for a Fact Table to optimize the performance . The Fact Table Order ( OrderID column) has more than 15000000 records which has a relationship with the other Product Price Dimension Table.
I have referred one of the blog for the same https://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/
However I want to know how I can change my measure and relationship based on this split column.
For example my measure are
Sales = Sumx('Order',[price]*[Quantity])
July 23, 2016 at 1:13 pm
abhishek_300 (7/23/2016)
I want to split the tabular model column for a Fact Table to optimize the performance . The Fact Table Order ( OrderID column) has more than 15000000 records which has a relationship with the other Product Price Dimension Table.I have referred one of the blog for the same https://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/
However I want to know how I can change my measure and relationship based on this split column.
For example my measure are
Sales = Sumx('Order',[price]*[Quantity])
Quick question, are you having performance problems due to the cardinality of the table? 15M rows doesn't sound like much unless the server is underpowered.
😎
My guess is that if you are experiencing performance problems that would be more related to the width of the tables, inefficient measures etc.
July 24, 2016 at 10:58 pm
Yes I am trying to fix performance issues. The fact column which has 15000000 records are all unique that's why I want to split this column as per the above link to reduce the cardinality
July 24, 2016 at 11:20 pm
abhishek_300 (7/24/2016)
Yes I am trying to fix performance issues. The fact column which has 15000000 records are all unique that's why I want to split this column as per the above link to reduce the cardinality
How wide is your Orders table?
😎
July 25, 2016 at 1:11 am
Further questions to the OP
😎
1) Do you really need the OrderID in the table? If the model has something like [Customer]---<[Order]>---[Produc] construct the OrderID is redundant as the Orders Fact should take the dimension keys of the model's dimensions ie. Customer, Product, Date, Demographics etc. and no table should hold a foreign key relation to the Orders table.
2) Is your transaction model a single product per order with the quantity and the price of the product?
3) How do you handle price variations, discounts etc.?
July 26, 2016 at 2:35 am
Thanks for your reply. Sorry for not providing the complete information. So this is how the table relationship is .
Product master---= product price -->order.
The order and the product price are linked with the order I'd and the product price and product master are linked by the product master key. The sql profiler shows performance issue when I try to run the report by product number,product price ,sum of sales. When I add the product price my performance get hit if the data is huge for the order table
The order table holds daily transaction.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply