June 29, 2017 at 2:27 am
Hi All,
I need to know whether unused columns in tables/views(excluding named queries) of DSV impacts cube/dimensions processing. for e.g. If I have a table Product with columns(Id,Name,Rate,Color) and created a dimension DimProduct and used only Id,Color for attributes Id,Color respectively leaving Name and Rate unused.
Similarly I have a fact table with 5 dimkey columns, 5 measure columns and creating a measure group consisting of 2 measure columns leaving 3 measure columns.
I want to understand does leaving such unused columns in tables/views of DSV impacts cube/dimension processing?
Regards,
BigB
June 29, 2017 at 2:46 am
BigB - Thursday, June 29, 2017 2:27 AMHi All,I need to know whether unused columns in tables/views(excluding named queries) of DSV impacts cube/dimensions processing. for e.g. If I have a table Product with columns(Id,Name,Rate,Color) and created a dimension DimProduct and used only Id,Color for attributes Id,Color respectively leaving Name and Rate unused.
Similarly I have a fact table with 5 dimkey columns, 5 measure columns and creating a measure group consisting of 2 measure columns leaving 3 measure columns.
I want to understand does leaving such unused columns in tables/views of DSV impacts cube/dimension processing?Regards,
BigB
The short answer to your question is, broadly, no. The cube will only process the columns from the tables that it needs to create attributes (for dimensions) and measures and keys (for measure groups).
However, because measure group partitions can accept custom SQL there's no guarantee that a cube will be interrogating columns during processing that it does not technically need. But by default it's generally only what it needs.
There are other design gotchas to watch out for - like having a referenced relationship between a measure group and a dimension. This will cause a join to happen in the SQL that is issued to the database when building the measure partitions and a lack of indexing can slow things down.
Run a trace on your SQL instance and run a full process of your cube and watch the statements that it issues and you'll see what I mean.
June 29, 2017 at 3:02 am
PB_BI - Thursday, June 29, 2017 2:46 AMBigB - Thursday, June 29, 2017 2:27 AMHi All,I need to know whether unused columns in tables/views(excluding named queries) of DSV impacts cube/dimensions processing. for e.g. If I have a table Product with columns(Id,Name,Rate,Color) and created a dimension DimProduct and used only Id,Color for attributes Id,Color respectively leaving Name and Rate unused.
Similarly I have a fact table with 5 dimkey columns, 5 measure columns and creating a measure group consisting of 2 measure columns leaving 3 measure columns.
I want to understand does leaving such unused columns in tables/views of DSV impacts cube/dimension processing?Regards,
BigBThe short answer to your question is, broadly, no. The cube will only process the columns from the tables that it needs to create attributes (for dimensions) and measures and keys (for measure groups).
However, because measure group partitions can accept custom SQL there's no guarantee that a cube will be interrogating columns during processing that it does not technically need. But by default it's generally only what it needs.
There are other design gotchas to watch out for - like having a referenced relationship between a measure group and a dimension. This will cause a join to happen in the SQL that is issued to the database when building the measure partitions and a lack of indexing can slow things down.
Run a trace on your SQL instance and run a full process of your cube and watch the statements that it issues and you'll see what I mean.
Thanks for your reply. Yes if believe if SQL profiler trace is used, it will only show SQL containing columns used for attributes/measure groups and columns used in joins if any.
Obviously, foreign key columns in fact will always be used for joining to respective dimension, and as far as custom SQL's in partitions are concerned then whatever SQL is written will get fired irrespective of which columns are being used for building measure groups.
I was mainly looking in terms of dimension only and your reply will be marked as answer.
Thanks much!
BigB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply