Require help on starting for OLAP

Question

Post reply

Require help on starting for OLAP

sqlinterset

Ten Centuries

Points: 1341
More actions
August 31, 2015 at 1:26 pm

#311265

Currently i am using sql queries to show data in excel pivot tables. however, data is very large around in millions and take so much time in refreshing the reports.
I am planning to build star schema and OLAP to fetch data in excel pivot tables. Will it be good start? my excel contains many pivot tables and pivot charts. Is there any good article to start it?
i am using sql server 2012.

Viewing 8 posts - 1 through 7 (of 7 total)

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

seeingfuluu SSC Rookie Points: 27 More actions · Answer 1

if your pivot table show less data,it may be useful

but i suggest you should do these things:

first of all, aggregate data in cube, as far as possible

second, the workload is hard if you refresh all the tables(pivot table) at one time ,many users would be worse.

so your ssas server should be strong

sqlinterset Ten Centuries Points: 1341 More actions · Answer 2

I have few questions based on wht you mentioned:

1) Is SSAS server same as SQL Server

2) My pivot tables has around millions of data.

3) Currently many pivot tables and charts in one excel sheet.

4) Will it make really any impact converting it into OLAP cubes. Currently data is fetched from SQL server through queries.

seeingfuluu SSC Rookie Points: 27 More actions · Answer 3

seeingfuluu

SSC Rookie

Points: 27

September 6, 2015 at 12:45 am

#1824846

just try

i think it will improve 80% at least

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

sqlinterset (9/2/2015)
Currently data is fetched from SQL server through queries

You should post the one that takes the longest along with the information identified in article at the second helpful link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

sqlinterset (9/2/2015)
I have few questions based on wht you mentioned:
1) Is SSAS server same as SQL Server
2) My pivot tables has around millions of data.
3) Currently many pivot tables and charts in one excel sheet.
4) Will it make really any impact converting it into OLAP cubes. Currently data is fetched from SQL server through queries.

1) No. SSAS is a different product. SSAS is an OLAP engine, SQL Server is a database engine.

2) Pivot tables are meant to show aggregated data, not millions of rows. Who wants to look at millions of rows? If you are showing aggregated data, then SSAS might indeed be a solution.

3) That's not a question.

4) It might. Depends on how well the cube is designed, what kind of data you have and how you query it.

This book might be a good start:

Knight's Microsoft Business Intelligence 24-Hour Trainer

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

seeingfuluu (9/6/2015)
just try
i think it will improve 80% at least

How can you say such a thing?

Depending on the data and the type of queries, SSAS might be even worse.

For example: distinct counts on millions of rows will probably be worse in SSAS.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

sqlinterset (8/31/2015)
Currently i am using sql queries to show data in excel pivot tables. however, data is very large around in millions and take so much time in refreshing the reports.
I am planning to build star schema and OLAP to fetch data in excel pivot tables. Will it be good start? my excel contains many pivot tables and pivot charts. Is there any good article to start it?
i am using sql server 2012.

You're not fixing the problem and you're possibly bringing other problems into play such as maintaining and refreshing your "star schema".

My recommendation is that you've correctly identified the problem of the queries taking a long time. Fix them. 😉 Once that's done, they might be useful in maintaining your "star schema".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)