Re: SSAS 2k5: Fact and Bridge Fact table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

hi Deepak,
I have a question about design for MSAS 2k5 on one-to-many of facts, when
one fact became a dimension to another fact.

I have cube with Item fact table and transaction fact table.

one-to-many ( Item.transaction_key is FK to TRANSACTION fact table
whereTransaction.transaction_key is PK ).

I also have Date with date_key as PK.
Transaction.date_key is FK in Transaction table to Date table
and
Item.date_tend_key is FK in Item table to Date table

I have 5 dimensions around TRANSACTION fact table and 3 dimensions around
ITEM fact table.

When building the cube I've defined TRANSACTION fact table as a 'fact and
dimension',
and ITEM table as a 'fact' .

Every aggregation/slicing by all dimensions works OK ...
EXCEPT when I am using Date dimension (date hrchy) with Item
facts(measures).
(date dimension has 4 levels of calendar date hrchy,fiscal hrchy and week
hrchy - none works with Item fact measures).

when drilling I am getting the same total for single measure, for example,
$12199999 for any date level or any dimension that is join to Date dimension,
even for years(upper level of cal.date hrchy) that do not have any data for
items' aggregations(measures)...

I know the explanation of problem sounds very vague...many things could
happened but ...
data is correct and if I am building a separate cube for item (without
transaction fact) - everything works well for all Date slicing...

I can build 2 cubes based on 2 separate facts - transaction and item...
but before doing so ...I would appreciate any suggestions on what can be
wrong with design of One-to-Many Facts , i.e. when fact is a fact and
dimension with Shared date dimension...or smth else...

thank you ,
Alex Deiden


"Deepak Puri" wrote:

This MSDN paper should help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90
/html/sql2k5_mmdiminas.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.
...



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

.

Relevant Pages

  • Re: Analysis Manager Operations extremely slow
    ... both RDBMS SMO and Analysis Services ... testing domain authentication. ... cube editor slowness if connecting to Oracle. ... Gender dimension to "M" and still browse the Sales cube. ...
    (microsoft.public.sqlserver.olap)
  • Re: Automatic MOLAP questions
    ... there is a lock when the polling query is evaluated. ... when you execute a polling query, the result of the query and the previous datetime can be used to do an incremental processing. ... so an incremental process only add content in a cube and can't update the cube. ... SSAS will not try to process the dimension. ...
    (microsoft.public.sqlserver.olap)
  • Re: SSAS2005 - When do partitions need to be processed?
    ... InsertCubeProcessingLog("Process Dimension All End", 1, 1, BeginTime, Now, ... The actual processing of partitions doesn't appear to be significant here ... Processing Cube 'Sales Current' completed successfully. ... With AS2005 there is a proactive caching option at the dimension level, ...
    (microsoft.public.sqlserver.olap)
  • Re: The attribute key cannot be found (a new twist in the story)
    ... Here is another story and NeilW unless I don't get better control of 2005 I ... In AS2000 we have only one place in cube to fix data integrity issues. ... I have actually solved my problem by deleting the dimension and creating it ... I'd run a trace (if you're using SQL Server RDBMS) to ...
    (microsoft.public.sqlserver.olap)
  • Re: Filtering a dimension based on value from another cube?
    ... creating a new dimension called "Targeted population". ... Employees with more then XX absences in the month ... calculated cells formula can apply the MDX formula in the cube. ... then the standard calculation is applied. ...
    (microsoft.public.sqlserver.olap)