SQL Server 2012: Installing on a Virtual Machine

Posted on April 6, 2012 by

Now that SQL Server 2012 RTM is out, I have an update to my blog post SQL Server 2012 (“Denali”): Installing on a Virtual Machine, which was for CTP3.  Here is the version for the RTM:

The steps below are what I took to create a virtual machine to install all the products needed to create an environment where I can play with everything.  This environment included: Windows Server 2008 R2 Enterprise x64, SQL Server 2012 Enterprise x64 RTM, Sharepoint 2010, PerformancePoint, Power View, PowerPivot, SSAS, SSIS, SSRS (Native Mode and SharePoint Mode), DQS, MDM, Report Builder, and SSDT:

  1. The first thing I did was follow Jonathan Kehayias’s post Building a Completely Free Playground for SQL Server – (1 of 3) – Downloading the Software and Setting up a Windows Server 2008R2 VM Template to create a template, or base, VM using Windows Server 2008 R2 Enterprise, 64-bit (you must use 64-bit for SharePoint 2010).  Use VirtualBox for your VM, since Microsoft Virtual PC does not support 64-bit.  Make sure to change the computer name to something other than the default, since the default name is usually hard to remember
  2. Next I cloned the template VM from step 1 to create another VM and made it a domain controller.  Then I followed the post Step By Step Guide for Windows Server 2008 Domain Controller and DNS Server Setup (or check out this video) to set up the VM as a domain controller, which is required by SharePoint 2010 for these reasons:
    1. A PowerPivot installation integrated with SharePoint requires the SSAS PowerPivot service account to be a domain user that can manage the installation through Central Administration
    2. I have always found that it is significantly easier to setup all of the BI services if there is a separate domain account for each core group of services.  This makes it easy to manage the accounts and ensure that the services remain isolated from each other
  3. Follow the steps in SQL Server 2012 (RC0) Business Intelligence Configuration.   A couple notes: When installing SharePoint (step #3), on the SharePoint install first choose the “Install software prerequisites”.  When he mentions to do “PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures” in step #3, instead wait to do this after installing PowerPivot (step #5)
  4. After the SharePoint install, this error appeared when running the Sharepoint 2010 Central Administration: “This computer does not have the correct version of the Analysis Services OLE DB provider.  To support connections to PowerPivot data from this computer, download and install the SQL Server 2012 version of the provider: http://www.microsoft.com/download/en/details.aspx?id=29065 (Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2012)”.  When I installed this provider the error went away
  5. Then go to Install Reporting Services SharePoint Mode as a Single Server Farm and start with “Install and Start the Reporting Services SharePoint Service” and complete that section and the following sections.  For details on how to create a report see Create SSRS Report with SharePoint 2010 Integrated Mode
  6. I then used my Office 2010 64-bit image to install Excel 2010 and Office Shared Features (it installs the 32-bit version by default, so you must go to the x64 directory and run setup.exe from there)
  7. Then I followed the post PowerPivot for Excel Installation to install the download Microsoft SQL Server 2012 PowerPivot for Microsoft Exce® 2010
  8. I installed SSDT.  I had thought I installed this when I went thru the SQL Server 2012 installation process since it was an option on the Features Selection menu that I selected.  However, this does not install the full SSDT product (it’s missing the database project piece), but only a pointer to it that makes SSDT visible when you create a project in Visual Studio 2010.  So to fully install SSDT, follow Install SQL Server Data Tools.  Yes this is very confusing and I blogged about it at SSDT – Installation confusion
  9. Install SSDT Power Tools
  10. I then ran Windows Update and installed the many patches
  11. Then I installed the SQL Server sample databases: Microsoft Contoso BI Demo Dataset for Retail Industry and Adventure Works for SQL Server 2012
  12. Next up I installed Master Data Services (MDM), and then the Microsoft SQL Server 2012 Master Data Services Add-in For Microsoft Excel
  13. Then I installed Data Quality Service (DQS) via the video How Do I: Install and Configure DQS
  14. Next was installing the Microsoft® SQL Server® 2012 Performance Dashboard Reports
  15. Then I installed Microsoft SQL Server 2012 Report Builder
  16. Finally I created a business intelligence center site in SharePoint Server 2010, created and configured a PerformancePoint Services service application, and configured the unattended service account for PerformancePoint Services
  17. Step #3 above installed SSRS in SharePoint Mode.  Note that you can have both SharePoint Mode and Native Mode installed.  To install Native Mode, see Create a Native Mode Report Server Database (Reporting Services)

More info:

How to Build a SQL Server 2012 Hyper-V Virtual Machine (KIWI build)

SQL Server BareMetal Workshop series – part 1: How to build an installation VHD file that includes all software needed to start with SQL Server 2012 Database and Business Intelligence

Build your own SQL2012 Demo Machine Part 1 – Preparation & Summary

Posted in SQL Server 2012, SQLServerPedia Syndication | 13 Comments

SQL Server 2012: Multidimensional vs tabular

Posted on April 4, 2012 by

To expand on what I talked about in SQL Server 2012 (“Denali”): Details on the next version of SSAS, there is a new data model called Tabular.  So when you are creating a new project, which model should you use, Multidimensional vs Tabular?  Well, there is no clear-cut answer, but there are some factors that can make you choose one over the other:

  • If you want to use DAX, you have to use Tabular
  • If you want to use Power View, you have to use Tabular
  • If your dataset is extremely large, go with Multidimensional
  • If you need writeback support, you have to use Multidimensional
  • If you need access to many different external data sources, choose Tabular
  • If you need complex calculations, scoping, and named sets, choose Multidimensional
  • If you need extreme speed and consistently fast query time, choose Tabular
  • If you need Many-to-Many relationships, choose Multidimensional (can be done in Tabular but difficult)
  • If your solution requires complex modeling, choose Multidimensional
  • If you need any of the following features, you must use Multidimensional: Actions, Custom Assemblies, Custom Rollups, Custom Drillthrough Actions (but BIDS Helper adds support), Linked objects, or Translations

I would recommend going with Tabular if possible, as it is better to use for these reasons:

  • It uses DAX, which is much easier to use than MDX, and least for the basics (but mastering DAX and optimizing DAX is hard)
  • It uses Vertipaq, which is much faster than Multidimensional
  • It uses your existing relational model, so there is usually no need to create a star schema (which usually means using ETL to create new dimension and fact tables in a DataMart).  Complex DAX may require a star schema
  • It is faster to develop
  • It’s less expensive to use in terms of time, resources and skill requirement
  • You can extend the data model without reprocessing the whole database by using calculated columns.  Instead it requires a much faster “Process Recalc”
  • In situations where a multidimensional model requires the use of snapshots (i.e. quantity totals by day), tabular is better because it can avoid snapshots by making up-to-date calculations at query time (thanks to its speed because the data is in memory)
  • In situations where a multidimensional model requires a distinct count (i.e. how many new distinct customers this month), tabular is better because it stores data in a way that discount count is very fast (writing a measure vs changing the data model and reprocessing the data)

Keep in mind the option on creating both types of models against the same data warehouse.  Say you built a multidimensional model before tabular was released, but now want to use Power View.  You can always just build a tabular cube to allow for the use of Power View.

You can also use the tabular model to prototype in since it’s so much easier and quicker to use, and then decide later whether to convert it to a full-blown multidimensional model.  And because of its ease of use, the tabular model could also be used by power business users to prototype a cube in, and then later IT can take that model and enhance it in tabular or convert it to multidimensional (self-service BI at it’s best).

More info:

Why to use #Tabular in Analysis Services 2012 #ssas

Video Vertipaq vs OLAP: Change Your Data Modeling Approach

Decisions: PowerPivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012

Observations on interoperability between BISM Tabular and OLAP clients

Feature by Server Mode or Solution Type (SSAS)

So what is the Business Intelligence Semantic Model or BISM really?

Multi-dimensional or Tabular? Which model to use?

Creating Your First Tabular Model (part 1)

Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

Posted in SQL Server 2012, SQLServerPedia Syndication, SSAS | 7 Comments

SQL Server 2012: SSAS enhancements

Posted on April 2, 2012 by

My previous blog, SQL Server 2012 (“Denali”): Details on the next version of SSAS, talked about the major new feature in SSAS, which is BISM.  But what other changes are there?  Well, not much.  Here are details on the few changes I have found:

  • All development work is now done in Visual Studio 2010, with BIDS replaced by SQL Server Data Tools (SSDT)
  • String stores in dimensions attributes or partitions has a 4 GB maximum file size per store (strings are stored separately from numeric data to allow for optimizations based on characteristics of the data).   If your dimensions or partitions include string stores of this size, you can work around the file size constraint by changing the string storage architecture to the new “scalable string storage”.   The maximum limit for scalable string storage is 4 billion unique strings or 4 billion records, whichever occurs first
  • Queries that include DistinctCount in a ROLAP process can run faster, assuming certain criteria are met
  • NUMA is now supported, allowing for more than 64 processors
  • You can now use SQL Server Extended Events (XEvents) to monitor Analysis Services
  • New Profiler events: Resource Usage (you can capture the number of reads or writes, reads or writes as measured in kilobytes, CPU time in milliseconds, rows scanned, and rows returned)
  • New Trace events:  Locks Acquired, Locks Released, and Locks Waiting
  • New Analysis Services PowerShell provider and cmdlet

More info:

What’s new in Analysis Services 2012 Multidimensional?

[SQLServer 2012] Analysis Services New Feature : Scalable String Storage

Analysis Services Thread Pool Changes in SQL Server 2012

Analysis Services 2012 Faster Processing

Analysis Services PowerShell Provider (SQLAS) in SQL Server 2012

Posted in SQL Server 2012, SQLServerPedia Syndication, SSAS | Leave a comment

BIDS Helper 1.6 for SQL Server 2012 released

Posted on March 30, 2012 by

BIDS helper is Visual Studio add-in with features that extend and enhance the functionality of the SQL Server 2005 and SQL Server 2008 BI Development Studio, and now supports SQL Server 2012 (including tabular mode) and Visual Studio 2010 (SSDT).  Version 1.6 can be downloaded here, and read up on the new features.  Note this version is still in beta.

It’s a must-have add-in with tons of features.  I find myself using at least one of the features every day.  The documentation is very detailed, so I encourage you to review all the features and I am certain you will find one’s that you can use right away.

Posted in SQLServerPedia Syndication, SSAS, SSIS, SSRS, Tools, Visual Studio 2008, Visual Studio 2010 | Leave a comment

TinyInt problem in SSAS

Posted on March 28, 2012 by

If you have a source table that has a Tinyint primary key column defined as IDENTITY and you create a Data Source View (DSV) in SSAS and add that table (usually a dimension table), that IDENTITY field will be incorrectly typed as System.int32.

This causes problems when attempting to define FK relationships between this column and related FK columns in other tables in the DSV.  If you try to define a relationship, you will get the message “The <source> column and the <destination> column have different data types”.  It will also not automatically create an relationship when using the Data Source View Wizard.

Note that if the column is not defined as an IDENTITY column it is correctly represented with a type of System.Byte in the Data Source View.

As a workaround, you can create a view or named query for each table in which you explicitly convert your tinyint field to tinyint in the view, and then refresh the DSV, the column type will correctly be recognized as System.Byte in BIDS.  For example, “SELECT Cast(KeyID as Tinyint) as KeyID”.  You can then create the relationship.

Another solution is to just change the data types in your source table to int.

I have found two Connect items about this: Data Source View incorrectly types a Tinyint Identity column as int32 and Data Source View does not support PK with tinyint type, but it has not been fixed yet.

More info:

Using TinyInt as keys in Analysis Services

Posted in SQLServerPedia Syndication, SSAS | Leave a comment

SQL Server Parallel Data Warehouse (PDW) Appliance Update 3 (AU3)

Posted on March 26, 2012 by

In the next few weeks there will be an update to the SQL Server Parallel Data Warehouse (PDW) called “Appliance Update 3″ or “AU3″.  More than just a cumulative update, an Appliance Update is a complete refresh on the entire solution including software, hardware, and firmware.  This is the third Appliance Update in the last 14 months and includes the following major new features:

  • Breakthrough performance with up to 10x improvement over prior releases through the new cost-based Query Optimizer and Data Movement Service
  • Ability to start small and scale with full and half-rack appliances; Dell has released a half-rack appliance and the HP half-rack is available in April
  • Best-in-class manageability with integration to System Center 2012 enabling IT to manage PDW seamlessly with other IT assets using a single pane of glass

PDW is a massively parallel processing (MPP) pre-built appliance designed to provide the highest scalability and performance for the most demanding data warehouse needs.  PDW appliances are available from HP (as HP Enterprise Data Warehouse Appliance) and Dell (as Dell Parallel Data Warehouse appliance) to scale well beyond hundreds of terabytes.  Check here for more info.

Posted in Appliance, SQLServerPedia Syndication | 4 Comments

Dell Quickstart Data Warehouse Appliance for SQL Server 2012

Posted on March 23, 2012 by

As a follow-up to my blog Microsoft SQL Server Reference Architecture and Appliances, Dell recently announced the future availability of Dell Quickstart Data Warehouse Appliance based on SQL Server 2012.  Right now only HP has a Business Data Warehouse appliance (BDW).  Basically these are pre-built appliances for data warehouses up to 5 TB based which are Fast Track Data Warehouse compliant.  HP is based on Fast Track 3.0 and Dell will be based on the upcoming Fast Track 2012.  HP has SQL Server 2012 Fast Track Data Warehouse candidate reference architectures with plans to expand the portfolio of SQL Server 2012 configurations in the future.

It’s good to see other companies besides HP creating these appliances, and hopefully others will follow suite,

Posted in Appliance, SQLServerPedia Syndication | Leave a comment

Fast Track for SQL Server 2012

Posted on March 21, 2012 by

When the Fast Track Data Warehouse 3.0 Reference Guide was published, it was based off of SQL Server 2008 R2.  With the release of SQL Server 2012, as expected that reference guide has been updated.  While an official release has not happened yet, there are some details that were made public:

  • There is no new hardware requirements in the configuration or spec.  It’s a simple software upgrade to SQL Server 2012
  • It has been optimized for ColumnStore Index (CSI)
  • For the scenario of a straight upgrade, there is no change in throughput
  • For the scenario of using a ColumnStore Index, there is a 2x Fast Track rated throughput increase over SQL Server 2008 R2 benchmarks using SQL 2012 CI (and would have been better if more RAM was added to machine)
  • There is now support of solid state storage.  Partners developing offerings include HP, Dell, EMC, Cisco, IBM, XIO, Nimbus, and Bull.  Selected partners will offer new reference architectures utilizing Solid State Devices
  • Support for AlwaysOn
  • Not sure if it will be called Fast Track Data 4.0 or Fast Track 2012, but the reference guide is in final production edit, and will be available in early April

More info:

Scalable Analytics & Data Warehousing: Fast Track Data Warehouse for SQL Server 2012 by Eric Kraemer at SQLServerLaunch

Posted in Fast Track, SQLServerPedia Syndication | Leave a comment

SQL Server 2012: xVelocity

Posted on March 19, 2012 by

After SQL Server 2012 was released this past week, I saw a few mentions from Microsoft about a new feature in SQL Server 2012 called “xVelocity”.  I have been using SQL Server 2012 for months now, playing with all the new features, and never came across one called xVelocity.  How did I miss it?  Or did Microsoft add this feature after RC0 and kept it secret?

Turns out, 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.

Here is a blog post from Microsoft that further explains the change:

Along with the SQL Server 2012 launch, Microsoft announced xVelocity in-memory technologies, delivering huge performance improvements for data warehousing and business intelligence.  The purpose of this blog post is to clarify what xVelocity means for business intelligence and Analysis Services in particular.

In SQL Server 2008 R2, Microsoft released PowerPivot for Excel which introduced an in-memory analytics engine called VertiPaq.  Vertipaq enables Excel to process hundreds of millions of rows with sub-second response times on desktop hardware.  This engine uses in-memory column-oriented storage and innovative compression techniques to achieve these remarkable results.

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.  Customers who previewed SQL Server 2012 have also experienced compression ratios in excess of 100 times.

In SQL Server 2012, we are re-branding the Vertipaq engine that runs inside PowerPivot and Analysis Services to xVelocity in-memory analytics engine.  This engine is part of the xVelocity family that represents the next generation performance improvements across SQL Server.  We will stop using the Vertipaq name and transition the product, documentation and other collateral to use the xVelocity name.

More info:

No more Vertipaq, it’s now called xVelocity in-memory technologies

Posted in SQL Server 2012, SQLServerPedia Syndication | Leave a comment

SQL Server 2012: New Certification Info

Posted on March 16, 2012 by

As a follow-up to my posts SQL Server “Denali”: New Certifications and SQL Server 2012: New Certifications, there are now final details coming out about the SQL Server 2012 certifications.  The major news:

  • UPDATE: Anyone can take the beta exams for free.  Just click here
  • Two new Professional-level certifications will be introduced for SQL Server: the Data Platform certification and the Business Intelligence certification
  • The database developer and database administrator certifications were merged into an overall Data Platform certification
  • To achieve a Professional-level certification you must successfully pass five exams
  • The beta versions of these exams are scheduled to release in late March 2012 (see Register for SQL Server 2012 Beta Exam).  Beta exams use the identifier 71-xxx instead of 70-xxx. NOTE: You can signup to take the beta exams now at Prometric.   If you pass a beta exam, the exam credit will be added to your transcript, and you will not need to take the exam in its released form
  • The certifications will be available in June 2012
  • To upgrade from a Professional-level SQL Server 2008 certification (MCITP) to a Professional-level SQL Server 2012 certification, you must successfully pass three exams, rather than five.  More information will be provided on upgrade paths in mid-April
  • Each professional-level certification will require recertification every three years.  For more info, check out the frequently asked questions about recertification
  • There is not a product version in the certification titles due to the recertification requirement
  • The first exam for the new SQL Server 2012 Master-level certification is scheduled to be available in beta version in June.  More information will be available in mid-April
  • There is a new book series called Exam Reference, which will map directly to the objectives on the exam. Some exams will have Training Kits and others will have Exam References

The seven exams for SQL Server 2012 are:

  • Querying Microsoft SQL Server 2012 (70-461) [BI and DP] (info) (course)
  • Administering Microsoft SQL Server 2012 Databases (70-462) [BI and DP] (info) (course)
  • Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463) [BI and DP] (info) (course)
  • Developing Microsoft SQL Server 2012 Databases (70-464) [DP] (info) (course)
  • Designing Database Solutions for Microsoft SQL Server 2012 (70-465) [DP] (info)
  • Implementing Data Models and Reports with Microsoft SQL Server 2012 (70-466) [BI] (info) (course)
  • Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467) [BI] (info)

I have indicated which certification (“Business Intelligence” [BI] or “Data Platform” [DP]) each exam fits into).

So if you have the three MCITP certifications (Business Intelligence Developer, Database Administrator 2008, and Database Developer 2008) and want to get both the Business Intelligence certification and the Data Platform certification, you will need to take four upgrade exams.

More info:

Learning resources from Microsoft

Frequently Asked Questions

Posted in Certification, SQL Server 2012, SQLServerPedia Syndication | 3 Comments