Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Data Cube / SQL Server 2012 / BI

Author  Topic 

Ross
Starting Member

3 Posts

Posted - 2013-06-11 : 07:09:12
Hi Folks,

I am a newbie to this forum and relatively new to SQL especially SQL Server.

My first query in this area:

I have got 2 tables with one common field on which i can make a join.

A/ I have been asked to convert this data into a cube.

All i know is that a cube is more then 2 dimensional data. Would love your input on where i can learn about cubes and how do i achieve this in sql server 2012 as i already have a license for it.


B/ The objective of this exercise i believe, is to keep ready as many reports in as many permutation combinations as possible so that when a user asks for this data, its readily available and very fast to execute.

Wouldn't a view just work for this or is that too simple and time consuming to modify every time there is a different request?

C/ We also would like to use the BI part of SQL Server 2012. End result is for users to use Excel to interact and play with the data/reports.

Can users directly through excel leverage the BI tools of SQL Server 2012

Thanks in advance..

Regds,
Ross

Thanks

Regds,
Ross

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 07:35:46
A.Cube would help you to organise and aggregate the data into multiple levels and prestore them into a n dimesnional format which makes dynamic filtering and aggregation on multiple levels easire.
You can use sql server analysis services to create a cube.

B. Yep. It will help you to create a multi dimensional model which somebody can consume in end user presentation tool like Excel, SSRS etc to dynamically aggregate and display data in variety of formats. Writing a view again reduces this to 2 dimensions and aggregation dynamically to variety of grain levels can become cumbersome in it and making it time consuming.

C. Users can consume cubes directly in Execl or they can even use Powerpivot plugin available in Excel to create a model and create reports out of it giving them a self service BI capability. In case they need to distribute it across, they would require sharepoint for deploying the created models



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ross
Starting Member

3 Posts

Posted - 2013-06-11 : 08:06:26
Thanks Visakhm,
Could you be kind enough to direct me to any sites which would explain about cubes; a place where i could learn how to cube.

Or will the sql server analysis services do this job of organizing the data into every sort of combination?

Thanks

quote:
Originally posted by visakh16

A.Cube would help you to organise and aggregate the data into multiple levels and prestore them into a n dimesnional format which makes dynamic filtering and aggregation on multiple levels easire.
You can use sql server analysis services to create a cube.

B. Yep. It will help you to create a multi dimensional model which somebody can consume in end user presentation tool like Excel, SSRS etc to dynamically aggregate and display data in variety of formats. Writing a view again reduces this to 2 dimensions and aggregation dynamically to variety of grain levels can become cumbersome in it and making it time consuming.

C. Users can consume cubes directly in Execl or they can even use Powerpivot plugin available in Excel to create a model and create reports out of it giving them a self service BI capability. In case they need to distribute it across, they would require sharepoint for deploying the created models



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks

Regds,
Ross
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 08:16:27
quote:
Originally posted by Ross

Thanks Visakhm,
Could you be kind enough to direct me to any sites which would explain about cubes; a place where i could learn how to cube.

Or will the sql server analysis services do this job of organizing the data into every sort of combination?

Thanks

quote:
Originally posted by visakh16

A.Cube would help you to organise and aggregate the data into multiple levels and prestore them into a n dimesnional format which makes dynamic filtering and aggregation on multiple levels easire.
You can use sql server analysis services to create a cube.

B. Yep. It will help you to create a multi dimensional model which somebody can consume in end user presentation tool like Excel, SSRS etc to dynamically aggregate and display data in variety of formats. Writing a view again reduces this to 2 dimensions and aggregation dynamically to variety of grain levels can become cumbersome in it and making it time consuming.

C. Users can consume cubes directly in Execl or they can even use Powerpivot plugin available in Excel to create a model and create reports out of it giving them a self service BI capability. In case they need to distribute it across, they would require sharepoint for deploying the created models



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks

Regds,
Ross


SQL Server analysis service will do the organizing part, but you need to create a project to define various entities (facts, dimensions) involved, their relationships etc inside it for that.
The starting point its to understand dimensional model which is basis for designing and creating a cube using Analysis Services

http://www.mssqltips.com/sqlservertip/1532/build-a-cube-from-an-existing-data-source-using-sql-server-analysis-services/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ross
Starting Member

3 Posts

Posted - 2013-06-11 : 08:41:35
Many thanks Visakhm

quote:
Originally posted by visakh16

quote:
Originally posted by Ross

Thanks Visakhm,
Could you be kind enough to direct me to any sites which would explain about cubes; a place where i could learn how to cube.

Or will the sql server analysis services do this job of organizing the data into every sort of combination?

Thanks

quote:
Originally posted by visakh16

A.Cube would help you to organise and aggregate the data into multiple levels and prestore them into a n dimesnional format which makes dynamic filtering and aggregation on multiple levels easire.
You can use sql server analysis services to create a cube.

B. Yep. It will help you to create a multi dimensional model which somebody can consume in end user presentation tool like Excel, SSRS etc to dynamically aggregate and display data in variety of formats. Writing a view again reduces this to 2 dimensions and aggregation dynamically to variety of grain levels can become cumbersome in it and making it time consuming.

C. Users can consume cubes directly in Execl or they can even use Powerpivot plugin available in Excel to create a model and create reports out of it giving them a self service BI capability. In case they need to distribute it across, they would require sharepoint for deploying the created models



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks

Regds,
Ross


SQL Server analysis service will do the organizing part, but you need to create a project to define various entities (facts, dimensions) involved, their relationships etc inside it for that.
The starting point its to understand dimensional model which is basis for designing and creating a cube using Analysis Services

http://www.mssqltips.com/sqlservertip/1532/build-a-cube-from-an-existing-data-source-using-sql-server-analysis-services/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks

Regds,
Ross
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 12:49:03
You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -