If you are using the Tabular model in SSAS, it will use the xVelocity technology to load your entire database in memory (greatly compressing the database).  So what happens if your database is too big to fit in memory?  You will get this error when you process the model:

“The following system error occurred: Insufficient quota to complete the requested service.

Memory error: Allocation failure. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

The current operation was cancelled because another operation in the transaction failed.”

This happens because, by default, no paging to disk is allowed if the data is too big for the amount of available memory on the machine where the model resides.  To change this, go to the Analysis Server properties (by right clicking on the Tabular Model server) and change the property VertipaqPagingPolicy to 1 or 2:

• Zero (0) is the default.  No paging is allowed.  If memory is insufficient, processing fails with an out-of-memory error.  All Tabular data is locked in memory
• 1 enables paging to disk using the operating system page file (pagefile.sys).  Lock only hash dictionaries in memory, and allow Tabular data to exceed total physical memory
• 2 enables paging to disk using memory-mapped files.  Lock only hash dictionaries in memory, and allow Tabular data to exceed total physical memory

Another solution is to use the DirectQuery mode, which bypasses the in-memory model, so client applications query data directly at the source.  Then it does not matter how big the database is since you are not using any memory.  Of course the trade-off is much slower queries.

More info:

Memory Settings in Tabular Instances of Analysis Services

Memory Properties

Undo Bad Tabular Mode Analysis Server Properties

A big headache in SSAS, especially when dealing with a large cube, is making a change that when deployed, causes a cube to become unprocessed, forcing you to do a full process so the cube is usable.  Nothing is worse than making a change without realizing it will cause the need for a full process on all the partitions of a cube that will take many hours.

For example, a process full on a dimension will cause cube/measures group/related partitions to become unavailable/unprocessed.  Here are other actions that will make your cube unprocessed:

• New measure group
• New measure
• Edit measure aggregation method
• Edit dimension usage

Here are other actions that will make your dimension unprocessed (and therefore all cubes connected to the dimension become unprocessed):

• Add an attribute to a dimension
• Change the order by property of an attribute
• Edit the attribute relationship
• Add or delete dimension translation

So, make sure you understand which changes will cause an unprocessed state which makes the cube unusable so you can do it off-hours so you don’t end up getting angry phone calls that the cube is not working!

More info:

SSAS: Which change makes the cube/dimension unprocessed?

Processing Analysis Services Objects

Cube “Process State” changing to UnProcessed

I see a lot of confusion on what exactly is the difference between a data warehouse and a data mart.  The best definition that I have heard of a data warehouse is:

“A relational database schema which stores historical data and metadata from an operational system or systems, in such a way as to facilitate the reporting and analysis of the data, aggregated to various levels”.

Or more simply:

“A single organizational repository of enterprise wide data across many or all subject areas”.

Typical data warehouses have these characteristics:

• Holds multiple subject areas
• Holds very detailed information
• Works to integrate all data sources
• Does not necessarily use a dimensional model but feeds dimensional models.

On the other hand, a data mart is the access layer of the data warehouse environment that is used to get data out to the users.  The data mart is a subset of the data warehouse which is usually oriented to a specific business line or team.

According to the Inmon school of data warehousing, a dependent data mart is a logical subset (view) or a physical subset (extract) of a larger data warehouse, usually isolated for the need to have a special data model or schema (e.g., to restructure for OLAP).  One of the benefits of the new Tabular mode in SSAS is that you can build that on top of a data warehouse instead of a data mart, saving time by not having to build a data mart.

So in short, I like to think of a data warehouse as containing many subject areas, and a data mart as containing just one of those subject areas.

More info:

Data Mart vs Data Warehouse – The Great Debate

Data Warehouse Architecture – Kimball and Inmon methodologies

Data Mart Does Not Equal Data Warehouse

Data mart or data warehouse?

Data Warehouse – Data Mart

Data Warehouse vs Data Mart

I have previously blogged about Fast Track Data Warehouse, a reference configuration optimized for data warehousing (see Microsoft SQL Server Reference Architecture and Appliances).  That was version 3.0 of the reference configuration that was for SQL Server 2008 R2.  Just released is version 4.0 for SQL Server 2012 (download).

The following table provides a list of notable changes or updates:

More Info:

SQLAuthority News – Fast Track Data Warehouse Reference Guide for SQL Server 2012

I received this error the other day when trying to process a particular SSAS cube:

Error      18           File system error: The following file is corrupted: Physical file: file:///\\%3f\E:\SQL\OLAP\Data\Options”>\\?\E:\SQL\OLAP\Data\Options BI QA.0.db\Dim Date.0.dim\1.Year No.bstore. Logical file .                                0              0

I also could not process or browse any cubes at all, even cubes that had nothing to do with this cube.  All gave this same exact error with the same file name even if the cube I was trying to process used a different file name.

When I went to SSMS, connected to Analysis Services, and renamed the cube, then everything worked.  I then deleted the “Options BI QA” cube.

The reason for this error message is the cube file in the file system is corrupted.  You can’t fix the issue by just redeploying the cube.  When you try to deploy the cube, you will get the same error message and the deployment process will fail.

After I fixed this issue I did some research and found a forum post that had the same problem with a different solution:

1. Stop the Analysis Services service
2. Delete everything in the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\%YOUR_PROJECTNAME% folder
3. Restart the Analysis Services service
4. Connect to the Analysis Service using SQL Server Management Studio
5. Delete the Analysis Service database which you got the error
6. Re-deploy the SSAS project

Either solution will work, with the solution I came up with an easier to do, but maybe might not work in ever case so it’s good to have another option.

Thanks to everyone who attended my presentation “Scaling SQL Server to HUNDREDS of Terabytes” at Houston SQLSaturday.

Here is the PowerPoint presentation: Scaling SQL Server to HUNDREDS of Terabytes

Scaling SQL Server to HUNDREDS of Terabytes

Learn how SQL Server 2008 can scale to HUNDREDS of terabytes for BI solutions. This session will focus on Fast Track Solutions and Appliances, Reference Architectures, and Parallel Data Warehousing (PDW). Included will be performance numbers and lessons learned on the very first PDW implementation in the world and how a successful BI solution was built on top of it using SSAS.

Learn about all the different appliances and how they can save you a tremendous amount of time and money instead of building on your own: HP Business Decision Appliance (BDA), HP Business Data Warehouse appliance (BDW), HP Enterprise Data Warehouse Appliance, and HP Database Consolidation Appliance.

If you are involved in the decision making in your company for purchasing one or more servers to be used for SQL Server, this session will make you aware there are options outside of the usual ordering a server and internally installing the hardware, OS, and SQL Server.

As a follow-up to my SQL Server 2012: New Certification Info, Microsoft Learning has announced the certification tracks for the SQL Server 2012 exams.  There are new names for the certifications:

Your first step is to achieve Microsoft Certified Solutions Associate (MCSA).

If you are new to certification you must take the following three exams:

• Exam 70-461: Querying Microsoft SQL Server 2012
• Exam 70-462: Administering Microsoft SQL Server 2012 Databases
• Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

If you have an MCTS in SQL Server 2008 already you can take the following path

• A pass in any SQL Server 2008 Microsoft Certified Technology Specialist (MCTS) exam (70-448, 70-433, 70-432)
• Exam 70-457: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 1
• Exam 70-458: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 2

Once you have achieved you MCSA status you can then start for your Microsoft Certified Solutions Expert (MCSE) certification.

You have a choice to do the MCSE: Data Platform or the MCSE: Business Intelligence, or of course you could do both.

MCSE: Data Platform involves

• Obtain your SQL Server 2012 MCSA
• Exam 70-464: Developing Microsoft SQL Server 2012 Databases
• Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012

There is also an upgrade path

• A pass in Microsoft Certified IT Professional (MCITP) Database Administrator 2008 or Database Developer 2008 (70-450 or 70-451)
• Exam 70-457: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 1
• Exam 70-458: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 2
• Exam 70-459: Transition your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE:Data Platform

MCSE: Business Intelligence involves

• Obtain your SQL Server 2012 MCSA
• Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
• Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

The upgrade path involves:

• A pass in Microsoft Certified IT Professional (MCITP) Business Intelligence 2008 (70-452)
• Exam 70-457: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 1
• Exam 70-458: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 2
• Exam 70-460: Transition your MCITP: Business Intelligence Developer 2008 to MCSE: Business Intelligence

As a result if you want to achieve the MCSE in either Data Platform or Business Intelligence and you are starting from scratch there will be 5 exams to take.  If you have the ability to upgrade your certification because you have an MCITP already then it will be three upgrade exams.

Notice that these new certifications don’t have “SQL Server 2012” in their names.  The certifications are not versioned.  Instead, certifications may cover multiple versions of the product.

Upgrade exams are scheduled to be available by the end of August 2012.  There is currently no info on the subjects they will cover.

There is also now two Microsoft Certified Solutions Master (MCSM) certifications: MCSM: SQL Server and MCSM: Intelligence.  It is good to see BI get its own MSCM.  And there is still the Microsoft Certified Architect (MCA).  Details for these have not been released yet.

Full details and questions can be found at http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx

More info:

A New Era in Microsoft Certification

Microsoft’s Certifications Reinvented for the Cloud

Breaking News: Changes to Microsoft SQL Server Certifications

I will be speaking at the Houston SQLSaturday this upcoming Saturday (April 21st).  Below is info on my session, which is at 4pm.  I hope you can be there!

Scaling SQL Server to HUNDREDS of Terabytes

Learn how SQL Server 2008 can scale to HUNDREDS of terabytes for BI solutions. This session will focus on Fast Track Solutions and Appliances, Reference Architectures, and Parallel Data Warehousing (PDW). Included will be performance numbers and lessons learned on the very first PDW implementation in the world and how a successful BI solution was built on top of it using SSAS.

Session Level: Intermediate

SQL Server Data Tools (SSDT) is a Business Intelligence Development Studio (BIDS) replacement, meaning it has a suite of Visual Studio 2010 add-in tools for building Integration Services (SSIS), Analysis Services (SSAS) & Reporting Services (SSRS) solutions (let’s call that suite SSDT-BIDS).  It is also a suite of Visual Studio 2010 add-in tools for building SQL Server database solutions/projects (let’s call that suite SSDT-DB), a replacement for Visual Studio Database Developer edition (aka “Data Dude”).  It also includes features from SSMS (SQL Server Management Studio).  However, depending on how you go about installing SSDT, either SSDT-BIDS or SSDT-DB will not be installed.  I ran through multiple types of installations, and this is what I found out:

If you go thru the SQL Server 2012 installation and choose “SQL Server Data Tools” on the Feature Selection page, it will install SSDT-BIDS as well as the Visual Studio 2010 Integrated Shell (the Integrated Shell will only contain SSDT tools, and does not include VS programming languages and the features that support their respective project systems), it then applies SP1, but does NOT install SSDT-DB.   Instead of installing SSDT-DB it installs a pointer to a web install that you will see when you try to create a new database project in Visual Studio 2010.  So to install SSDT-DB, follow Install SQL Server Data Tools.  I did not realize I was missing the database project functionality until I could not find the SQL Server Object Explorer window in Visual Studio.  Then after wasting much time I realized I still needed to install SSDT-DB.

If you have not installed SQL Server 2012 (or you did install it but did not select  ”SQL Server Data Tools” on the Feature Selection page), and go to http://msdn.microsoft.com/en-us/data/gg427686 to download and install SSDT, it will automatically install the Visual Studio 2010 Integrated Shell, apply SP1, and install SSDT-DB, but does NOT install SSDT-BIDS.  If you then install SQL Server 2012 and choose “SQL Server Data Tools” on the Feature Selection page, it will install SSDT-BIDS.  It notices that you already installed the Visual Studio 2010 Integrated Shell and won’t install it again.

Note that when SSDT-BIDS is installed, it also installs the menu item “SQL Server Data Tools” under the “SQL Server 2012″ Start Menu option.  When SSDT-DB is installed, it also installs the menu item “Microsoft SQL Server Data Tools” under the root of the Start Menu.  Both of these menu items point to the same executable.

If you have Visual Studio 2010 Professional Edition or above installed before you perform an installation of SSDT through SQL server 2012 or on its own, then you must install SP1 manually before installing SSDT.  The SSDT installation will integrate all the functionalities of SSDT into your existing VS environment.

More info:

SSDT – What’s in a name?

I blogged previously that when you are a consultant or contractor, you can sometimes be faced with the decision when taking a new contract of whether to go 1099 or W-2 (Consultants: 1099 or W-2?).  However, some staffing firms or clients don’t do 1099, but instead do Corp-to-Corp (C2C).  In short, Corp-to-Corp means that your client, which is a corporation, pays your business, which is organized as a corporation, for the services rendered by you.  Your client may prefer this instead of 1099 as it protects them from the risks regarding the employer-employee relationship (even though you are paid via 1099, the IRS might still consider you an employee and disallow your independent contractor status.  See IRS Publication 1779: Independent Contractor or Employee? and Consultants, know how the IRS determines employee status.  If this were to happen, the company you work for would owe back payroll taxes, so some companies prefer Corp-to-Corp to avoid this situation).

I have done C2C a few times when I was subcontracting for a consulting company.  You might find clients that have a checklist similar to the one below that you must adhere to in order to do a C2C (some call it an “Independent Consultant” (IC) agreement):

• Is the subcontractor an independently established business registered with or incorporated in one of the United States (as opposed to merely an individual)? DOCUMENTATION REQUIRED IF ANSWER IS YES: Subcontractor must submit copy of Certificate of Incorporation.
• Does the subcontractor possess a Federal/Employer Tax I.D. Number (as opposed to only an individual’s social security number)? DOCUMENTATION REQUIRED IF ANSWER IS YES: Subcontractor must submit copy of formal notification of Tax ID number and completed W-9 Form (attached hereto).
• Does the subcontractor issue paychecks and W-2 Forms to its all personnel (as opposed to business checks and Form 1099s)?
• Does the subcontractor make payroll deductions for its personnel’s federal, state and local income taxes, FICA, FUTA, SUTA and required disability insurance (if any) from its personnel’s paychecks?
• Does the subcontractor file all required employment tax and payroll reports (such as IRS Form 940s and unemployment insurance contribution reports) with the appropriate agencies?
• Does the subcontractor obtain Form I-9s for its personnel? Does the subcontractor currently employ the individuals/candidates being presented for consideration with our clients?
• Does the subcontractor currently employ the individuals/candidates being presented for consideration with our clients?   DOCUMENTATION REQUIRED IF ANSWER IS YES: Subcontractor must provide proof of employment, i.e. visa if sponsored or other employment documentation.
• Does the subcontractor provide workers compensation coverage for its personnel? DOCUMENTATION REQUIRED IF ANSWER IS YES: Subcontractor must provide proof of coverage via a Certificate of Insurance issued to us.
• Does the subcontractor possess General Liability Insurance coverage?
• DOCUMENTATION REQUIRED IF ANSWER IS YES: Subcontractor must provide proof of coverage via Certificate of Insurance issued to us. (Min. coverage $300,000).  Note: Most require 1 million in GL coverage, which you can get for around $400-500/year.  I got my coverage from TechInsurance
• Does the subcontractor have workers compensation and auto insurance?
• Does the subcontractor provide similar services to companies other than us, i.e. is the subcontractor free to do business with anyone who may wish to contact it, even while its personnel are performing services for us?
• Does the subcontractor advertise in the Yellow Pages, internet postings (Internet-Monster, Dice, etc), local newspapers, trade publications, or other media?  If so, are the services marketed by the subcontractor the same as those the subcontractor will be providing us?

The major difference between C2C and 1099 is that with C2C, you don’t have to pay self-employment taxes on your income.  However, you do have to pay yourself a salary and with it both employer and employee taxes.

More Info:

Consultants: W-2, 1099 or Corp-to-Corp?