Anchor modeling (Part I)

Introduction
One of the datawarehouse concepts anchor modeling, is an interesting approach. In the past i've written some blogposts about creating some Datavault models based on AdventureworksLT database and i would like to implement an Anchor model based on this small database. So, in the coming months you can expect some more blogposts about this modeling technique. But before i'll do this i'll go deeper into the fundamentals of Anchor modeling. Most of the information in this blogpost can be found on www.anchormodeling.com.

Background
Founders of the Anchor modeling are Olle Regardt and Lars Ronnback. Anchor Modeling is an agile database modeling technique suited for information that change over time both in structure and content. It provides a graphical notation used for conceptual modeling similar to that of Entity-Relationship modeling, with extensions for working with temporal data. The modeling technique is based around four modeling constructs: the anchor, attribute, tie and knot, each capturing different aspects of the domain being modeled. The resulting models can be translated to physical database designs using formalized rules. When such a translation is done the tables in the relational database will mostly be in the sixth normal form (Wikipedia)

Anchor Modeling has four basic modeling concepts:

• Anchors. Anchors are used to model entities and events. This table only contains a surrogatekey. this table is comparable with the hub of the datavault model. But here is no need for a business key in the Anchor table in contrast with the datavault model.
• Attributes.  
• Ties. Ties model the relationships between anchors and this seems to be comparable with the Link table in Datavault.
• Knots. Knots are used to model shared properties, such as states or gender..

Attributes and ties can be historized when changes in the information they model need to be kept.

Below an example of a model as used by the modeler:

Name convention
Below an explanation of the basic symbols that is used in Anchor modeling:

•  This is the symbol for the Anchor. The naming convention is two letter mnemonic + Descriptor. For instance PE_Person, AD_Address or LO_Location.

• This is the symbol for a Knot and the naming convention here is   three letter mnemonic + descriptor. Examples are GEN_Gender, STA_STATE, COU_Country

• And this is the symbol for Attribute. Inherited Anchor mnemonic +  three letter mnemonic +  Inherited Anchor descriptor + descriptor. Examples are PE_SUR_Person_Surname.

•  This is a symbol for Tie.   Two letter mnemonic  +  Anchor Descriptor +  Two letter mnemonic +   Anchor Descriptor

These are the basic symbols used by the modeler tool of www.anchormodeling.com.

Conclusion
It's too soon to draw some conclusions about the anchor modeling technique. This is modeling technique is currently under investigation by me. 

Greetz,

Hennie

TOGAF and BI principles

Introduction
During study of TOGAF i was pointed on the usage of principles. Principles are guidelines/rules that are defined before/while building an Enterprise Architecture and these principles are seldom changed. Otherwise why is a principle a principle, off course ;-). As BI can be a part of the Enterprise Architecture careful thoughts should be taken about BI and principles. What kind of BI principles do you favour?

Background
TOGAF contains a couple rules and guidelines for use in applying TOGAF. One of these guidelines are architecture principles and these can be defined for the architecture being developed. These Architecture principles can be split up into three levels of principles: Enterprise, IT and Architecture.

How are principles defined? In TOGAF there is a standard way of describing principles. A name is given to the principle and each pricciple should have associated rationale and implication statements, for understanding and acceptance.

Example principle
Below an example of a principe that can be used in a Self Service BI project as i'm currently involved.

Name:	Self Service BI
Statement:	(Power)Users have access to information in such a way they can build reports themselves.
Rationale:	The ability for (power) users to access their own reports and content lowers cost and backlog of the IT department..(Power)users know the meaning of information better than IT personal.
Implications:	(Power) users should have reporting tools available. (Power) users can build reports without intervention of IT. Reports are published on a reportserver where they are managed by IT personal. Users will be trained in building reports, Meaning of BI and the usage of the reporting server.

Conclusion
If you want BI to be integrated in a Enterprise Architecture there will be a need to develop BI principles and perhaps you'l lbe asked to define the principles that will hold for years. So if you're a BI department think about the principles that you want to achieve or you are achieving.

Greetz,
Hennie

SSAS: optimizing processing query using reference dimension relationship

Introduction
In one of my former posts titled "Select Facts with Reference and Many2Many relationships" I blogged about the usage of reference dimension relationships in cubes. In my current clientsituation i'm experiencing a major slowdown while processing the cube. Some investigation learned that the reference relationsshiop seems to be the problem. In this blogpost i'll explain the problem, a solution (by replacing the load query) and a conclusion.

The problem 
I'll be using the labsituation that i've describedin my former blogpost "Select Facts with Reference and Many2Many relationships". In this blogpost i've shown how to build a reference dimension relationsship and a many2many relationsship. For this partiular problem i'll focus on the reference dimension  relationsship because this is experiencing performance slowdon at the client i'm currently operating. 

Suppose i've the following situation (see diagram below). In this situation i've three reference relationsships: Customer, Department and Date. These dimensions are the normal dimensions of the Orders fact. Because we want to influence the numbers on the OrderLines fact we have to create a Reference Dimension relationship.

