DMV Queries: To Get All Attributes in a cube

Use below Dynamic Management View (DMV) queries to get all the dimension and attributes of your cube

SELECT

       [CATALOG_NAME] as [Database],

       CUBE_NAME AS [Cube],

       [DIMENSION_UNIQUE_NAME] AS [Dimension],

       HIERARCHY_DISPLAY_FOLDER AS [FOLDER],

       HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],

       HIERARCHY_IS_VISIBLE AS [VISIBLE]

FROM

       $system.MDSchema_hierarchies

WHERE 

       CUBE_NAME  ='My Cube Name' AND 

       HIERARCHY_ORIGIN=2

ORDER BY

       [DIMENSION_UNIQUE_NAME]

 

Note: Replace 'My Cube Name' with your cube name

Cannot login to SSAS 2008 from SSMS?

Issue: 
When I tried to connect to a named instance of SQL Server 2008 Analysis Services (SSAS) by using SQL Server Management Studio, I received the following error message intermittently:

The 'InstanceName' instance was not found on the 'ServerName' server. (Microsoft.AnalysisServices.AdomdClient)

Solution:
 When I checked my services all the services was up & running, even I had restarted my SSAS services, but it didn't help me. but the issue was resolved after restart the SQL Server Browser service. 

If you aware, how to to restart any windows services that related SQL server then Ignore the steps below:

Steps to Restart "SQL Server Browser" service:
1.On the Start menu, right-click My Computer, and then click Manage.
2.In Computer Management, expand Services and Applications, and then click Services.
3.In the list of services, double-click SQL Server Browser.
4.In the SQL Server Browser Properties window, click Start or Stop.
5.When the service starts or stops, click OK.

After restarting the "SQL Server Browser" service, I tried to connect SSAS via SSMS, it works. Hope it may help you.

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


Have a look at the exhibit given below, hope it will help you to understand about the types of hierarchies in better way


Balanced Hierarchies:-

Each level in a hierarchy has the same number of members above it as any other member at the same level


Un Balanced Hierarchies:-

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.


Ragged Hierarchies:-


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.

“Derived Measure” Vs. “Calculated Measure”

The Main difference between “Derived Measure” and “Calculated Measure” is when the calculation is performed. The following tables will help you understand about this.

Derived Measure	Calculated Measure
This is calculated before the aggregation are created	This is Calculated after aggregations are created
The values of this measure is stored in the Cube	This values aren’t stored in the Cube

You can choose any one the measure depends on your requirements. The main purpose for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

How To Create XMLA Script For Cube Process Using SSMS

Following steps will explain “how to generate a XMLA Script for Cube process” using SQL Server Management Studio (SSMS).

1.       Open SQL Server Management Studio by clicking on the SSMS shortcut icon which located in the Start --> All Programs --> Microsoft SQL Server 2008 R2 --> SQL Server Management Studio.

See the given exhibit for reference (select any version whichever you installed)

2.       Click on the “Connect” button, which is available at Object Explorer, Choose “Analysis Services…” as shown Below

3.       Enter the server detail/credential you want to connect

4.       After you connected to the with proper server details, you can see the list of databases available on the server in Object Explorer Windows as shown below

5.       In Object Explorer, Select the cube which you need to process --> Right Click on “Process”

6.       Upon click on process, Following model dialog window will appear  --> Click on Script link

7.       Script link helps you create a script in 3 ways as listed in the exhibit

8.       I’ve selected the option to generate the output script on “New Query Window”, so it generated the script on New XMLA Script window as mentioned below:

Sample XMLA Script:-

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Parallel>

    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">

      <Object>

        <DatabaseID>Analysis Services Tutorial</DatabaseID>

      </Object>

      <Type>ProcessFull</Type>

      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process>

  </Parallel> 
</Batch>

Note: system will generate the script as per the input you provided on Process window in step 6

Maximum Capacity Of SSAS Object

Today, one of my colleague asked me, what is maximum number of measures can be created within a Measure group?, I don't know the answer for that, but the question insist me to find the solution for that.

When I search the solution for this by Google, I came across following information of other SSAS Object's limitation.

Hope it helps you

