Tuesday, July 29, 2014

what is hierarchy and types of hierarchy ?

      A hierarchy is a very important part in OLAP Cube. Hierarchy is an Arrangement of Attributes in a dimension to improve the performance of Analysis and allows users to roll up attributes from lower level to upper level or in another word to drill down attributes from summary levels down to detail levels.

Natural Hierarchy When we create a user-defined hierarchy, we define relationship between hierarchy levels. It helps the Analysis Services to create useful aggregations which in turn increases query performance. When levels in a hierarchy are linked in a natural relationship like one-to-one or many-to-one, such hierarchies are known as Natural Hierarchies. Like in a Calendar hierarchy, Day level related to the month level, month level to the Quarter and so on.
Non-Natural Hierarchy - In some type of hierarchies the natural relationship is lacking between the hierarchy levels and branches of the hierarchy descend to different levels. Such hierarchies are known as Non-Natural or Unbalanced hierarchies. For example, the Marital Status à Gender hierarchy is a non-natural hierarchy as Marital Status and gender attributes do not have a relationship to each other.
Ragged Hierarchies - Are the type of unbalanced hierarchies wherein, the logical parent member of at least one member is not in the level immediately above the member. In this case the hierarchy descends to different levels for different drilldown paths. Take an example of geographic hierarchy. The levels available are Country à State / Province à City. But some countries like Greece do not have provinces thus creating a ragged hierarchy.
In dimension table supporting a ragged hierarchy, the logically missing members can be represented in using nulls or empty strings, or they can contain the same value as their parent to serve as a placeholder.
Parent-Child Hierarchies When an attribute in a dimension table has the parent attribute which is related using a self-referencing relationship, it is known as Parent-Child Hierarchy. Such hierarchies are constructed from a single parent attribute. Please refer to the DimEmployee dimension schema in the AdventureWorks2008 database. The ParentEmployeeKey column in the table is related with EmployeeKey primary key column with foreign key relationship. This means each record in a table is related with another record in the same table through a parent-child relationship. In this kind of structure the data can be derived using self-join.

Sunday, July 27, 2014

What are the differnces between a RDBMS schema and a data warehouse schema?

RDBMS Schema
  •   Used for OLTP systems
  •   Highly Normalized
  •   Difficult to understand and navigate
  •   Difficult to extract and solve complex problems
DWH Schema
  •   Used for OLAP systems
  •   De-normalized
  •   Easy to understand and navigate
  •   Relatively easier in extracting the data and solving complex problems

Thursday, July 17, 2014

What is use of IsAggregatable property?

     In Analysis Service we generally see all dimension has All member. This is because of IsAggregatable property of the attribute. You can set its value to false, so that it will not show All member. Its default member for that attribute. If you hide this member than you will have to set other attribute value to default member else it will pick some value as default and this will create confusion in browsing data if someone is not known to change in default member

How will you make an attribute not process in ssas?

By selecting  “ AttributeHierarchyEnabled = False”, we can make an  attribute not in process.

How will you hide an attribute in ssas ?

We can hide the attribute by selecting “AttributeHierarchyVisible = False” in properties of the attribute.