Topic
  • 3 replies
  • Latest Post - ‏2013-02-11T22:49:15Z by dtc
mgibson
mgibson
20 Posts

Pinned topic Formatting a Reverse Engineered Data Warehouse Star Schema

‏2013-02-10T23:48:06Z |
Hi,

I've just reverse engineered a Star Schema from our Data Warehouse and was wondering whether any of the Layout options properly catered for this? I've checked the pre-sets, and none seem to.

Specifically, is it possible to define the central Fact Table in a Physical Model and have DA automatically layout the dimension tables around it in a star?

And of so, does this extend to Snowflaked dimensions?

Also, some of our dimensions are used in multiple roles, so hopefully the diagram can create aliases - but that might be hoping for too much.

Any help will be appreciated - we really don't want to manually layout a star design.

Regards
Mick
Updated on 2013-02-11T22:49:15Z at 2013-02-11T22:49:15Z by dtc
  • dtc
    dtc
    63 Posts

    Re: Formatting a Reverse Engineered Data Warehouse Star Schema

    ‏2013-02-11T16:00:11Z  
    Please do the following:
    (1) In Data Project Explorer select the model, right click, and select "Use Dimensional Notation".
    (2) Open the model, right click, and select "Discover Facts and Dimensions".
    (3) Expand the model. You should see tables annotated with dimensional notation such as Fact, Dimension, etc.
    (4) Select Diagrams, right click, and select "New Dimensional Overview Diagram" to create a dimensional overview diagram.

    The dimensional notation and diagram work for star schema, snowflake schema and starflake schema. Please see the online help for more information.
  • mgibson
    mgibson
    20 Posts

    Re: Formatting a Reverse Engineered Data Warehouse Star Schema

    ‏2013-02-11T22:04:25Z  
    • dtc
    • ‏2013-02-11T16:00:11Z
    Please do the following:
    (1) In Data Project Explorer select the model, right click, and select "Use Dimensional Notation".
    (2) Open the model, right click, and select "Discover Facts and Dimensions".
    (3) Expand the model. You should see tables annotated with dimensional notation such as Fact, Dimension, etc.
    (4) Select Diagrams, right click, and select "New Dimensional Overview Diagram" to create a dimensional overview diagram.

    The dimensional notation and diagram work for star schema, snowflake schema and starflake schema. Please see the online help for more information.
    Hi DTC,

    Thanks for the assistance. It didn't seem to recognise all of the dimensions using the automated process - it identified the Fact table just fine, but the final diagram only included 4 dimension tables, where the model actually has many more.

    initially it thought there were more than one fact table and related the other dimensions to those, so I manually changed these designations.

    Once I updated the dimensional notation, including which were 'outrigger' snowflaked tables, it did a much better job.

    What a great feature of the product.

    Thanks for your help.

    Mick
  • dtc
    dtc
    63 Posts

    Re: Formatting a Reverse Engineered Data Warehouse Star Schema

    ‏2013-02-11T22:49:15Z  
    • mgibson
    • ‏2013-02-11T22:04:25Z
    Hi DTC,

    Thanks for the assistance. It didn't seem to recognise all of the dimensions using the automated process - it identified the Fact table just fine, but the final diagram only included 4 dimension tables, where the model actually has many more.

    initially it thought there were more than one fact table and related the other dimensions to those, so I manually changed these designations.

    Once I updated the dimensional notation, including which were 'outrigger' snowflaked tables, it did a much better job.

    What a great feature of the product.

    Thanks for your help.

    Mick
    The automatic discovery is based on heuristics and can make wrong designations, especially in the beginning without any guidance. I am glad you found out that you could correct the designations and rerun the automatic discovery and that it did better subsequently, taking into consideration your corrections.