none
Database with relationships defined at the application level

    Frage

  • How would you work with a SQL database in the business intelligence environment where the relationships between tables are defined at the application level rather than at the database level? Is there a relationship model that can be created that sits on top of the database that can be used to create solutions quickly rather than defining relationships between tables over and over again?

    Freitag, 30. August 2013 19:00

Antworten

  • Hello Micheal,

    my approach on this one would be to create a staging relational data warehouse in which you would bring data from that ERP using SSIS.

    The benefits of this approach would be the following:

    1. The data warehouse would be easier to maintain and modify.

    2. You would have the desired relationships between the tables.

    3. You can create SSAS cubes on top of this relational data warehouse where the relationships between the tables will be automatically be recognized by the wizards.


    This way you will only have to modify the SSIS packages and data warehouse structure whenever you will want to bring new data in the warehouse (new fields/tables etc.)

    Please let me know if this answered your question and also if you have any other inquiries.



    Best regards,

    Razvan

    Per aspera ad astra!

    • Als Antwort vorgeschlagen Vikas S. Rajput Dienstag, 3. September 2013 14:52
    • Als Antwort markiert Micheal12341 Dienstag, 3. September 2013 15:01
    Dienstag, 3. September 2013 10:50

Alle Antworten

  • We have an option to create via Visual studio, have a look at this thread.

    http://social.msdn.microsoft.com/Forums/en-US/35807fa2-8473-41bc-9598-2a208ff5f205/database-designer-in-visual-studio-2010-edition



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sonntag, 1. September 2013 14:03
  • Your query is not very clear? 

    Are you meaning to find what if an application controls or defines the relationships among different tables (read data stores)? Kindly elaborate the case you have so it can be addressed precisely. 


    Vikas S. Rajput

    Montag, 2. September 2013 00:33
  • I work with an ERP application that is database independent and does not store the relationships between tables within SQL. It stores them inside the ERP application. Therefore, when I use SQL tools to create BI solutions, SSRS, SSIS, etc. its not as easy as dragging and dropping tables and having them link up automatically using SQL relationships. Each time you create a project, the relationships have to be defined between the tables. This is extremely time consuming. How do you overcome this from a BI standpoint?
    Montag, 2. September 2013 04:02
  • Hello Micheal,

    my approach on this one would be to create a staging relational data warehouse in which you would bring data from that ERP using SSIS.

    The benefits of this approach would be the following:

    1. The data warehouse would be easier to maintain and modify.

    2. You would have the desired relationships between the tables.

    3. You can create SSAS cubes on top of this relational data warehouse where the relationships between the tables will be automatically be recognized by the wizards.


    This way you will only have to modify the SSIS packages and data warehouse structure whenever you will want to bring new data in the warehouse (new fields/tables etc.)

    Please let me know if this answered your question and also if you have any other inquiries.



    Best regards,

    Razvan

    Per aspera ad astra!

    • Als Antwort vorgeschlagen Vikas S. Rajput Dienstag, 3. September 2013 14:52
    • Als Antwort markiert Micheal12341 Dienstag, 3. September 2013 15:01
    Dienstag, 3. September 2013 10:50
  • Very interesting and a good idea! That sounds like a simple solution to implement. Thank you for your feedback!

    Dienstag, 3. September 2013 15:01