SSAS Object	Maximum Number of Objects/Size
Databases in an instance	2^31-1 = 2,147,483,647
Dimensions in a database	2^31-1 = 2,147,483,647
Attributes in a dimension	2^31-1 = 2,147,483,647
Members in a dimension attribute	2^31-1 = 2,147,483,647
User-defined hierarchies in a dimension	2^31-1 = 2,147,483,647
Levels in a user-defined hierarchy	2^31-1 = 2,147,483,647
Cubes in a database	2^31-1 = 2,147,483,647
Measure groups in a cube	2^31-1 = 2,147,483,647
Measures in a measure group	2^31-1 = 2,147,483,647
Calculations in a cube	2^31-1 = 2,147,483,647
KPIs in a cube	2^31-1 = 2,147,483,647
Actions in a cube	2^31-1 = 2,147,483,647
Partitions in a cube	2^31-1 = 2,147,483,647
Translations in a cube	2^31-1 = 2,147,483,647
Aggregations in a partition	2^31-1 = 2,147,483,647
Cells returned by a query	2^31-1 = 2,147,483,647
Record size of the source query	64K
Length of object names	100 characters
Maximum number of distinct states in a data mining model attribute column	2^31-1 = 2,147,483,647
Maximum number of attributes considered (feature selection)	2^31-1 = 2,147,483,647

SSAS: The Targeted Mailing ~MC cube has no measure groups. Errors in the metadata manager.

Problem:

When I tried to deploy/browse/process a cube, i'm getting the following error.

Errors in the metadata manager. The Targeted Mailing ~MC cube has no measure groups. Errors in the metadata manager. An error occurred when loading the Targeted Mailing ~MC cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008 SE.0.db\Targeted Mailing ~MC.1.cub.xml'. 

Solution:
If the OLAP DB file is corrupted then we used to get this kind of problems.This can be solved if you have the backup of SSAS DB Solution, otherwise we can't. If you've then follow the Steps given Below:

1. Stop SQL Server Analysis Services
2. Delete the OLAP DB related files from the location where the message suggests, ofcourse, this is the location of SSAS Database.

In My case, It is from "C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data"

3. I deleted "Adventure Works DW 2008 SE.0.db" and "Adventure Works DW 2008 SE.0.db"
4. Restart SSAS Services
4. Then i deployed again, Now it works fine for me.
5. Still if not works, the you need to deleted only other DB related files, then you've to deploy as new

MDX: Tuple Vs Set

Tuple:

• It is a collection of members from different Dimension
• It Allows only one member from each dimension
• It is a Basic Unit to forming an axis
• As a rule, tuples are enclosed in parenthesis

Set:

• One or More tuples from same dimension,
• All tuples enclosed in a set with same order of dimensions

Set Can contains tuples and tuple can consist of dimensions.

Pupose of ampersand (&) character in MDX

In MDX Query, A member can be referenced by either its member name or by its member key


The ampersand (&) character is used in MDX to differentiate a member key from a member name, as shown in the following example:


Reference the member by its member Key:
[Time].[2nd half].&[Q4]


Reference the member by its member name:
[Time].[2nd half].[4th quarter]

SSAS Processing Options

The following table contains the Lists of Processing Options available in the SQL Server Analysis Services and which objects they applies to:

Processing Option	Database	Dimension	Cube	Measure Group	Partition	Mining Structure	Mining Model
Process Full	X	X	X	X	X	X	X
ProcessClear	X	X	X	X	X	X	X
ProcessDefault	X	X	X	X	X	X	X
ProcessData		X	X	X	X
ProcessIndexes		X	X	X	X
ProcessUpdate		X
ProcessAdd		X			X
ProcessStructure			X			X
ProcessScriptCache			X
ProcessClearStructureOnly						X

Business Intelligence

Business Intelligence is a combination of the following three components:


1. OLTP system,
2. Data mart and
3. Analysis Service cube (or OLAP)

BI: Dimensional Model - Star Schema

The star schema is the simplest style of data warehouse schema. The star schema consists of a few fact tables (possibly only one) referencing any number of dimension tables.

Star Schema Format 1:

Star Schema Format 2:

Star Schema Format 3:

Star Schema Format 4: