Wednesday, April 11, 2012

VertiPaq Engine Renamed as xVelocity in SQL Server 2012.

VertiPaq engine is currently only available to PowerPivot for Excel in SQL Server 2008 R2. After the amazing success of PowerPivot for Excel in the previous release of SQL Server 2008 R2 this engine moved to the server side with the same InMemory BI and compression and it’s called the VertiPaq Engine or the Analysis Services Tabular Mode. Data retrieval and calculations happen at a much faster rate as its entire database is in-memory.Vertipaq enables Excel to process hundreds of millions of rows with sub-second response times on desktop hardware.

Now Along with the SQL Server 2012 launch, Microsoft announced xVelocity in-memory technologies, delivering huge performance improvements for data warehousing and business intelligence. This engine uses in-memory column-oriented storage and innovative compression techniques to achieve these remarkable results.

xVelocity is not a new feature, but rather a renaming of an existing feature.  Vertipaq is the existing feature that will now be called xVelocity.  So the Vertipaq engine that is inside PowerPivot and Analysis Services 2012 Tabular is now called “xVelocity in-memory analytics engine”.  Also, “xVelocity” now refers to the column store index feature in the SQL Server 2012 relational database.

Columnar database is its driving principle So First Column bases storage as opposed to “row store” & Secound High level of compression, all in memory – RAM based. In SQL Server 2012, Microsoft has taken the same Vertipaq engine and integrated it into Analysis Services. The results have been staggering with scan rates up to 10s of billions of rows per second on typical server hardware. 

Using a combination of compression, algorithms enhanced for modern CPU/memory architecture, and highly parallel intra-query execution, xVelocity can speed up query execution times by 4x, 10x, and even 100x, simply by adding column store indexes to the fact tables of a star-schema data warehouse.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Thursday, April 5, 2012

BIDS No More Exist in SQL Server 2012.

Once you will installed SQL Server 2012 you could not find the BIDS to develop SSAS/SSIS/SSRS application. BIDS will replace with SSDT i.e. SQL Server Data Tools.

Comparison of SQL Server 2012 Vs SQL Server 2008 R2.

You can get more idea on below Screen shot how SQL Server 2012 looks compare with SQL Server 2008 R2 components.

SQL Server 2012 Components

  1. SQL Server Data Tools
  2. SQL Server Management Studio
  3. Analysis SErvices
  4. Configuration Tools
  5. Data Quality Services
  6. Documentation & Community
  7. Integration Services
  8. Master Data Services
  9. Performance Tools

So in SQL Server 2012 we have below four new components available.

  1. SQL Server Data Tools
  2. Data Quality Services
  3. Documentation & Community
  4. Master Data Services

Today will discuss more on SSDT i.e. SQL Server Data Tools.

SSDT (SQL Server Data Tools)

1-      Developing SSAS/SSIS/SSRS Projects.

2-      Developing Databases in Visual Studio like working in SQL Server Management studio.

Will discuss the how we can use SSDT one by one.

Developing SSAS/SSIS/SSRS Projects.

·         If you are looking for BIDS (Business Intelligence Development Studio) in SQL Server 2012, then you will not find it. It is replaced with SQL Server Data Tools (SSDT) in SQL Server 2012.

·         You can start the SSRS/SSAS/SSIS Projects as we created in BIDS in SQL Server 2008/R2.

·         SQL Server 2012 installs "SQL Server Data Tools" under the "SQL Server 2012" Start Menu option - this includes BIDS but not Database Projects.

·         In this use can create the SSAS Tabular Projects called as BISM.

Developing Databases in Visual Studio like working in SQL Server Management studio.

·         In this you can create new Database Design features with schema management etc in Visual Studio more like working in SQL Server Management studio.

·         The idea is that developers want to develop databases using similar methods to working with regular (i.e. .NET code) object within VS.  To that end, SSDT includes offline projects, object creation interfaces (i.e. for tables, views, etc…) schema snapshot and compare, database version targeting (for SQL Server 2005, 2008, 2012 and for SQL Azure) and refactoring with preview.

·         Database Projects installs "MICROSOFT SQL Server Data Tools" under the root of the Start Menu - this doesn't have BIDS functionality included if installed separately.

  1. Responsive SSDT'S GUI as compared to SQL Server Management Studio.
  2. Fast and reliable build of deployment scripts.
  3. Detection of isolated changes in the target database.
  4. Resolve references to other production and system databases like msdb & databases connected by a linked server.
  5. Support of migration scripts to enable data preservation and to add static data.
  6. Possibility of executing and deploying a single file.
  7. Comfortable T-SQL writing.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2