Don't Always Blame The Data

Just a quick post on this error I received yesterday. 

While I was working at a client, the application manager made some changes to a Slowly Changing Dimension of type 2 with effective dates, as mentioned here. This dimension contains the organisational units from the client.

A little while later a part of the financial report I made, was broken. After executing the report (or actually the procedure of the dataset in SSMS) it gave me the following error message:

The maximum recursion 100 has been exhausted before statement completion.

Although this answer on SO mentions the MAXRECURSION option at the end of the CTE, that doesn't solve my problem. The poster mentioned he had a circular reference in his data, what could cause my report to break also.

After investigation of the dimension I couldn't find any problems so I was back at the report and looked at the procedure of the dataset. I started executing parts of the dataset and it turned out to be a code issue instead of a data issue:
The anchor part of the recursive CTE that creates a hierarchical tree of the organisation didn't take into account the effective dates of the SCD and therefore creating a circular reference between two versions of an organisational unit. It kept on trying to build the tree before failing until the 100th recursion, as mentioned in the error message.
My solution was to add the effective dates of the SCD to the CTE and after that the procedure and the report ran fine again. Everyone happy :)

NetFx3 Error While Installing SQL Server 2012 SP1 on Windows 8 VM

A while ago I was looking for a VM with SQL 2012 installed on it (and all the other BIDS and pieces :) because I was about to start learning for my MCSE and needed some serious SSAS training. Because I couldn't find one I decided to create one myself from a VM with Windows 8 on it. I did run into some troubles however..

I was going to install all the features of SQL I could possibly need, so also MDS, DQS, IS, RS, etc. After configuring everything the install started, but I rather quickly hit the first bump:

It did continue after that, but only SSDT was successfully installed as you can see.

Apparently, the install couldn't automatically enable the .NET Framework 3.5, so let's do it manually following the next screen shots.

Windows will download the files if an internet connection is present.

But unfortunately.. The install fails once again. No idea why..

Nothing left than downloading and installing it myself from the Download Center.

And finally, it installed successfully!

Now back to the SQLServer install and my study! :)

Rule "Same architecture installation" Failed When Installing 64-bit SSDT for Visual Studio 2012

Just a quick post on an error I encountered when installing SQLServer Data Tools - Business Intelligence (SSDT-BI) for Visual Studio 2012.

The installation wouldn't even start because of a failing rule check:

Rule "Same architecture installation" failed
The CPU architecture of installing feature(s) is different than the instance specified. To continue, add features to this instance with the same architecture.

Make sure you select “New instance” on the installation dialog if you have SQL Server x64 installed. Otherwise, you probably get the error stated above!

MCSA SQL Server 2012 and Best Wishes

I can finally announce that this Wednesday, just before Christmas, I nailed the 70-463 exam. About 3 weeks ago I already passed 70-462, so I am now qualified as MCSA SQL Server 2012, whohoo! :-)

And now on to my MCSE BI.. But not before 2015 starts.. Let's take a few days of family time to relax and start the learning experience again in January!

I wish everybody a merry and joyful Christmas (yes, we have 2 of them days over here :) and see or speak to you in the new year!

Book Review: Reporting with Microsoft SQL Server 2012

I have just finished reading this book (Reporting with Microsoft SQL Server 2012) and wanted to let you know my opinion.

I believe the book is a very good starting point for beginners in SSRS or Power View. I also got some gotcha's out of it, so there may be interesting points for more experienced users.

Overall I feel the content described is correct (some minor typos aside), thorough and easy to read, but it is on a pretty basic level. So if you have (almost) never worked with Reporting Services I believe it is worthwhile reading the chapters on SSRS, but if you're already a(n) (more) experienced user, I don't believe this will add very much to your knowledge.

While chapter 2 also covers new features in SQL 2012 (like data alerts) and could therefore be of use for experienced users, chapter 3 will not cover many new things in my opinion.

Because Power View is a much younger product than SSRS, this book might also be a good starting point. It covers the differences between SharePoint and Excel as a starting point for a Power View report very well. It also let's you create a basic report and publish this to SharePoint.

All in all a good and easy read on the subject.

70-461

You could say: -391. Because that's what 70 minus 461 is.. :)

But I actually meant to say 70-461: Querying Microsoft SQL Server 2012, that's the exam I finally did last friday and succeeded with a score of 981!

Now up to 70-462 for my MCSA.

SQL03070: This statement is not recognized in this context

I just ran into the following problem:
when trying to build a database project in my VS2010 solution I got this error:

SQL03070: This statement is not recognized in this context.

After searching the first hit in Google already solved the problem:
The reason for this error is the following:

The project system expects the stored procedure as a create statement only. The additional statements to drop if existing are not necessary within the project system. Project deployment takes care of detecting if the sproc already exists and if it needs to be updated.

