ColumnStore Index - SQL 2012 New features

Summary:

The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.

Download "Column Store Index" white paper

Note: above content is copied from the White paper discussed in this post

FASMI

Today I Came across the Term Called “FASMI”, when I google about this term, I found following answers.

FASMI is an Alternative Term for OLAP, an Each Character of term holds definition which are listed below:

Fast Analysis (FA) – is defined in the OLAP scenario in five seconds or less.

Shared (S) – Must support access to data by many users in the factors of Sensitivity and Write Backs.

Multidimensional (M) – The data inside the OLAP Database must be multidimensional structure.

Information (I) – The OLAP database must support large volumes of data.

This term is invented by “Nigel Pendse” who is a BI and OLAP analyst and the editor of The BI Verdict (formerly The OLAP Report) and author of The BI Survey (previously known as The OLAP Survey).

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

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)