Databases
StackOverflow2013
Postsdbo
POMany to Many Relationship between Fact and Dimension with Parent-Child Hierarchy
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMany to Many Relationship between Fact and Dimension with Parent-Child Hierarchy
    primarykey
    data
    text
    <p>I have the following Business Scenario. I have a Fact Table with some facts. There is also a Dimension Category that has a parent-child hierarchy. There are many different hierarchies packed in one Category dimension. I mean there are a lot different types of categorization. There are also a many-to-many relationship between Fact and Category Dimension. The facts can be bound to more then one category at the time. I have now smth about 200 hierarchies, maybe a little bit more, but I do not expect to get more then 100k-200k rows in dimension table. </p> <p>I have added Category Dimension two times to the cube in order to be able to have different hierarchies on columns and on rows. My problem is, that I do see the values ob the crosses between the different hierarchies. If I filter one hierarchy on rows and another one on columns, I get nothing</p> <p>I think my question somehow relates to the multiple choices example from many-to-many revolution, but I have not found the sample for it.</p> <p>I am using SSAS 2012 Multidimensional. </p> <p>I am also interested if it is possible to smth like that in Tabular :))</p> <p>Here is the Database schema </p> <p>CREATE TABLE [dbo].[cat]( [cat_id] [int] NOT NULL, [parent_cat_id] [int] NULL, [name] nchar NULL, CONSTRAINT [PK_cat] PRIMARY KEY CLUSTERED ([cat_id] ASC)</p> <p>CREATE TABLE [dbo].[Fact]( [fact_id] [int] NOT NULL, [name] nchar NULL, [value] [int] NULL, CONSTRAINT [PK_Fact] PRIMARY KEY CLUSTERED ([fact_id] ASC)</p> <p>CREATE TABLE [dbo].[fact_cat]( [fact_id] [int] NULL, [cat_id] [int] NULL, [id] [int] NOT NULL, CONSTRAINT [PK_fact_cat] PRIMARY KEY CLUSTERED ([id] ASC) GO</p> <p>ALTER TABLE [dbo].[fact_cat] ADD CONSTRAINT [fk_fact_cat_cat] FOREIGN KEY([cat_id]) REFERENCES [dbo].[cat] ([cat_id]) GO</p> <p>ALTER TABLE [dbo].[fact_cat] ADD CONSTRAINT [fk_fact_cat_fact] FOREIGN KEY([fact_id]) REFERENCES [dbo].[Fact] ([fact_id]) GO</p> <p>Thanks in advance for your help!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsmikesh
    1. USsmikesh
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMany to Many Relationship between Fact and Dimension with Parent-Child Hierarchy
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload