Why use analysis services? 1

[joelwenzel]

Hi,

I'm new to analysis services. I've created a cube with it though and can browse through my data based on the different dimensions. However, I'm do not really see what is so special about analysis services. I can use a pivot table in excel and look at dimensional data the same way.

Analysis services must have another purpose but I don't know what it is.

 

[mrdenny]

Correct Analysis Services does the same thing as a pivot table. The difference is that with a pivot table in Excel you have to download all the data from the database, and then have excel draw the data into the pivot table. With a very large database of hundreds of millions or billions of records this will take for ever (not to mention won't fit in excel).

This is where Analysis Services comes in. With AS you can create the cube and schedule it to process on a schedule so that when you come in the next day the cube is already processed and all the hard work in complete. All you need to do it view the data.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[joelwenzel]

ok, thanks

 

[alandool]

Since we are on this subject ... it has brought to mind more questions ......

As I have posted on here before .... I am researching Analysis Services (AS) for use where I am employed. At this juncture, I do not have a clear picture in my mind of why I would want to use AS. As posted above ... (previously), I see that AS allows dealing w/ millions of rows as opposed to 65k in Excel.

I work for a large health insurer where we deal w/ huge amounts of data ...... millions upon millions of rows. We answer business related questions from this data and quite often work w/ detailed data as opposed to summarized data. We have many data analyst or data pulling tools available such as WebFOCUS, QMF, SQL Server, Business Objects, Teradata SQL Assistant, Clementime, SAS, and of course Excel, Access, and so on. Databases/warehouses we have available are DB2 on mainframe, Teradata, and SQL Server. Teradata is becoming our main data warehouse source. Thus, I am attempting to use AS and connect it to Teradata. Other times I will connect to SQL Server.

Concerning AS, my questions are this:

1) Based on what I've said above, why would I want to use AS ..... is it just b/c it will deal w/ many more rows of data at a time (wh/ admittedly is a good reason)?

2) How does AS fit in the scheme of things, i.e. from other folks experience ... is it a good tool as opposed to what else is available? In others' experience, where does it rate or rank w/ other tools available?

3) And most important, we often have to eliminate breaks or summarize data downloaded to Excel or brought into Access due to the number of records. Can AS deal w/ millions of rows of detailed data and then allow analysis on this data w/ drill-down ---- possibly thru Excel or somewhat like Excel?

Sorry for the long post. Any feedback will be much appreciated.

Alan

 

[MDXer]

From Bottom to Top

3) And most important, we often have to eliminate breaks or summarize data downloaded to Excel or brought into Access due to the number of records. Can AS deal w/ millions of rows of detailed data and then allow analysis on this data w/ drill-down ---- possibly thru Excel or somewhat like Excel?

Yes. Past project I was the DW was 2Billion transactions with a 3 year history. Properly designed cubes would report queries subsecond the most complex of queries was 5 minutes. This was using MOLAP storage modes with a number of preaggregations. Our worst designed cube (not by choice) was 250GB, and an average query time of less than 1 minute and aggregated all 6Billion records.

Yes In cubes you can drill to detail.

2) How does AS fit in the scheme of things, i.e. from other folks experience ... is it a good tool as opposed to what else is available? In others' experience, where does it rate or rank w/ other tools available?

AS Doesn't replace your Databases or your Warehouses it builds the data contained within those structures. It could possibly replace Business objects or BO could at least be used to report the data within the cubes, although BO is ugly ontop of MOLAP cubes. BO Likes to show off there ROLAP reporting and has built there product around ROLAP their MOLAP tool is nothing special (last I saw).

1) Based on what I've said above, why would I want to use AS ..... is it just b/c it will deal w/ many more rows of data at a time (wh/ admittedly is a good reason)?

