OLAP Cube Development

Named Sets

Simple static list:

The {} make the group of tuples () into a Set:

{([Date Document].[Date].&[–3.67E2]),([Date Document].[Date].&[–1.])}

CREATE SET CURRENTCUBE.[Yesterday and Prior Year]

AS {([Date Document].[Date].&[-3.67E2]),([Date Document].[Date].&[-1.])}, DISPLAY_FOLDER = 'Date Doc Parts' ;

Advanced Features

Many to Many relationships

Note that using Many to Many relationships is expensive, performance-wise. Use wisely.

http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf

THEORY: For a simple usage, you link from a regular fact table to a regular (one to many) dimension. Then from a (fake) bridge fact table to both the regular dimension and the “many-to-many” dimension. Now the real fact table can be linked to the many-to-many dimension.

Simple example:
Scv Order Cube, with Svc Order lines as fact table, Svc Order Header, and Complaints. But there can be many complaints associated with one order.

We want to be able to look at any complaint and see all the order lines associated with the orders associated with that complaint. And be able to select an order and see its complaints listed.

1. Make an Order Header dimension and link it to the Order lines table normally.
2. Make a Complaints dimension, add it to the cube, but don’t link it in.
3. Make a Complaints Bridge table with Order Header key fields and Complaint keys, one record per unique combination.
4. Add this Bridge as a new MEASURE GROUP, the measure type being Record Count. Make the measure NOT VISIBLE.
   
5. In the Dimensions tab of the cube, make Regular relationships from the Bridge measure group to the Order Header and Complaints dimension.
   
6. Now you can add a Many to Many relationship between the Complaints Dimension and the Order Lines fact table.
   

Writeback Cubes for Budgets

http://richardlees.blogspot.com/2010/08/write-back-cubes-are-easy-with-excel.html
table is created after next processing.
Don’t add dimensions or delete, or will break the table connection.
Set to Automatically Calculate Changes, so it will recalc formula measures when published.

summarizing the permissions set needed to grant to a Non-Sysadmin “Writeback Users” Role in order for role members to be able to successfully writeback to a cube using Excel 2010 (for example):

•Process Database is required (On the General Tab of the Role) - may also require Read Definition on this tab
•Grant Read access to the Data Source used by the writeback cube (On the Data Sources tab for the Role)
•Grant Read/Write access to the Cube (On the Cubes tab for the Role) - this is the intuitive Role property that you would THINK is sufficient to enable writeback access on a cube…
•Enable cell data read/write permissions on the Role’s Cell Data tab and add a boolean expression to return true for all cells in the cube that this Role is permitted to writeback to - this expression MUST be set, and as mentioned above can be set to 1=1 to enable writeback to all writable cells in the cube.
http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/696758b5-f6f9-474d-86dd-3ac834853d82

cell security in first and last boxes
[Measures].currentmember.name=“Target Amt”

After creating the partition, process it. Then process it again. Then check that writeback table was created.