Excel 2007 provides many APIs which are quite powerful from analytics point of view.Not all of them are exposed via the UI though ( for some reason … ). SQL Server MVP Greg Galloway has developed this very cool tool which is EXCEL 2007 add-in and lets you do very cool stuff.

You know that calculated members are evaluated on the fly.But the only way you can have this calculated member available from browsing is by doing this physically in the cube. There is a way that you can do it in the excel itself .These are called Private Calculated Members

Simple ratios or differences and stuff that are very specific to a (or small group) of cube users can be put in here , rather than cluttering the cube.

Limitation: If you run "OLAP Tools... Convert to Formulas" on a PivotTable with private calculated members, the private calculated members will show N/A. There is no known workaround at this point other than having your OLAP administrator define these calculated members in the cube itself.

There is much more to this too and the download and more information is available here http://www.codeplex.com/OlapPivotTableExtend.

First of all lets understand what Parent Child dimensions are and where and When they are modelled.

From MSDN

A parent-child dimension is based on two dimension table columns that together define the lineage relationships among the members of the dimension. One column, called the member key column, identifies each member; the other column, called the parent key column, identifies the parent of each member. This information is used to create parent-child links, which are then combined into a single member hierarchy that represents a single meta data level.

For example, in the following Employee table, the column that identifies each member is Employee_Number. The column that identifies the parent of each member is Manager_Employee_Number. (This column stores the employee number of each employee's manager.)

Here is an example of how the data underlining a parent-child dimension might look like.

Important  If a parent-child dimension is included in a cube with a fact table that has rows associated with the dimension's nonleaf members, you must set the dimension's Members With Data property to Nonleaf data visible or Nonleaf data hidden. Otherwise, processing the cube fails.

The Members With Data property indicates whether nonleaf members of a parent-child dimension are allowed to have associated fact table data. By default, nonleaf members are not allowed to have associated fact table data, so the property is initially set to Leaf members only.

Limitations of Parent Child Dimensions

Parent child dimension do provide flexibility when modelling dimensions like employee organization structure and other self referencing dimensions but beware that this flexibility is not free.

The primary issue is that since there is no consistent levelling, you cannot have pre-calculated aggregates for intermediate levels.

If you go to Mosha Pasumansky’s Blog link which is given below , you will find the codeplex link to the Jon Burchel (a Senior Support Escalation Engineer in Microsoft) PCDimNaturalizer project.

http://sqlblog.com/blogs/mosha/archive/2008/08/25/parent-child-dimension-table-naturalizer.aspx

NOTE :- It is also added to BIDS Helper's new release http://bidshelper.codeplex.com/Wiki/View.aspx?title=Parent-Child%20Dimension%20Naturalizer  but if you want to call it from SSIS or external application you will need binaries which can be downloaded from http://pcdimnaturalize.codeplex.com/