This is a harder question to answer than it seems because there is no true right answer. Can AS bring benefit and even reduce some work efforts? I'm willing to bet yes. However the only answer that is going to be correct has to come from your business requirements. Moving to a new reporting tool requires a large investment in resources both dollars and people. AS definetly is one of the Best OLAP tools on the market from my experience (Worked with AS, Played with BO currently use Cognos). Also the strength of the solution will only come by the Experience of the developers. Unfortunately AS is not always as straight forward as it may seem. Yes building a basic cube is easy in SQL 2005 they have a wizard to do it, but will these cubes allow for extremely complex reporting? NO. It maybe hard for an individual in a large orginization to make a sale like this based on some of the factors discussed.


Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[alandool]

Thanks once again, MDXer. Looks like I'm just going to have to get in there and see what I can with AS. Company wide, there are all kinds of reporting tools used. There are some folks in the company that use AS already, I've heard, but it's not widespread or in my area. I'm just looking into AS for our departmental needs only as an additional option. Who knows, it could become the best option.

Alan

 

[wwwSQLOBSERVERcom]

Hi Alan,

My two cents as well:
- it's not only about the volume of data but also about how they relate to each other. With these tools you may choose various levels and entry points for effecting drilldown but also multiple dimensions, measures and facts
- these tools also help business users who may not be as query-savvy as you are and thus allows quick and transparent access to data

Excel is a great tool but its simplicity is a double-edged sword.

HTH

http://www.SQLobSERVER.com

the Microsoft BI/DW community

 

[venetianjigsaw]

Hi all. First let me say this is a very good thread. I appreciate all the info that has been posted. I have recently entered the world of Data Cubes and Pivot Tables, so this makes me a rookie and also a little intimidated b/c of the knowledge that each of you possesses, but none the less, the info listed is great!!!

I am somewhat of an IT Journeyman in that my skills, over the years, have taken me from Business Analyst (1995) to "Greenhorn" developer (present day). My company develops software that is used in the Medical Industry and we presently provide our clients w/ three canned reports. We are now adding data cubes as part of a "custom" reporting solution. Our application is written in ASP and resides on SQL Server 2000. While I am able to create the cube, my goal is to deploy to our clients based on their requirements. Understand that the client has the same SQL Server setup as we do (i.e. database and tables are mirrored w/ minor data entry changes).

Now that I've given you the history, here's where I am going w/ all of this:

1) When we deploy the client's cube (both the .cub and .qry files), is there any special setup that will need to take place on THEIR end? I realize they'll need Office 2002 (or greater), but will they need OLAP Administrator permissions or any other special permissions in order to access?

2) If OLAP admin permissions are required (or any other permissions w/ the exception of Administrator), can they be configured in such a way to keep the client from hitting the db and reconfiguring the cube?

3) Will Analysis Services need to be installed on their server before they can use the cube we deploy?

4) Since the client will be using a Pivot Table, is there any other way it can be refreshed on a periodic basis, at the server level, other than the check box that they can select in the Pivot Table properties?

5) Does Office Web Components (OWC) already come w/ Office or does the client even need them? Is OWC only if you want to view the Pivot Table in IE (does this even need to be a point of conversation/concern)?

6) When the client upgrades Office to a newer version, will the cube need to be reconfig'd? I think I saw in XP that the Pivot Table looks pretty similiar to one in Analysis Services...again, that may be the OWC piece.

7) Is there anything else I should know that I may be leaving out as a result of my total ignorance to the whole data cube, pivot table process.

I know this is pretty long, but I wanted to give as much info on the front end to avoid alot of unnecessary posts.

As my dad would say, "It's better to get it right the first time then to have to do over". I'm sure that someone else might have said that, but he's right.

Thanks for any info that you can/may provide.

 

[blom0344]

venetianjigsaw,

You might consider working with offline cubes. In that case your clients need not bother with server software , they only will need to use Excel to create pivottables on the offline cubes. Compared with online use offline cubes are slightly more limited, but one can work with them regardless of having a server-connection...

Ties Blom