Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Data Warehouse Design - entered planning values in cube1 shall influence cube 2

Answered Data Warehouse Design - entered planning values in cube1 shall influence cube 2

  • Thursday, January 31, 2013 11:54 AM
     
     
    Sign In to Vote
    0

    Hello,

    I build up a Data Warehouse for Sales and Operations Planning. How do I have to design the Data Warehouse to enable the data flow among single Cubes?
    What I want to say: Once a new dataset is entered into the salescube, the inventorycube shall be updated by this information and the productioncube shall calculate the needed amount to satisfy the planned inventoryamount.

    [
      Example:

           salescube:
           planned salesamount           product1 Feb 1st: 100 pcs

           inventorycube:
           minimum reserve inventory  product1 Feb 1st: 200 pcs
           planned inventoryamount    product1 Feb 1st: 200 pcs

           productioncube:
           ending inventoryamount      product1 Jan 31st: 50 pcs
           planned productionamount  product1 Feb 1st: 150 pcs
                                                                                                   ]

    How do I have to design the Data Warehouse to solve this issue? Do I have to create three seperate cubes incl. datasets for the OLAP-analysis?
    How do I bring the entered dataset from the salescube to the other cubes?

    In a first attempt I used Trigger to create new datasets in the cubes once a new dataset is entered in the salescube - this was too slowly.
    Is there a possibility to solve this issue with formulas and still beeing able to run OLAP?

    Used Software: SQL-Server 2012 (Data Warehouse), PowerPivot (OLAP), Dynamics Nav (Source System)

    Thank you very much for your help!

    Regards
    Jörg 

     

All Replies

  • Thursday, January 31, 2013 3:22 PM
     
     Answered
    Sign In to Vote
    0

    Hi,

    you can add a (sub)cube to an existing cube. Multiple fact tables are supported within a cube.
    You can segregate the various subcubes within the cube by perspectives or measure groups (& related dimensions).

    u can achieve this with linked objects in SSAS

    http://www.databasejournal.com/features/mssql/article.php/3694991/Introduction-to-Linked-Objects-in-Analysis-Services-2005.htm

    http://www.ssas-info.com/VidasMatelisBlog/40_splitting-analysis-services-2005-cubes-based-on-measure-groups

    Or you want to reuse some object within another cube? If so, you can take a look at linked object(linked dimension/measure group):

    http://msdn.microsoft.com/en-us/library/ms175648(SQL.105).aspx

    There are some limitations with linked object, before making a decision, you may need to check:

    http://www.packtpub.com/article/measures-and-measure-groups-microsoft-analysis-services-part2?utm_source=sl_expertcube_abr1_0709&utm_medium=content&utm_campaign=sanjiv

    Best Regards,
    Silna
    Please feel free to ask if you have any doubts and remember to mark the correct replies as answers.

    • Marked As Answer by J.Roca Friday, February 01, 2013 6:30 PM
    •  
     
  • Friday, February 01, 2013 6:30 PM
     
     
    Sign In to Vote
    0

    Hi Silna,

    thank you very much for your answer! I think I found a way to solve this issue. I just add further columns to the fact table and import the sales vlaues into the sales column, the inventory values into the inventory column...

    I will build the cubes in PowerPivot. So I can add "calculated columns" to the table and use DAX to bring in my formulas. Is this what a linked object does and can be called a (sub)cube? Or is it just a simple solution for my problem.

    Best Regards,

    Jörg

     
  • Sunday, February 03, 2013 5:16 AM
     
     
    Sign In to Vote
    0

    Hi,

    No Linked objects are different ..

    To create a linked dimension in Microsoft SQL Server 2005 Analysis Services (SSAS), right-click the Dimensions folder in an Analysis Services database or project, and then click New Linked Dimension. The Linked Object Wizard guides you ...On the Select Objects page of the wizard, choose the dimensions you want to link to in the remote database. You cannot link to linked dimensions in the remote database. Linked objects in SSAS should be used very carefully, as the object resides in the original cube and just a link is created in the target cube. The risk is that when multiple teams are working on different projects, people can take a shortcut for faster development and create links out of other cubes. Linked objects has a limitation that its definition cannot be modified in the target cube design, and this can be a big bottleneck if this is not the desired design. In an environment where multiple cubes are developed in the same solution, allowing free use of linked objects opens up a risk of creating a spider web of linked objects across cubes.

    what you are doing from powerpivot is just accessing the ssas cube and defining formulas

    HTH

    Best Regards,
    Silna
    Please feel free to ask if you have any doubts and remember to mark the correct replies as answers.

     
  • Sunday, February 03, 2013 6:18 PM
     
     
    Sign In to Vote
    0

    Hi Silna,

    thank you very much for your explanation. I will try it out in the next multidimensional project with SSAS. This time I stick to tabular and PowerPivot.

    Best Regards

    Jörg