Hi all

I'm a newbe to AS2005. I built a sales cube for a retail company. This cube
hass 6 Dimensions: SKU, Subcategory, Category, Brand, Store, Time.

Until now I have two "native" mesures: Sales and quantity.

In this AS 2005 cube I tried to implement two calculations:
SUM(PERIODSTODATE([Time].[Year - Month - Date].[Year],
[Time].[Year - Month - Date].currentmember),[Measures].[Quantity])

AND

SUM(PERIODSTODATE([Time].[Year - Month - Date].[Year],
ParallelPeriod([Time].[Year - Month - Date].[Year],1,[Time].[Year - Month
- Date].currentmember)),[Measures].[Quantity])

This calculation works fine on a high level, but when I drill down to the
SKU level, I encounter performance problems. This will not happen with
"native" mesures like quantity.

With only 40'000 records in the relational database this sounds like I do
something wrong at design-time.

I tried to resolve this issue with aggregation in my partition on my fact
table, without success. I have a Molap partition storage setting. When I try
to calculate aggregations, the optimation level reaches only 34%, regardles
my settings (estimated storages reaches ..., I click stop).

Q1: What can I do to resolve the performance issue?
Q2: Can anyone point me to the right direction with aggregation in Molap?

Thanks for your help!

Tom

Here some more informations about my test cube:
My fact table has the following columns:
ID
Dat
Info
SubCategory
Category
Brand
Product#
Productvariant
Store
Quantity
Sales

My Dimension Tables (with a foreign key relationship to the fact table) are:
- Category
- Subcategory
- Store
- Server TIME dimension

While I created the cube and the dimensions I used the default settings.

Everything works fine on the top. But when I start to drill down to the
bottom, that means record level, my query takes several minutes. When I try
to use reporting services the performance problems are even worse.

Because I'm a newbe to AS2005 and multidimensional databases, I dont know if
that is usual. But it looks very unpractical to me - I expected that I can
drill down to the product level very quickly.

Please help!

Tom.


"Tomilee" wrote:

Hi Tom,
Looks like you are tyring to calcukate "YTD Quantity" out of measure
Quantity. Have you tried this yet instead:

IIF (Time.CurrentMember.Level = Time.Year,
SUM(PeriodsToDate(), Measures.Quantity, NULL)

Please check the syntax since right now, I do not have MDX Editor handy
to me but essentially it should work at any level for any dimension
without any performance issues.

Also, in regards to your dimension, have you actually tried
implementing a single dimension consist of

Category, Sub Category, Barnd, SKU rather than having seperate
dimensions for each? So your dimension should look like this;

Product
Category
Sub Category
SKU + Brand
You can also add SKU as Member Properties of Product Dimension.
Your fact table should also be modified and remove any hierarchies and
have only surrogate keys of your dimensions and measures.
Hope this helps.
Regards.
Dip

Hi Dip

Thank you for your reply. Yes, I want to calculate a YTD and a previous YTD
value. And what helped is the following link to resolve the performance
issue:

http://msdn.microsoft.com/newsgroups...2-1e6431d82515

This approach using the Business Intelligence Wizard in the cube view is
more performant than my first approach.

Thanks for the advice for the design of fact tables.

I have two other questions:
1) When I drill down to the record level - in this design to SKU - is AS2005
going to the relational db? This means do I have to make sure that the
database is well indexed?

2) Can you point me to a direction with aggregations?

Thanks!

Tom

Hi Tom,
1) When I drill down to the record level - in this design to SKU - is
AS2005
going to the relational db? This means do I have to make sure that the
database is well indexed?

No, unless you performing drillthrough operation to bring whole lot of
line by line transactions for your sales value against a Brand for a
particular time slice. Now, regarding indexing, it would not matter
unless you are also using relational tables to produce transaction type
of reports using SQL Server Reporting Services or Excel. Then you need
proper indexing, Primary Key, Foreign Key relationship etc. for
absolute performance. It is always a best practice to have all your
relational table indexed. Now, one thing, indexing makes data loading
to DW Tables from OLTP source system approximately 3 times slower than
normal load. But during your data load, you can always first remove all
indexes and then load data and then put back all indexes to tables
after data loading finishes.

2) Can you point me to a direction with aggregations?

Unless you have a real monster dimensions such as over 50,000 members,
you can always live with MOLAP otherwise, you better off use ROLAP
structure which is performance intensive.
Thanks,
Dip

Thanks Dip - this you really helped me. One thing I discovered that is also
very important for performance is attribute relationship. When I use these to
query a cube with MDX I get a far better performance. I read something that
they show AS 2005 how to aggregate.

Best Regards, Tom

Hi Tom,

Did you find a solution to your aggregations stopping at 34% in the design
wizard?

I'm having a similar problem with my partitions stopping at a mere 10% and
using minimal storage given their size.

I was going to post a message but if you've already got a solution to this...

Cheers.
--
Kyle Henly
DBA
Nationwide Building Society
UK


"Tomilee" wrote: