ow to simplify what i'm doing (AS the solution?)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rsphere@gmail.com - 21 Jan 2006 15:27 GMT

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.

a la ...

select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)

I have simplified the above example just for brevity here.

So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.

The data I am using is from a warehouse on SQL 2005. I have very little

experience with warehouses - and know little about analysis services
and OLAP.

My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that

is fast and simple to query. Is that the idea of AS?

thx for helping a learner here. let me know if i am making enough sense

in the question.

Reply to this Message

Bobby Henningsen - 25 Jan 2006 13:12 GMT

Hi there,
you're stating that you use a datawarehouse. How is the structure in the DW
? Is it a relational star schema ? Some of the idea in using DW's is that
you transfer your data from your OLTP systems as they are not well suited
for reporting because you typically have to join, group and aggregate your
data in very complex ways. The star schema structure is relational as well
but far more intuitive and better suited for reporting. Why AS then ? AS
won't create new tables for you but place the data in cubes which is a
multidimensional storage. The point here is that you preaggreagate data and
by this speed up queries by actually querying your cubes, not the realtional
data. You could say that you're violating a lot of the rules for relational
design to get the better performance. In AS you will work with mesures and
dimensions. This could be Sales per customer. Sales is a measure and
customer is a dimension.

So the process is normally :

OLTP -> Staging -> DW -> Cube <- Report -- Your reports query A.S Cube

I can't figure out whether you are querying your OLTP system now. For
complex reports this is normally a bad idea. But it is hard for me to tell
if A.S is the right way to go not knowing your exact need for output.

Hope this enlighten things a little bit anyway.

Regards:)
Bobby Henningsen

>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
[quoted text clipped - 32 lines]
>
> in the question.

Reply to this Message