September 9, 2011 at 10:16 am
Hi Guys,
Sorry about my NOOB question. I have taken over a half developed cube. There are 2 dims, stores and products. I need to create something which shows which products are in which stores. I have a product start and end date for each store in the transaction table. Should i create a type 2 Dim in SSAS for this? There is no front end being used right now so i was wondering how this will be browsed in SSMS browse cube mode?
Thanks
September 9, 2011 at 12:18 pm
BIMind (9/9/2011)
Hi Guys,Sorry about my NOOB question. I have taken over a half developed cube. There are 2 dims, stores and products. I need to create something which shows which products are in which stores. I have a product start and end date for each store in the transaction table. Should i create a type 2 Dim in SSAS for this? There is no front end being used right now so i was wondering how this will be browsed in SSMS browse cube mode?
Thanks
Hi,
A cube isn't the appropriate thing to use if you only want to see which items are in which stores. Cubes need facts/measures (or transactions) from a fact table...something like sales. When the cube is built, it will then (based on your development) associate the facts/measures with all the available dimensions and you would be able to browse the data in that fashion.
What is the structure of your database? Is there any direct association between stores and products? If there is, and you only need to see that without wanting to associate any facts (i.e. sales), then it is probably better to create a Reporting Services report which queries the tables directly.
Hope this helps.
Martin.
September 9, 2011 at 2:56 pm
So of those products in those stores, how would i see my sales broken down by store by product if its not coming from the cube...write group by SQL statements? i am sorry but i believe this is a common question and there must be a simple solution using a cube...
September 10, 2011 at 10:20 am
BIMind (9/9/2011)
So of those products in those stores, how would i see my sales broken down by store by product if its not coming from the cube...write group by SQL statements? i am sorry but i believe this is a common question and there must be a simple solution using a cube...
A cube is ultimately built from database tables (fact and dimension tables). Have a look at your datasource view in the SSAS project...all dimension and fact tables that appear there are referring to some underlying tables in a database.
To answer your question though...yes, it would be possible to write SQL statements to represent sales by product and store. The reason why you would want to build a cube, is to allow the ability to slice and dice data (and change your view of it) easily and on the fly. Apart from 3rd-party tools, the best ways to represent cube data to end users currently is through Excel or Reporting Services. Excel will allow users to change the view themselves, through the use of Pivot tables. With Reporting Services, you will be able to develop more standard reports with limited ability to interact. It is also possible to create a Report Model, which will allow users to create their own reports in Report Builder.
The question of whether you should build a cube or write SQL queries for a specific report, will ultimately depend on the amount of data you have and the business requirements. Cubes can efficiently deal with huge amounts of data, but that doesn't necessarily mean you have to build a cube for everything.
Sorry for the long-winded reply, but I hope I have answered your question and that it makes a little more sense.
Martin.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply