Attach a SQL Server Database without a Transaction Log File

Suppose we have only .mdf file and .ldf file is deleted or crashed then here is the solution for that:

For Example the database file name is AdventureWorksDW2012_Data.mdf

Here is the code:

USE [master]
GO
CREATE DATABASE [AdventureWorksDW2012] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_Data.mdf' )
FOR ATTACH
GO

Dimension Hierarchies

A hierarchy is the set of members in a dimension and their positions relative to one another. Hierarchies are sometimes represented as pyramidal structures. The only exceptions are hierarchies in which all members are at the same level.  From the top of a pyramidal hierarchy to the bottom, the members are progressively more detailed. The lower the level of a pyramidal hierarchy, the more members it usually contains. Analysis Services supports dimensions with multiple hierarchies. These dimensions provide similar yet alternate views of cube data. For example, a Time dimension with two hierarchies  can have a regular calendar hierarchy and a fiscal calendar hierarchy. In Analysis Services, a dimension with multiple hierarchies is defined as two or more dimensions with names that share the same prefix followed by a period but have different suffixes (for example, dimensions with names of Time.Calendar and Time.Fiscal). 


Types of Hierarchies in Data Warehouse:
Hierarchies are logical entities that an end user can use to analyze fact data.  These entities can be made of one or multiple levels.

Hierarchies are classified as three ways:-
1. Balanced
2. Unbalanced
3. Ragged



Balanced hierarchy:




Each level in a hierarchy has the same number of members above it as any other member at the same level. In a balanced hierarchy, all branches of the hierarchy descend to the same level, and each member's logical parent is the level immediately above the member. e.g. Each member in the Product Name level has a parent member in the Brand Name level, which in turn has a parent member in the Product Subcategory level, and so on. Also, every branch in the hierarchy can be traced, from highest to lowest level, to a member in the Product Name level.


UnBalanced hierarchy:




Each level in a hierarchy may not the same number of members as the member which are at the same level, refer the hierarchy structure shown above. In an unbalanced hierarchy, branches of the hierarchy descend to different levels. For example, an Organization dimension contains a member for each employee in a company. The CEO is the top member in the hierarchy, and the division managers and executive secretary are immediately beneath the CEO. The division managers have subordinate members but the executive secretary does not.

Ragged hierarchy:

It is not like balanced Hierarchy, the logical parent member of at least one member is not in the level immediately above the member. It will skip the immediate parent.  

For Example: In a company, all the divisions will be reporting to the VP thru hierarchical structure, but Finance department will directly reporting to VP without any middle level.


Ragged hierarchies of indeterminate depth are difficult to model and query in a relational database. Although SQL extensions and OLAP access languages provide some support for recursive parent/child relationships, these approaches have limitations. With SQL extensions, alternative ragged hierarchies cannot be substituted at query time, shared ownership structures are not supported, and time varying ragged hierarchies are not supported.  All these objections can be overcome in relational databases by modeling a ragged hierarchy with a specially constructed bridge table. This bridge table contains a row for every possible path in the ragged hierarchy and enables all forms of hierarchy traversal to be accomplished with standard SQL rather than using special language extensions.

The use of a bridge table for ragged variable depth hierarchies can be avoided by implementing a pathstring attribute in the dimension. For each row in the dimension, the pathstring attribute contains a specially encoded text string containing the complete path description from the supreme node of a hierarchy down to the node described by the particular dimension row. Many of the standard hierarchy analysis requests can then be handled by standard SQL, without resorting to SQL language extensions. However, the pathstring approach does not enable rapid substitution of alternative hierarchies or shared ownership hierarchies. The pathstring approach may also be vulnerable to structure changes in the ragged hierarchy that could force the entire hierarchy to be relabeled.

A ragged dimension is a dimension with at least one member whose logical parent is not in the level immediately above the member. For example, a Geography dimension consists of the levels Country, Province, and City. The logical parent of the Vatican City member in the City level is the Vatican City member in the Country level, because Vatican City is not divided into provinces. Because of the missing information for the Province level, the Geography dimension becomes a ragged dimension.
In a ragged dimension's table, the logically missing members, such as the province containing the Vatican City member in the City level in the preceding example, can be represented in different ways. The table cells can contain nulls or empty strings, or they can contain the same value as their parent to serve as a placeholder. For example, in the column for the Province level, in rows that contain members in the City level for the Vatican City member in the Country level, the placeholder member is also named Vatican City to match the name of the Country member. The nonexistent Vatican City province of Vatican City is stored as a placeholder member in the Province level because its parent at the Country level is the Vatican City member.