SSAS Local Cube

1         Creating a local cube

Local cubes and local mining models allow analysis on a client workstation while it is disconnected from the network. For example, a client application might call the OLE DB for OLAP 9.0 Provider (MSOLAP.3), which loads the local cube engine to create and query local cubes, as shown in the following illustration:

Ref: Microsft

1.1        Create the XMLA script

Create the XMLA script using server cube. Then copy script to Clipboard.

1.2        Create the local cube file and connect to the local cube.

Specify the local cube path and the filename (including .cub as the extention) as the server name. Then press "connect" which will create cube file.

1.3        Open a new XMLA query

Open a new XMLA query window by right clicking on local cube connection and paste the script copied above.

1.4        Execute the script file

You can search for following elements in the script file to make sure that you are connecting to right database.

<ConnectionString>

1.5        Process the cube

Right click on the local cube database and click “Process”

Ignore following error messages

Value cannot be null.

Parameter name: key (System)

2         Access local cube file

2.1        Microsoft Excel

.cub file can be simply browsed using Excel (2010/2013) and you will get the same interface that you get when access SSAS server cube using Excel.

2.2        XLCubed

.cub file can be also simple connected to XLCubed by creating XLCubed connection. Connection type should be “Analysis Service Cube File.

3         Benefits

•  Local cube files can dramatically improve browsing speed performance, especially when analysing low levels of large dimensions.
• Local cube files can also improve browsing performance because requests for additional data are handled on the local computer rather than across a network on an Analysis Server.
•  Local cube files can now be encrypted and password-protected.
• Analysis Services 2005 provides more precise control over the creation of local cube files.
• When you use local cube files you can give each user the specific data they need, or want, or are allowed to see.

4         Limitations

• When creating local cubes from server-based cubes, the following considerations apply:
•  Distinct count measures are not supported.
• When you add a measure, you must also include at least one dimension that is related to the measure being added.
• When you add a parent-child hierarchy, levels and filters on a parent-child hierarchy are ignored and the entire parent-child hierarchy is included.
• Member properties are not created.
• When you include a semi-additive measure, no slices are permitted on either the Account or the Time dimension.
• Reference dimensions are always materialized.
• When you include a many-to-many dimension, the following rules apply: 
• You cannot slice the many-to-many dimension.
•  You must add a measure from the intermediary measure group.
• You cannot slice any of the dimensions common to the two measure groups involved in the many-to-may relationship.
• Only those calculated members, named sets, and assignments that rely upon measures and dimensions added to the local cube will appear in the local cube. Invalid calculated members, named sets, and assignments will be automatically excluded.

Ref: http://technet.microsoft.com/en-us/library/bb522640.aspx

SQLServer function to update the same table where cursor selects values

--Check whether cursor is open

IF CURSOR_STATUS('global','c4') >= 0

BEGIN

        CLOSE c4

        DEALLOCATE c4

END

DECLARE @comparison VARCHAR(200)

DECLARE @source VARCHAR(200)

DECLARE @supplier VARCHAR(200)

DECLARE @itemname VARCHAR(200)

DECLARE @atc3names VARCHAR(max)

DECLARE c4 CURSOR FOR

SELECT DISTINCT

        [Comparison NAME],     

        [Data Supplier NAME],  

        [Data Source NAME],    

        [Item NAME]

FROM dbo.[Data Validation Attribute Items] WHERE [Comparison NAME] = 'Product'

FOR UPDATE OF [ATC3]

OPEN c4

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

WHILE @@FETCH_STATUS = 0