The problem is that i'm experiencing a slowdown in performance during processing  the cube because of the usage of the reference dimension relationsship in the cube. This (the processing query) will result in the following load query of the cube:

SELECT 
[dbo_tblFactOrderlines].[OrderLines_Count] AS [dbo_tblFactOrderlinesOrderLines_Count0_0],
[dbo_tblFactOrderlines].[Product_ID] AS [dbo_tblFactOrderlinesProduct_ID0_1],
[dbo_tblFactOrderlines].[Category_ID] AS [dbo_tblFactOrderlinesCategory_ID0_2],
[dbo_tblFactOrderlines].[Orders_ID] AS [dbo_tblFactOrderlinesOrders_ID0_3],
[dbo_tblFactOrders_4].[Customer_ID] AS [dbo_tblFactOrdersCustomer_ID4_0],
[dbo_tblFactOrders_5].[Department_ID] AS [dbo_tblFactOrdersDepartment_ID6_0],
[dbo_tblFactOrders_6].[Date_ID] AS [dbo_tblFactOrdersDate_ID8_0]
FROM [dbo].[tblFactOrderlines] AS [dbo_tblFactOrderlines],
[dbo].[tblFactOrders] AS [dbo_tblFactOrders_4],
[dbo].[tblFactOrders] AS [dbo_tblFactOrders_5],
[dbo].[tblFactOrders] AS [dbo_tblFactOrders_6]
  WHERE 
  (
   ([dbo_tblFactOrderlines].[Orders_ID]   =  [dbo_tblFactOrders_4].[Orders_ID])
    AND  
   ([dbo_tblFactOrderlines].[Orders_ID]   =  [dbo_tblFactOrders_5].[Orders_ID])
    AND  
   ([dbo_tblFactOrderlines].[Orders_ID]   =  [dbo_tblFactOrders_6].[Orders_ID])
  )


As you can see that for every "Reference Dimension" relationship an extra join is created to the same table (?!). The WHERE clause uses 3 joins for Customer, Department and Date. At my client i'm having 5 joins and it takes now about 15 minutes to load a specific measuregroup. When i replace the 5 joins with 1 join the processingtime drops dramatically, from 11 minutes to 35 seconds.

The solution
I've replaced the OrderLines tabel in the datasource view by the following view

DROP VIEW vwOrderLinesCube
GO

CREATE VIEW vwOrderLinesCube AS
SELECT 
[OL].[OrderLines_Count] AS [OrderLines_Count],
[OL].[Product_ID] AS [Product_ID],
[OL].[Category_ID] AS [Category_ID],
[O].[Orders_ID] AS [Orders_ID],
[O].[Customer_ID] AS [Customer_ID],
[O].[Department_ID] AS [Department_ID],
[O].[Date_ID] AS [Date_ID]
FROM [dbo].[tblFactOrders] AS [O]
INNER JOIN [dbo].[tblFactOrderlines] OL ON [O].[Orders_id] = [OL].[Orders_id]

So i  had to draw some extra lines in the datasource view and that is a drawback of the solution, unfortunately. The model is now less intuitive and less clear. Too bad...

With this new setup the following processing query is executed:

SELECT 
[dbo_tblFactOrderlines].[OrderLines_Count] AS [dbo_tblFactOrderlinesOrderLines_Count0_0],
[dbo_tblFactOrderlines].[Product_ID] AS [dbo_tblFactOrderlinesProduct_ID0_1],
[dbo_tblFactOrderlines].[Category_ID] AS [dbo_tblFactOrderlinesCategory_ID0_2],
[dbo_tblFactOrderlines].[Orders_ID] AS [dbo_tblFactOrderlinesOrders_ID0_3],
[dbo_tblFactOrderlines].[Customer_ID] AS [dbo_tblFactOrderlinesCustomer_ID0_4],
[dbo_tblFactOrderlines].[Department_ID] AS [dbo_tblFactOrderlinesDepartment_ID0_5],
[dbo_tblFactOrderlines].[Date_ID] AS [dbo_tblFactOrderlinesDate_ID0_6]
FROM [dbo].[vwOrderLinesCube] AS [dbo_tblFactOrderlines]

Now you can see that the three joins has been replaced by the view, which contains now the one and only join to the Orders table.

Conclusion
Mainly this solution is needed when the processing time takes a long time because of the usage of the reference dimension relationsship in the cube design. In my opinion because of bad design of the load query of the cube. Too bad but sometimes you have to use the pragmatic solution.

Greetz,
Hennie

SSAS : Error with many-to-many dimension and level of granularity

Introduction
In this blogpost a brief explanation of an error i've encountered during a implementation of a 'many2many relationship' in a SSAS cube. Although i haven't found the rootcause of this problem i do want to share with you the solution. I did only found one relevant website of Melissa Coates with the same sort of problem. So hopefully this will help the MS BI community a bit futher.
 
Problem
In my former post i've worked out an example with Referenced Dimension relationship and a Many2Many relationship. As you have seen in this blogpost everything worked fine but when i implemented this solution at my client i got the following error(off course i replaced the names used at the client with the names used in the example) :

Errors in the metadata manager.

