473,218 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

Q: Analysis Services Cube Design

I have a (hopefully typical) problem when it comes to cube design. We
store millions of product records every year, broken down by
month/quarter. Each product can be assigned to various heirarchial
classification groups etc. The data in an OLTP DB occupies roughly
100G for a typical year.

We're looking at breaking this out into OLAP to provide faster access
to the data in various configurations and groupings. This is not a
problem, as this is the intended use for Analysis Services.

The problem is that we apply projection factors on the product prices
and quantities. This would be ok if it only happened once, however,
this happens every quarter (don't ask why). The projection factors
change 4 times a year, and they affect all historical product records.

This presents a challenge because to aggregate the data into a useful
configuration in the cubes, you throw out the detail data, but this
means throwing out the price and quantities which are needed to apply
the projection.

So if you have Product A at $10 and Product B at $20, and roll both up
into Category X, you'll have $30, but you'll lose the ability to apply
a projection factor of .5 to Product A and .78 to Product B. They're
rolled up.

I don't want to regenerate the cubes every 3 months. That's absurd.
But we can't live without the ability of projection the
prices/quantities on a product level (detail level). So how can this
be achieved when the other cubes are created at a higher level with
less details and sums of the detail data?

My initial guess is that we have to update the product data, and then
reaggregate all the other data that is built upon that product data.
Is there any other way to apply math to the data on the way out?

Thanks in advance!

Regards,

Zach
Jul 20 '05 #1
2 3753
zn*****@hpis.net (znelson) wrote in message news:<f5**************************@posting.google. com>...
I have a (hopefully typical) problem when it comes to cube design. We
store millions of product records every year, broken down by
month/quarter. Each product can be assigned to various heirarchial
classification groups etc. The data in an OLTP DB occupies roughly
100G for a typical year.

We're looking at breaking this out into OLAP to provide faster access
to the data in various configurations and groupings. This is not a
problem, as this is the intended use for Analysis Services.

The problem is that we apply projection factors on the product prices
and quantities. This would be ok if it only happened once, however,
this happens every quarter (don't ask why). The projection factors
change 4 times a year, and they affect all historical product records.

This presents a challenge because to aggregate the data into a useful
configuration in the cubes, you throw out the detail data, but this
means throwing out the price and quantities which are needed to apply
the projection.

So if you have Product A at $10 and Product B at $20, and roll both up
into Category X, you'll have $30, but you'll lose the ability to apply
a projection factor of .5 to Product A and .78 to Product B. They're
rolled up.

I don't want to regenerate the cubes every 3 months. That's absurd.
But we can't live without the ability of projection the
prices/quantities on a product level (detail level). So how can this
be achieved when the other cubes are created at a higher level with
less details and sums of the detail data?

My initial guess is that we have to update the product data, and then
reaggregate all the other data that is built upon that product data.
Is there any other way to apply math to the data on the way out?

Thanks in advance!

Regards,

Zach


You might want to post this in microsoft.public.sqlserver.olap - you
may get a better answer there.

Simon
Jul 20 '05 #2
znelson wrote:

This presents a challenge because to aggregate the data into a useful
configuration in the cubes, you throw out the detail data, but this
means throwing out the price and quantities which are needed to apply
the projection.
Not necessarily.

So if you have Product A at $10 and Product B at $20, and roll both up
into Category X, you'll have $30, but you'll lose the ability to apply
a projection factor of .5 to Product A and .78 to Product B. They're
rolled up.
They're rolled up, yes, but you can also have the detail data in the
cube depending on your dimension structure. For example, if your
hierarchy has the actual products in it (i.e. your hierarchy in this
example would be:

Category X
Product A
Product B
Category Y
Product C
Product D
etc.

I don't want to regenerate the cubes every 3 months. That's absurd.
If you want the historical data to reflect the new projections then I
don't see that you have a choice. Plus, you'll be updating your cube
with new sales data anyway.
But we can't live without the ability of projection the
prices/quantities on a product level (detail level). So how can this
be achieved when the other cubes are created at a higher level with
less details and sums of the detail data?

My initial guess is that we have to update the product data, and then
reaggregate all the other data that is built upon that product data.
Is there any other way to apply math to the data on the way out?


Unfortunately I really don't understand enough about your scenario or
how the projections are used to be able to offer any suggestions beyond
what I've already mentioned above. I hope that helps you some.

Zach Wells

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bounty Bob | last post by:
Hi all, I received the following email from a work colleague. Any help with this would be greatly appreciated. ----------------------------------- Can you please post the following...
3
by: Daniele | last post by:
I have a 40 MB database in excel format. I need to use it in Analysis Services, I imported the data by DTS (Data Transformation Services), everything is working I can see the database, but I can't...
1
by: BruceGilpin | last post by:
I was at a Microsoft sales presentation last week for the new version of SQL Server, Yukon. They had an extensive presentation on SQL Server and Reporting Services but didn't mention Analysis...
2
by: Ramir Santos | last post by:
my analysis server is hosted on a win2k server with IIS, the hosting page is on another machine. I have followed everything as per MS KB article and I could not connect through the analysis server...
0
by: wwalkerbout | last post by:
Greetings, Although, this relates to Analysis Services administration, I thought I'd post it here in case someone with the administrative side of SQL Server who subscribes to this Group may also...
1
by: Nesaar | last post by:
Hi I have been tasked to draw up design documentation for two web services we need to develop. Sort of the systems analyst for this project. I would like to know if anyone out there has example...
0
by: Kannan.V [MCSD.net] | last post by:
Hi All, Here is my situation, I have my windows application on windows server 2003 and have the analysis services on a different system (win 2003) on the same network. I am using DSO to...
0
by: flickimp | last post by:
Hi We have two servers on Analysis Manger. On Server 1 - we have created a cube and have allocated permissions/roles to 150+ accounts......quite a tedious job (as each role is slightly...
1
by: russzee | last post by:
Hello, Can I import an OLTP (Reltional DB) as a Data Source into SQL Server Analysis Services 2005 and then use the Cube Wizard and the new Data Source View feature to create the OLAP model ? ...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.