Process Analysis Cube by using XML

Follow the steps inorder to process analysis cube by using XML file:

1. Connect to a SQL Server Analysis Services Instance using SQL Server Management Studio.

2. In the Object Explorer Pane, expand the Analysis Services Instance, expand Databases, expand Analysis Services database, and then right click the Analysis Services cube which you want to process and then choose New Query ->XMLA from the drop down menu to open a new XMLA Query window.

3. In the XMLA Query window copy and paste the below XML. You need to change the DatabaseID and CubeIDappropriately as per your environment and then to do a Full Processing of the cube execute the XMLA Query.

<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">      <Object>        <DatabaseID>Adventure Works DWDatabaseID>        <CubeID>Adventure Works DWCubeID>      Object>      <Type>ProcessFullType>      <WriteBackTableCreation>UseExistingWriteBackTableCreation>    Process>  Parallel>
Batch>

4. You can also generate the XMLA Query to process the cube by selecting Script Action to New Query Windowoption under the Process Cube GUI as shown in the below snippet. 

Note

When you run the XMLA query as shown in Step 3 this will process the cube with the default options. Using the SSMS and then use the "Script Action to New Query Window" to see how all of these addtional options are configured in XML.

SQL Server Date Formats

Hi,
Here are some date formats which might help you in some situations while working in SQL Server.

Date Format	SQL Queries	Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	99-01-24
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	1999-01-24
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/99
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2005
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
Month DD, YYYY	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2006
Mon YYYY	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006
Month YYYY	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006
DD Month	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September
Month DD	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11
DD Month YY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72
DD Month YYYY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2006
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
Mon-YY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02
Mon-YYYY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002
DD-Mon-YY	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05
DD-Mon-YYYY	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2005

Handling Errors in SQL Server by using TRY...CATCH

Hi All,I found one of the best example to handle errors in SQL Server database.There are different ways to do that. Based on the situation or requirements. These examples can also found in MSDN.

The stored procedure uspLogError logs error information in the ErrorLog table about the error that caused execution to transfer to the CATCH block of a TRY…CATCH construct. For uspLogError to insert error information into the ErrorLog table, the following conditions must exist:

• uspLogError is executed within the scope of a CATCH block.
• If the current transaction is in an uncommittable state, the transaction is rolled back before executing uspLogError.

The output parameter @ErrorLogID of uspLogError returns the ErrorLogID of the row inserted by uspLogError into the ErrorLog table. The default value of @ErrorLogID is 0. The following example shows the code for uspLogError.

CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorTime] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) NOT NULL,
 CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
(
[ErrorLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Its time to create stored procedure-

CREATE PROCEDURE [dbo].[uspLogError]

@ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.

AS
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged.
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log.
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END;

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        RETURN -1;
    END CATCH
END; 
For Reference -- 
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://msdn.microsoft.com/en-us/library/ms179296.aspx

Data Warehouse v/s OLAP

'Data warehouse’ and ‘OLAP’ are terms which are often used interchangeably. Actually they refer to two different components of a decision support system. While data in a data warehouse is composed of the historical data of the organization stored for end user analysis, OLAP is a technology that enables a data warehouse to be used effectively for online analysis using complex analytical queries. The differences between OLAP and data warehouse is tabulated below for ease of understanding:

Data Warehouse

Data from different data sources is stored in a relational database for end use analysis Data is organized in summarized, aggregated, subject oriented, non volatile patterns.

Data is a data warehouse is consolidated, flexible collection of data Supports analysis of data but does not support online analysis of data.

Online Analytical Processing

A tool to evaluate and analyze the data in the data warehouse using analytical queries.

A tool which helps organize data in the data warehouse using multidimensional models of data aggregation and summarization.

Supports the data analyst in real time and enables online analysis of data with speed and flexibility.

Clustered v/s Non Clustered Index

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

SQL Server Views v/s Materialized Views

A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.

A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition.

A View can also be used to improve security by restricting access to a predetermined set of rows or columns.In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.

You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.A materialized view can be stored in the same database as its base table(s) or in a different database. 

Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

MDX Examples

Hi All,
I found some examples of  MDX queries based on Adventure Works cube.Please find the link
Thanks,
Maruthi...