BEGIN

        -- Combine multile rows in to a column seperated by a ';'

        SET @atc3names  = 'DECLARE @atc3names  AS VARCHAR(200)

        SELECT @atc3names = COALESCE( @atc3names + '';'', '''') + [ATC3 Name]

        FROM

        (

        SELECT DISTINCT [ATC3 Name]

        FROM dbo.[' + @source + ' Fact Data] a

                INNER JOIN dbo.[Product Dim Product-Pack-Country] b

ON a.[Product-Pack-Country KEY] = b.[Product-Pack-Country KEY]

                INNER JOIN dbo.[Product Dim Product-Country] c

ON b.[Product-Country KEY] = c.[Product-Country KEY]

                INNER JOIN dbo.[Product Dim Product-Pack] f 

                        ON b.[Product-Pack KEY] = f.[Product-Pack KEY]

                INNER JOIN dbo.[Product] e ON c.[Product KEY] = e.[Product KEY]

                INNER JOIN dbo.[ATC4] g ON f.[ATC4 KEY] = g.[ATC4 KEY]

                INNER JOIN dbo.[PRODUCT DIM ATC4] h ON h.[ATC4 KEY] = g.[ATC4 KEY]

                INNER JOIN dbo.[ATC3] i ON i.[ATC3 KEY] = h.[ATC3 KEY]

        WHERE e.[Product NAME] = ''' + @itemname + '''

        ) A

       

        UPDATE .dbo.[Data Validation Attribute Items]

        SET [ATC3] = @atc3names

        WHERE [Comparison NAME] = ''Product''

                AND [Data Source NAME] = ''' + @source + '''

                AND [Item NAME] = ''' + @itemname +''''

        EXEC (@atc3names)

        SET @atc3names  = '';

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

END

CLOSE c4

DEALLOCATE c4

GO

Find an object in a SQL Server Instance (across databases)

Following block generates a concatenated string for a set of selected databases defined in the sub query.
DECLARE @command VARCHAR(max) ;
SELECT @command = coalesce (@command + 'union' , ' ') +
'    SELECT ''' + [DBName] + '''AS DBName,
    [type_desc] AS ObjectType
    FROM ['+ [DBName] + '].sys.objects
    WHERE [name] = ''ATC4''
'
FROM (SELECT TOP 1000 name AS DBName FROM master.sys.sysdatabases) d
EXEC (@command)

sp_MSforeachdb is an undocumented stored procedure, which can be used to run sql statements across databases in a SQL Server instance.
DECLARE @command varchar(1000)
SELECT @command =
'    USE [?] ; SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM sys.objects
    WHERE name = ''ATC4''
'
EXEC sp_MSforeachdb @command ;

Following cursor can also be used to execute SQL Statements across databases in a SQL Server instance.
DECLARE @DB_name varchar(1000)
DECLARE cur CURSOR LOCAL for
    SELECT name FROM master.sys.databases
OPEN cur
FETCH NEXT FROM cur into @DB_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM [' + @DB_name + '].sys.objects
    where name = ''ATC4''
    ')
    FETCH NEXT FROM cur INTO @DB_name
END
CLOSE cur
DEALLOCATE cur

Generate date range (Year and Month)

Following  T-SQL query can be used to generate Month Names and Year Names within a given period of time.

DECLARE
    @StartDate DATETIME,
    @EndDate   DATETIME;
--Define start date and end date
SELECT @StartDate = '2013-04-01', @EndDate = '2014-03-01';
WITH cteDates AS (
    SELECT TOP (
        DATEDIFF (mm, @StartDate, @EndDate) + 1)
        DATEADD( mm, DATEDIFF(mm,0, @StartDate ) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0
     ) AS MonthDate
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT
    YEAR(MonthDate) AS [Year],
    MONTH(MonthDate) AS [Month]
FROM cteDates

Ref:http://stackoverflow.com/questions/4181286/t-sql-select-get-all-months-within-a-range-of-years

View meta data - system objects - SQLServer

View list of constraints for a specific table
Select table_catalog, table_name, constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where table_name = 'area' order by table_name asc

View constraint names and table name for a specific column within the DB
Select table_catalog, table_name, column_name, constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where column_name = 'product-pack key' order by table_name asc