Showing posts with label regarding. Show all posts
Showing posts with label regarding. Show all posts

Wednesday, March 28, 2012

Performance regarding transposing fact data

Hello

I have an Analysis Services performance question:

Scenario:

My Fact table has a column that indicates some value-ID and only one value column. Out of this fact table I'd like to create a cube with two measures, one for each value-ID. So I could either write a query like

Code Snippet

SELECT key1, key2,

sum(case valueID when 'A' then value else null end) as measureA,

sum(case valueID when 'B' then value else null end) as measureB

FROM myFacts

GROUP by key1, key2

Then I'd use this table or view as fact table.

Or I could include the case syntax directly in the measure definitions inside Analysis Services and use the original table.

Does anybody know what's better concerning performance

-regarding cube processing?

-regarding querying the cube?

Are there other things that make one solution the better one?

Hi,

If you use your select statement to load the data into the cube.

-Processing will be slower as it has to execute the case statement

-Querying will be faster

If you read in the values with out the case statement

-Processing will be quicker as it doesn't have to execute the case statement

-Querying will be slower, you will have to create to MDX calculated measures that do the same as you are showing.

If you use your group by select statement, the granularity of your fact is also going to change - is that really what you want?

I would just create two MDX calculated measures, does depend on the size of data I suppose.

CREATE MEMBER CURRENTCUBE.[measures].[measureA] as

([Measures].[Value],[Dimension].[Dimension Key].&Angel)

,non_empty_behavior = [Measures].[Value],VISIBLE = 1;

Might look something like above - ish Smile

Hope that helps,

Matt

|||

Hi Matt

Thank you for your reply.

(First I apologize for not having added that I'm using Analysis Services 2000 not 2005.)

Generally I don't want to create a calculated member for each measure becaus calculated members are only calculated during run time but I want to have the measures correct after the cube was processed.

In Analysis Services 2000 I don't have the ability to use a select statement as a source for a fact table. Instead a table or view must exist in the underlying datasource. So there I have the possibility to either create a table or view with the select statement above or I enter the case expression inside the measure definition. So somewhere the case will be executed because having a dimension like "valueID" is not what I want or, wait, ...maybe it could be also a possible solution to have just one measure in the cube and have a valueID dimension... But this leads to more difficult queries and some inconveniances for cube users.

Regards

Chris

|||

Hi,

My 2000 skills are a little rusty and weren't perhaps that great in the first place Smile

I would probably create a view, it would allow you to compare A against B or even create a total of A and B if you need to.

Sorry I can't be much help, perhaps someone else will help you.

Cheers

Matt

Saturday, February 25, 2012

Performance monitoring

Hi,

I want to collect performance measures regarding the import of data
and the growth of resulting extract_tables.

I use - say - 15 tables from a erp-system (like JDE Edwards)
to build a -say - sales-warehouse and a MS-OLAP-cube.

For every incoming table I got a dts-package witch is
protocolled into msdb.sysdtspackagelog.
Every package got the name
[Build]_[Subsystem]_[Table_name]
e.g. JDEdwards_Sales_F0005
The destination table is namend e.g. extr_F0005

Now:
With a seperate DTS-package I transport the
records from msdb-db into my build-db
- say - JDEdwardsExtract.
Name: extr_performance_monitor
(eventually filter on buildname, because there are several builds in
my system)

So this result is quit good and easy to handle for
seeing elapsed time per day.

But the dtslog won't tell me, how many records the dtspackage
had to copy.(and there is one at least with no records (Cubeupdate))

Now the count(*) comes in.

In the dts-package
sys...log -- to -- extr_performance_monitor
I added the columns
extr_table_name,
extr_table_rowcount,
extr_table_timestamp.

With
select name, 'extr_' + replace(name, '[Build]_[Subsystem]','') as
extr_table_name from extr_performance_monitor
I cut the original dts packagename down to the extr_.. name.

But I don't have a clue how to solve the count(*)

Example:
dts_package_name,... , extr_table_name,extr_table_rowcount, extr_table_timestamp

[Build]_[SS]_[F0005], ..., extr_F0005, 0, sysdate.

I think about a package wich is running after the last
data_import (and cube_refresh) is done.
(but the same day)

So the result could be:

Table_name (as dimension category)
Time to perform
Number of records in import table
Records per second.

The next step could be to look for required space.

The result should be a grafik - say - over 12 month
were you can easily see the amount of data performend
time consumend, (table space used),
and - very important -
you could extrapolate your hardware requirements.

Looking forward for any hints.

Thanks in advance.

MichaelMichael,

How "married" are you to the DTS methodology?

What are the formats of the files?

Are you in the middle of building this, or maintaining it?

If I had a choice, I'd use stored procedures...

I guess you could use ActiveX in DTS

Where are all the DTS mavens?

Stored procedures are just more flexible and faster..

OK a statement like that deserves a big

MOO *

* My own opinion