Visio Forward Engineer Addin

A while ago I came across this useful addin for Visio 2010 from Alberto Ferrari. Now I needed it again so I decided to make a quick post for anyone who isn't using this yet.
You can design your database model in Visio 2010 and export it to a SQL-script to create the databases.

It can be downloaded from codeplex at: http://forwardengineer.codeplex.com/

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 Day 1: Optimizing your BI Semantic Model for performance and scale

I joined my first session on tuesday by Dave Wickert. Here's my short version of the session.

VertiPaq is all about Performance, Performance, Performance!
The VertiPaq engine chooses query performance over processor performance.
What the VertiPaq engine does:

1. Encoding (per column): creates a dictionary with value or hash encoding (self-deciding)
2. VertiPaq storage
• dictionary
• column segment
• hierarchy
• relationships
• partitions
3. Processing
• Process Full
• Process Data
• Process Recalc (includes calculated columns)
4. Advanced Processing
• Process Defrag: updates the dictionary
• Parallel processing
• Error handling
5. Server Memory Map
• Databases
• Formula engine (DAX, MDX)
• VertiPaq caches
• Storage engine

Defaults that can be adjusted (be careful):
VertipaqMemoryLimit - 60%
TotalMemoryLimit - 80%
VertipaqPagingMode

MSTechEd Europe Keynote Day 2

We started day 2 with another keynote session from Antoine LeBlond with a focus on Windows 8.Antoine showed some cool demos of what Windows 8 is capable of, including a demo on a tablet and laptop. Features included were Swipe in, semantic (smart) zoom, quick cycling through apps (the old alt+tab).
A new feature for the laptop is that the touchpad on it also has smart touch capabilities! You can also use semantic zoom and swipe in on the touchpad for example!

Another cool thing was taking your work environment on a 32 GB USB-drive and run it on your desktop at home, where a reboot and login with BitLocker showed a secured and protected environment, locked away from the physical hard drives of the desktop. Then suddenly he unplugged the USB-device, you then have 60 seconds to plug it back in to not loose any data. The video he was playing just continued after a flick of a second.. Nicely done!

Further demoing Windows 8 development with Windows Runtime (WinRT).
Multiple monitor support has much improved. You can set different backgrounds or just stretch it over 2 monitors. The taskbar can also be adjusted, e.g. when you drag and drop an application over to the 2nd monitor the shortcut on the taskbar also moves with it.

More sessions later on today on SQL Server and Spatial Data and of course the famous BI Power Hour, can't wait to have seen that one!

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

Ready for MSTechEd Europe 2012

I'm getting ready for the TechEd in Amsterdam RAI next week. Registration is completed, the hotel is booked and I've build up my agenda with the most important sessions I certainly do not want to miss.

I've watched several videos on Channel 9 from the MSTechEd North America, those were all very promising for what's to come. Earlier I also wrote a summary of the Day 1 Keynote by Satya Nadella.

I hope I can update my blog next week with new and exciting things from the TechEd!

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!