The 'Category' many-to-many dimension in the 'Orders' measure group requires that the granularity of the 'OrdersDD' dimension is lower than that of the 'OrderLines' measure group

Solution
The situation at the client is much more complex than the labsituation that i've descibed in the blogpost. I have about 15 measure groups and about 75 dimensions. So debugging the problem is hard to do. I had a couple options to debug this problem:

1. Downgrade the complete solution to a certain level where the problem is more manageable. Break everything out of the solution that is not needed for understanding the problem. Off course use a copy of your development environment ;-).
2. Trail and error. In this approach you tweak the solution a bit and see whether the error disappears.
3. Reproduce error in labsituation. As already said, í've created a labsituation where everythings works fine. A manner could be to change some settings in the project and see whether the error appears.

I decided to take a gamble on option 2 and found a solution very quickly, very fortunately!!

The solution was to change the Fact dimensionship relationship of the OrdersDD dimension with the Orders Measure group to a regular dimension relationship.

Conclusion
A short post about a problem i've encountered during implementation of a many2many relationship in a SSAS Cube. I've presented a solution that helped solving the error. Currently testing whether everything works fine but everythings seems working very fine. 

Greetz,
Hennie

SSAS : Select Facts with Reference and Many2Many relationships

Introduction
In this blogpost i'll explain how to connect two starschemas with each other and how to setup the cube in SSAS  in case you want to use the dimensions of the two facts selecting the measures in both facts. In other words, suppose i've two facts that have a 1:N relation (eg. Orders and OrderLines) and there are a couple of dimensions (eg. Customer, Department, Date, Product and Category). And, sometimes because of naming conventions (?) a referenced dimension relationship is automatically created between  a 1 fact and a N fact. This will enable selecting the measures of the N fact with the dimensions of the 1 fact. Using a Many-2-Many (M2M) relationship will enhance selecting measures in the 1 fact with the dimensions of the N fact.

The blogpost is ended with leasons learned part and conclusions.

The problem
Let's elaborate this example to a lab situation. Suppose i've the following situation:

There are two starschemas: Orders and Orderlines (OrderDetails) and they have a 1: N relationship. OrderDD is the connecting dimension that actually is a 'fact dimension' in SSAS (not materialized but could be if you want). Physically there is an Order_id attribute in the Orderlines tables present to enhance the Fact dimension relationship in the cube in SSAS.

The Order Fact has three real dimensions : Customer, Department and Date and Order Lines has a Category and a Product dimension.

Mostly, the problem is that by default relationships are not created automatically, meaning that using the dimensions doesn't have effect on the facts of both starschemas (when you browse the cube). There are additional steps needed to enable this. This will be explained in this blogpost.

There are two possible directions to influence the measures on both facts:

• Selecting measures of the N fact (eg. Orderlines) with dimensions  (eg. Customer, Department or Date) of the 1 fact (eg. Orders).
• Selecting measures of the 1 fact (eg. orders) with the dimensions (eg. Categroy or Product) of the N fact (eg.Orderlines)

In the solution described below i'll start with an initial setup where there is no relationship, only the Fact  dimension OrdersDD, then i'll add an extra relationship 'Referenced Relationship' and finally the end solution with the 'Referenced Relationship' and the 'Many2Many relationship', together.

Solution
In this solution i've identified 3 steps:

1. Possible initial situation.
2. Reference dimension relationship solution
3. Endsolution with Reference and Many2Many relationship

 1) Suppose you build a cube on the described starschema, the following structure could be the result. Could because because of namingconventions a 'reference dimension' relationships is sometimes automatically gernerated (see also step 2).

If the cube is browsed you can observe that the selection with dimension "Customer" selects the measures of the orders fact but not the orderlines measures. The OrderLines count is not controlled by the dimension "Customer".  For every dimension value the same (total value) is shown. This is not possible by default.

The other way around, selecting measures of the Orders fact by the dimensions that belongs to the  Orderline fact shows also the same problem. For every dimension value the same (total value) is shown.

2) In step 2 of the solution the reference dimension relationship is set up with the customer, Department en Date dimension. The OrdersDD functions as an intermediate dimension that directs the cube to relate the information from the Orders to the OrderLines starschema.

Below a screendump and it shows that the dimensionvalues has an imnpact on the measures on both starschemas : Orders and Orderlines.

Selecting values in the Orders starsschema with the product dimension still shows incorrect values.

3) In this final step i've added the Many2Many relationship to the cube. The Product and Category dimension now has a Many2Many relationship with Order.

The dimensions of the Orders starschema has an impact on the measures of OrderLines.

And now the dimensions of the OrderLines starschema has an impact on the measures of  the Orders starschema.

Conclusion
In this solution i've shown how to connect two starschemas (with a 1:N relation) and how to tune the cube in a way that all dimensions has an impact on the measures on both starschemas. The steps that are needed are:

1. Create a common dimension (physical or by a fact dimension relationship)  that reprensent the 1:N relation between the starschemas.
2. Create a reference dimension relationship for the dimensions that belong to the 1 Fact (Orders).
3. Create a Many2Many relationship for the dimensions that belong to the N Fact (OrderLines)

Greetz,
Hennie