Hi Teo I met you at Tech-Ed after your great Reporting Services Class. We are weighing the pros and cons of building one huge "Operations" cube as opposed to building a handful of cubes and then creating a linked cube to bring them all together. For example, our existing Forecast cube uses the InventoryFact table for a measure group. A proposed Manufacturing cube will use the same InventoryFact table, but the manufacturing data is not necessarily directly related to the Forecast cube data elements. However, we can see the potential of having both data elements on a report some day. We are trying to answer the question, is it better to have one huge cube containing all the measure groups and dimensions or a few smaller cubes connected together with a linked cube. Can someone please give us their opinion or share their experiences? FYI - We are using SQL 2008 and have not built a linked cube before. We do however have other cubes in production in separate databases not necessarily related to this project. Thanks Chris
Hi Teo
I met you at Tech-Ed after your great Reporting Services Class.
We are weighing the pros and cons of building one huge "Operations" cube as opposed to building a handful of cubes and then creating a linked cube to bring them all together. For example, our existing Forecast cube uses the InventoryFact table for a measure group. A proposed Manufacturing cube will use the same InventoryFact table, but the manufacturing data is not necessarily directly related to the Forecast cube data elements. However, we can see the potential of having both data elements on a report some day.
We are trying to answer the question, is it better to have one huge cube containing all the measure groups and dimensions or a few smaller cubes connected together with a linked cube. Can someone please give us their opinion or share their experiences?
FYI - We are using SQL 2008 and have not built a linked cube before. We do however have other cubes in production in separate databases not necessarily related to this project.
Thanks
Chris
Hi Chris,
Thanks for attending my TechEd session. Well, how" huge" will be the cube if you have everything in it? Gigabytes, terabytes? Will you have complex business calculations? The SQL Server 2005/2008 performance guide advocates splitting a very large UDM into smaller cubes although it doesn't provide size guidelines when the split should be considered.
If you ask me, I'd strongly suggest you go for the single cube approach and consider splitting when performance is inadequate but after trying every trick in the book (figuratively speaking, not my book necessarily ) to optimize it. A single cube will let you do cross-measure group reporting. Linked objects is a good concept but IMO has left a lot to be desired from an implementation standpoint. I've personally never used linked objects in a real-life project.
Thanks so much for answering and I want to give you a status. The word "huge" is relative. The size the cube is in gigabytes and (after we worked out the vacations and meeting schedules) we decided to take your advice and build a single, large cube. I finished the SSIS ETL and am about to start building the new cube. We'll still end up with about 10 cubes total for the organization, but at least this one will be more robust than the previous legacy cubes.
I hope this approach works for you.