Do you need a cube if you have a Data-mart and PoverPivot/Power Query

  • Hi Guys, I have a question to ask. I have been using SSAS and Cubes for about a year now. I admit my MDX is not that good but I have been able to plod along and generate reports that are needed. Anyway what I have been leaning against, is using the Cube for simple reports that the user can create in Excel and for complicated reports I use the Data mart. My question (finally!) is this. I have read that you use the cube because the speed of access and that all the data is in one place. But with Power Pivot/Power Query why do I need the cube. These power Excel extensions store the information in memory, so that is the speed issue dealt with. Also having the data in one place would be the Data-mart. Plus SQL is a lot easier than MDX and most of the team know SQL. So why do I need a cube, when I have a Data-mart. I know people will tell me all the fancy things that you can do with the cube. But it is a lot harder than SQL. If I want to do something in the cube I usually have to change the structure of the Fact table. In SQL I just add a field. I honestly don't see a reason why I need to use the cube. Can anyone give me an answer please.

  • I'm no guru, but here is my 2 cents. Define what you mean in your context of a data mart.

    If you are referring to a relational database using a star or snowflake schema within SQL Server, then you are still running into the same issues SSAS aims to minimize or eliminate. That's because SSAS pre-calculates / pre-aggregates stores of data in a format to make reporting much more efficient than the relational data mart within your SQL Server environment.

    If you are having trouble understanding the end users needs or those needs are always changing where using and managing that cube is more of a pain than what it's worth, then that data mart optimized for reporting (reporting data mart as I call them) as much as you can be may be a worthy trade-off from cube performance to get your users to where they need to be.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply