data being repeated.

Hello, I have merged a master and detail tables into a fact table in order to create a cube, the master table let's say it's a sales table, and the detail the products of the sale, I merged both tables into a single fact table, so the data for one sale is being repeated once for each detail, this is the way I want. So if my sale has a total, let's say $100, and the detail has 3 products, when I visualize the cube the total for the sale is $300, and obviously this is incorrect, and let's say too, if there are many sales with different totals and differents details, the calculation can not be a simple MAX function or AVG, so, somebody know how I can show the data as I want?

Thanks in advance!

odin_dark@.yahoo.com.mx

If you're using AS 2005, you could consider the new many-many dimension feature:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp

>>

Many-to-Many Dimensions in Analysis Services 2005

Richard Tkachuk
Microsoft Corporation

June 2005

Applies to:

Microsoft SQL Server 2005 Analysis Services

Summary: See an example of using the Many-to-Many dimension in SQL Server 2005 Analysis Services to analyze sales data, and get ideas for other uses such as treating medical conditions, software testing, and more.

>>

There is an example, in Adventure Works, where Sales Reason relates to Internet Sales as a many-many dimension, via the intermediate measure group: Sales Reasons.

So, in your case, the main measure group could be based on the master table, and the intermediate measure group could be based on the detail table. And dimensions like Product, which relate directly to the detail table, could be configured as many-many dimensions. In your example of $100 total sale (on the main measure group), $100 would be attributed to each of the 3 individual products, but the contribution at the All Product level should still be $100, not $300.

|||

Hi,

U can u many to many concept.But this can be solved if u proper joins while joining the table.

Some time the relation between the table is not set properly which result in repeated data.

from

Sufian

|||

Hello all, thanks for your reply, unfortunately I'm using analysis services 2000, so is there another way?

and.. wich kind of joins are u talking about, because I want the data to be repeated in the fact table to improve performance, but in the cube I want to fiter this data correctly.

thanks in advance!

|||

Hi,

By repeting the data in the fact table will not increase the performance but it will effect the performance in negative way.

When the cube is processed the it will take more time and more space to store ur aggregates and data.

I am talkin about the joins between the facttable and dimension table.

Some time when u create the cube through desing view analysis services creates the join where it fines refrence of relation keys.

HTH

from

Sufian

|||Unfortunately, I don't know of any way to achieve exactly what you want with AS 2000. In master-detail scenarios, I typically set up separate fact tables and cubes for the master and detail, but combined them in a virtual cube. Still, I had separate measures from the 2 cubes.|||

I've already tried using separate fact tables and combining them into a virtual cube, but I do need the measures "united", not separated, because, how I can know to which "master" registry corresponds the "details" registries?

|||

thanks all!, but I'm still having the problem, mohd sufian could you please send me an example about the joins between tables you're talking about? because i think i have the proper joins but the data is being repeated..

thanks in advance!

|||

Dear ,

In SSAS2005 can have more then one fact table.

So when u going the fact table with the dimension table .The join shoud be Facttable primary key is used as a refrence key in the dimension table.

The same methord is applied in all.

In this way AS will not repeat the data .

HTH

Regards

Sufian