To resolve this error

• Simply remove the additional statements other then your create SP, Function etc.

OR

• Exclude the file from build. Right Click on your file in Solution Explorer, Click Properties > Build Action > Not in Build

Credits to: Prashant Acharya.

Database [databasename] cannot be upgraded because it is read-only or has read-only files

Today I was studying for my MCSA SQL Server 2012, for 70-461 (Querying SQL Server 2012) and I ran into an issue while going through Chapter 6: Querying Full-Text Data, Lesson 1, Excercise 2.

For this exercise I installed Full-Text Search on SQL Server 2012 and later I needed to make sure the Semantic Language Statistics Database was installed. This was not the case, so I tried to follow the necessary steps.

While I was trying to attach the database with the beneath script I was getting an error message of the following type:

CREATE DATABASE semanticsdb ON
(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb.mdf'),
(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb_log.ldf')
FOR ATTACH;

Msg 3415, Level 16, State 2, Line 1Database 'semanticsdb' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

As the error message was saying, I checked the permissions of the database files and ensured they were not read-only. Also, the service user account running the SQL Server instance had full access on the files..

I was about to give Everyone full access to the folder when it struck me: I tried starting the SQL Server Management Studio in Administrator mode. When I executed the script again I got a lovely message saying:

Converting database 'semanticsdb' from version 693 to the current version 706.Database 'semanticsdb' running the upgrade step from version 693 to version 694.Database 'semanticsdb' running the upgrade step...

Worked like a charm for me!

Power View for Multidimensional Models - Preview

Yesterday the public CTP on Power View for Multidimensional has been released by Microsoft!

In SQL Server 2012 RTM/SP1, Power View can consume data from tabular models, however multidimensional models (a.k.a. “cubes”) were not supported. The Power View for Multidimensional Models CTP addresses this key limitation.  Now Power View users can connect to both tabular BI Semantic Model AND multidimensional BI Semantic Model (cubes).  This is achieved through native support for Data Analysis Expressions (DAX) in Analysis Services Multidimensional models, ensuring optimal performance and functionality.

The SQL Server BI team blogged about it here. You can also directly download the Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP.

If you have any issues or suggestions you can provide feedback via MS Connect.

SQL Server Days 2012

In a few weeks I will be attending SQL Server Days 2012 in Schelle, Belgium.

I already saw lots of good sessions and speakers pass by in the agenda: Jen Stirrup on Mobile BI, Chriss Webb on SSAS, Koen Verbeeck on CDC and SSIS..

Hopefully I can meet up with some new SQL-friends and add some to my SQLFamily :)

See you @ SQL Server Days.

MSTechEd Europe 2012 Day 3: BI and Data Visualization

I finally had some time to work out some of the stuff from the MSTeched Europe from last week.
I have a few other sessions I'd like to share and see also on Channel 9, so you'll see some more posts coming soon hopefully!

This session given by Jen Stirrup was about BI and data visualizations, mostly about PowerPivot (download) and Power View.

The session starts out with a more theoretic (Jen's) view of Business Intelligence and how visualizations determine the first impression of humans.

The comparison is made between Traditional BI vs New End User BI, where BI is about people first and the technology is an enabler. Business users have to embrace the BI solutions being build, otherwise there's no feeling with the system build and it will not be adopted within the business.
Traditional BI is difficult for IT and also takes a long time to build where End User BI is more flexible and mashups are possible.

This is a spreadsheet to manage my spreadsheets!

Starting with a quick look at PowerPivot: it gives users the power to create compelling self-service BI solutions using a familiar Excel Interface. It can be used for mashups, has a high performance due to the xVelocity in memory analytics engine (a.k.a. VertiPaq) and has sophisticated Time Intelligence functions.

Business puzzles vs Business mysteries
Excel (PowerPivot) is used  for business puzzles: the business has a very well defined question and a well defined answer. Compared to Power View: we have an ill defined business question and an ill defined answer which make up the business mysteries.

The prerequisites for Power View are
- SQL Server 2012
- SharePoint (Enterprise) 2010 SP1
There is supposed to be no typing in Power View (except for the name of the charts), so it's all about dragging, dropping, slicing and brushing and filtering data.

Data visualization is about people:
- preattentive processing: we see lines and colors, in 2D
- visual integration: we see faces
- cognitive integration: use context and world knowledge and look back top-down
People tend to look at the top right corner of a page or chart first.
The Line, Bar and Scatter chart are best used when we take into account the above theories. This is why Jen doesn't really like Pie charts because they use area and angles, both are not well read.

Andy Caddy pointed out the following pie chart to Jen last week!

Something that also stood by me was the comparison made with Kylie Minoque when looking at pie charts. In "Can't get you out of my head" she nodges here head to left and right, that's what people tend to do when looking at pie charts when the slice is not starting at 12 o'clock in the pie chart.

Power View has the possibility to export your page to PowerPoint and interact with that page inside the presentation. An instance of Power View is loaded, therefore a connection to SharePoint is also needed, and you can slice and dice in the same way as in Power View.

I also had the honour to speak to her in person after her session. Nice meeting you Jen!

You can watch the full session at Channel 9 here.

MSTechEd Europe 2012 Pre-Con: SQL Server Performance Tuning and Optimization - It all depends...

Yesterday I was at the Pre-conference Day at the MSTechEd Europe. I learned a lot during the session and tried to make some useful notes so I can use them for later reference. Specially the part on DMVs was very useful, I didn't use them before and certainly am checking them in the next weeks!

By Denny Cherry and Thomas LaRock

1. Identifying performance bottlenecks

Tools used:

- Performance Monitor

- Activity Monitor

- DB Tuning Advisor (DTA)

- Data collection

- Profiler

- SQLTrace

- Dynamic Management Views (DMVs)

- Extended Events (xEvents)

Blocking processes / wait events (running, runnable, waiting) aren't shown in Profiler (= SQLTrace + visualization). Those can be shown with the xEvents Wizard under Management in SSMS. Always tune it to your needs, never use a default (See PAL).

Summary:

- Define the problem

- Decide for tool with the best metrics and least (perf.) cost

- Consider the time needed to analyze

- It depends..

2. Index optimization

1. Indexing

- Started at design time

- Data types matter!

- Changes over time

- First line of defense for (quick) perf. Tuning

Do we have the right indexes?

- It depends..

- Follow best practices

- Use DMVs for missing indexes

- Focus on Logical IO (Inside SQL Server 2005: T-SQL Querying by Itzik Ben-Gan)

SQL Diagramming > table with highest selectivity of data

Index seek is better than Index scan.

If an index has only writes and no reads > delete the index

2. Execution Plans

Look for:

- Correlation with wait events

- Examine first operator (Select | Update | ..)

- Warnings (missing index)

- "More costly"  operators: LIKE > Spool uses tempdb

- "Fat fingers": thick arrows means lots of rows

- Looking at the #rows in

□ An index > update statistics

□ Hash match > input vs output rows (do nothing)

3. Filtered Indexes

An index with a filter (where clause), but no extended logic (e.g. OR)

4. ColumnStore Indexes

- Based on the xVelocity in-memory analytics engine (a.k.a. Vertipaq)

- CREATE COLUMNSTORE INDEX [COLUMNS]

- Downside: the table is read-only, workarounds:

□ Delete index > load data > create index

□ Use 2 tables, 1 read-only and 1 read/write table

3. Storage optimization

1. Table partitioning

- Reduce maintenance window

- Improve query response time

- Reduce recovery time

Create filegroups, a partition function and a scheme referencing the function.

Business (logic) knowledge is essential before starting partitioning!

When moving data: meta data (page headers) is updated

When deleting data: 

- Move partition to another table > then truncate (faster and save)

- Also getting data back that shouldn't be deleted is faster. Let the data stay in the other table for a few days before actually deleting it.

2. AlwaysOn (Enterprise feature)

- No need for shared storage

- Offload backups

- Scaling out read-only activity

4. Troubleshooting

- Are all queries running slow?

- Use xEvents with DMVs

- Is a subset of queries running slow?

- Use Executions plans with DMVs

Useful DMVs:

- Sys.dm_exec_query_stats

- Sys.dm_exec_query_plan

- Sys.dm_exec_cached_plans

STATISTICS IO ON

STATISTICS TIME ON

Using GO 10 after a SQL statement will execute the statement 10 times, useful when inserting data.

Resources mentioned can be found @ http://mrdenny.com/res/teched-2012-precon

Free E-Book Gallery for Microsoft Technologies

Just a quick post to share some nice E-books found in a TechNet Wiki article by Monica Rush!
A few days ago I came across this link where you can find existing and new content for ASP.NET, Office, SQL Server, Windows Azure, and other Microsoft technologies in e-book formats.

The gallery can be found here.

For anyone who's interested in an overview of SQL Server 2012, I can also recommend the Books Online for SQL Server 2012 on MSDN.

Enjoy reading!

Microsoft TechEd Europe 2012

I'm proud to announce I am one of the already 2.5k+ attendees at the TechEd Europe in Amsterdam next month!

I allready put up my agenda at europe.msteched.com, to attend at least some of the sessions of the following speakers:

- Kasper de Jonge

- Marco Russo

- Alberto Ferrari

- Jen Stirrup

See you all at the TechEd!