Local cube files have an extension of .cub. We can open the SSMS and open a new mdx query editor window. Then we will create a GLOBAL CUBE query. This is a strange thing. Although the query uses CREATE GLOBAL CUBE, but it is in fact known as local cube or offline cube.
CREATE GLOBAL CUBE [Adventure Works]
STORAGE 'C:\Documents and Settings\bcsmith\My Documents\Adventure Works.cub'
FROM [Adventure Works]
MEASURE [Adventure Works].[Internet Sales Amount],
MEASURE [Adventure Works].[Internet Order Quantity],
MEASURE [Adventure Works].[Internet Extended Amount],
MEASURE [Adventure Works].[Internet Tax Amount],
DIMENSION [Adventure Works].[Account].[Accounts]
DIMENSION [Adventure Works].[Department].[Departments]
DIMENSION [Adventure Works].[Destination Currency].[Destination Currency]
LEVEL [Destination Currency],
MEMBER [Destination Currency].[Destination Currency].&[US Dollar]
DIMENSION [Adventure Works].[Employee].[Employees]
DIMENSION [Adventure Works].[Organization].[Organizations]
DIMENSION [Adventure Works].[Scenario].[Scenario]
Some of the important thing we need to notice here while writing the query.
- We always need to mention the cube name before specifying dimension or measure.
We don’t need to use the measure group name before measures. In fact we cannot specify the measure group name. The syntax for specifying the measure is
[Cube Name].[Measure Name]. We may often get this type of error message if we use the measure group name also.
If we have any many-many dimension mapping, then we must include atleast one measure from the intermediate measure group. It is often a practice to hide the intermediate measure groups as the user does not need to see this. But while create offline or local cube, we don’t have any choice. We must include at least one measure from each of the intermediate fact table. In the above sample, the measure groups that has a prefix of Rel_ are the intermediate measure groups. If we don’t use this we may get an error message similar to this:
The above query shows the full local cube creation. But of course we can create a partial local cube also. We just can exclude measures and dimensions that we don’t want to make available in our local cube. But of course we have to take care of the above mentioned rules.
Hiding a Dimension in the Local Cube
If you want to hide a dimension the syntax is:
DIMENSION [Cube_name].[Dimension_Name] HIDDEN
but you will often find that the dimension is not hidden in the local cube. Why this happens. If your cube has any attribute whose AttributeHierarchyVisible is set to true, then you cannot hide the dimension. The local cube creation will not give any error but eventually the dimension will be visible. So hide all the attribute hierarchy in the cube and then use the Hidden qualifier in the local cube creation syntax.
Just drag an Execute SQL Task and double click to open SQL Task Editor. Under the SQL Statement section, you will find the slot for Connection.
Click on New connection and you will see the Configure OleDb connection Manager window. Click on the New button.
Now , you will find the window called Connection Manager. Click the provider called Native OleDb/SQL Server Native Client and choose Microsoft OLEDB provider for Analysis Services 10.0.
Now, click ok and go back to the SQL Task Editor window and write your MDX statement (I used a Create Global Cube statement for creating a local cube). Now, you can exeucte the task and see the result.