proclarity question

Hi,

I have a question regarding Proclarity viewer and whether it can assists me.

I have a UDM as follows.

A fact table containing a sale for each salesman. There can be more that one row per salesman

This Fact is connected to a dimension dim_salesman that hold details about each salesman and other more dimensions.

Another fact containing a number of vacations each salesman took. There is more that one row per salesman.

This fact also connected to the same dim_salesman and and other more dimensions.

I want to inquire for example - how many vacations and sales each saleman had, sliced by the dim_saleman.

If I uses a pivot table excel to view a report that contains data from both fact tables I get wrong data.

Is the proclarity viewer able to browse 2 different fact tables and join them to a single report?

Thank for you help.

Hello. Both ProClarity Professional 6.2 and Excel 2007 can create reports from data in two separate fact tables.

This is not as much a client question but how you have related the two measure groups(fact tables) in the cube editors dimension usages tab. In SSAS2005 one cube can have several fact tables(measure group).

HTH

Thomas Ivarsson

processing time issue

Hi all,

I have a quick question regarding the processing time.

I created view in db for the count purposes with huge fact table. ( I'm using dimension table which

joins the 1 fact and 1 dimension to count the key for the fast data retrieval)

If I write the query for the count in management studio and it gives a results within 5 sec.

But, if I add this view in DSV and process the cubes than it takes around 50 minutes.

This causes due to the Fact table? or what else?

I don't know what to check and please give me some comments.

Thanks in advance.

Have you run a profiler trace against the server. You could probably trace either SQL or SSAS to capture the exact SQL statement that is being executed. If this view is being used as a fact table SSAS will do a full scan of it and do a look up for each row to each of the associated dimensions. Doing a trace against SSAS might give you some more hints on what is going on. There are also a number of SSAS specific counters in Performance Monitor that might give some insight into what is going on.|||

Hi Darren,

Thanks for your good tips.

As you mentioned above, the view looks up for each row of associated dimension (fact table). In the Fact, total number of rows is around 153,000,000 and it makes cube processing very slow now.

So to improve better performance and faster processing what would you recommend in this situation? I only need the count of store number not that huge fact table.

Please give me some comments.

Thanks.

|||

There would be a couple of possible approaches to improving the processing performance.

One would be to partition the fact table so that you only process recent records and not the full 153 million.

Another might be to look at doing incremental processing. If you can keep track of which records are new since you last processed, you can just load those.

Finally you could possibly do a "group by" in your view to reduce the granularity, but often reducing the granularity reduces the flexibilty of your design and would generally be a last resort.