Desktop productivity for business analysts and programmers

Question on OLAP reporting

Reply
Occasional Contributor
Posts: 6

Question on OLAP reporting

Hi There,

We are starting to explore the possibilities of SAS OLAP. So far it's very useful to us. A SAS user from our company has following question about using OLAP cubes:

Is it possible to create following report from an OLAP cube in an easy way?

Hospital1

Hospital2

admission_type

emergency department

operating theatre

300

100

100

..

admission_source

Ward

ER

344

23

..

As I understand it all OLAP dimensions are hierarchical, so how can this be accomplished in an easy way?

Cheers,

Frank

Occasional Contributor
Posts: 6

Question on OLAP reporting

No reply :smileyconfused:

Does nobody else needs this?

Community Manager
Posts: 2,693

Question on OLAP reporting

Frank,

One tricky bit might have to do with the hierarchies.  You've got admission_type as one dimension, and admission_source as another.  Unless those roll up to another category, it might not be easy to get them to appear one on top of the other as shown in your example.

If not, you can create two slices: one with admission_type and one with admission_source, and then combine those outside of the OLAP analyzer using other EG tasks.  However, I assume that you don't want to aggregate these two dimensions together, adding the counts for "source" to the counts for "type", since that would double-count each event.

A new blog on the scene that covers OLAP topics:

http://www.bi-notes.com/

As well as Angela's blog:

http://blogs.sas.com/content/bi/

Chris

Occasional Contributor
Posts: 6

Question on OLAP reporting

Hi Chris,

The problem indeed is that the dimensions are not hierarchical to each other. The solution with the slices works nice but can be a big effort if you have lots of variabels to report on.

Do you know whether this is a restriction in SAS OLAP or a restriction in OLAP in general? Could it maybe be accomplished using some alternative MDX code?

Cheers,

Frank

SAS Super FREQ
Posts: 8,719

Question on OLAP reporting

Hi:

  Another county heard from...

  

   This sort of looks like a PROC TABULATE or Summary Tables Task to me. The data wouldn't even have to be in an OLAP cube to generate a report like this. The interior table lines might be an issue, since SAS isn't Word or Excel in making tables, but the table itself should be do-able.

cynthia

Trusted Advisor
Posts: 1,052

Re: Question on OLAP reporting

Count me in with Cynthia. This is one of the things that the Summary Tables task is ideal for. Try this with SASHELP.SHOES, and see if it's what you're after. Using Product as a proxy for admission_type, and Subsidiary as a proxy for admission_source, with Region across the top for Hospital, you'll get Product by Region, followed by Subsidiary by Region, the two subtables independent of each other.

EGx.bmp

(Excuse the 4.1 format...it's what I have at home.)

Esteemed Advisor
Posts: 5,198

Re: Question on OLAP reporting

I think we need to know the reason for using an OLAP cube.

It's concets quite different (OLAP and using tasks in EG).

And yes, you could probably get the data from the cube using MDX, but then you are responsible for putting it togehter by your self...

For me, it look like two different queries, and the measure in the columns aren't possible to sum, right?

So, I think it would be clearer to have these two hierarchies' measures displayed in two separate tables, and that could be achieved by using WRS and Infomaps.

/Linus

Data never sleeps
Community Manager
Posts: 2,693

Re: Question on OLAP reporting

Hear that? Tom runs EG at home. Now *that's* dedication.  Smiley Wink

Occasional Contributor
Posts: 6

Re: Question on OLAP reporting

Thanks for all your answers.

Proc tabulate will do for certain. (Also proc freq and proc univariate van be very usefull)

But for efficiency an simplicity reasons it would be nice if an end user could make those type of reports from a OLAP cube. The OLAP cubes contain all data and the end user knows how to handle the cubes...

I think it's just a wish from my side...

Ask a Question
Discussion stats
  • 8 replies
  • 156 views
  • 1 like
  • 5 in conversation