I have data in a table like CHILD_ID PARENT_ID 1 0 2 0 3 2 4 1 5 4 6 1 7 4 8 7 '0' is root and '1' is child of '0' and '4' is child of '1' and '7' is child of '4' and so on the parent child relation goes I wanted to have a tree structure (a structure like I can query for all the childs of a parent)of this. I am completely new to ssas please advise. I am using SQL Server 2008 R2
SSAS allows you to create a Parent-Child hierarchies in the Dimensions.. So if in your sample the table will represent a hierarchy, yo can then mark the PARENT_ID attribute as ParentKey and start building the Parent-Child hierarchy in the cube. What more, if the design the parent child relationship in the data source view of SSAS, then when using Create New Dimension Wizard, it will automatically detect this and allows you to create the hierarchy automatically. But probably the table is only the hierarchy part of some other table. So you will probably need to join it with the other table containing the rest of the attributes to construct some usable dimension from it.
Hi , This is more of a design issue and has nothing to do with SSAS.Before building the SSAS cube , you need to create a UDM Unified Dimension Model which is nothing but Fact Tables (For measures) and Dimension Tables (For dimensions) in database. In your case i think the most appropriate will be Star flake schema where you have hierarchies at dimension levels.(as shown in your example) Other design schemas for UDM are star schema (most common )and fact constellation schema. For more information browse for designing database schema for ssas. Thanks, Kannan