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.

Error on opening a database connection in SSMS

Yesterday I ran into an issue with SQL Server Management Studio (2012). I was using it on my laptop with Windows 7 installed.

Whenever I wanted to connect to a database I got the following pop-up and nothing could be done with the database I connected to. Opening databases or security also gave me the same pop-up with an error in Microsoft.VisualStudio.Platform.WindowManagement:

After a thorough search I came to an MSDN message that suggested the following:

• Try installing the following Microsoft Visual Studio 2010 Shell (Isolated) Redistributable Package
• Then apply SP1 for Visual Studio.

Installing the redistributable already failed with me so I searched through my recently installed programs to see what has changed the last days.. This monday I installed Vault Client for source control, and after uninstalling this the problem had disappeared. I still don't know why this was happening though..

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.

Power Pivot Field List vs. PivotTable Field List

Something strange happened this week when I was giving a PowerPivot training.
The PowerPivot Field list disappeared and the PivotTable Field List came back. This happened after a few minutes after I had made a PowerPivot table in a new document. It seemed to happen just in a moment, not after any particular action. Saving, closing and reopening the document solved the problem.

This triggered me to look at the autosave options. When you create a new document and autosave is on, make sure it saves it with the right extension. In this case, the format of autosave was on OpenDocument Spreadsheet (.ods). This caused the PowerPivot Field list to disappear and only the PivotTable field list was available.

This can be undone when the document type of autosave is on Excel Workbook.
The autosave options can be changed under File > Options > Save.

Power View in action on Politics

Kasper de Jonge had a blog post to a tweet to this nice video where PowerPivot and Power View in Excel 2013 are used to look at the political landscape of the Dutch parliament over the last 66 years. At september 12 we had elections in Holland, so this topic is quite hot now.

Please keep in mind the video is in Dutch, but I believe it is still worth watching if you don't understand dutch (Watch on YouTube).

More to IT than BI

Well, it's been a while and a whole lot has happened since.

For starters, Nienke and I had a great and lovely wedding! With this wonderful opening dance. We also had a great honeymoon with our sailboat to NP "De Biesbosch" in the Netherlands.

Also some nice things in BI-land have happened:

Office 2013 Preview was released with native PowerPivot and a PowerView add-in. Kasper wrote an excellent blog post about the ton of new features.

SQLServerDays 2012 will be held in San Marco Village, just south of Antwerpen. The agenda is already available with i.a. Marco Russo, Jennifer Stirrup, Chriss Webb and Koen Verbeeck.

Soon more things to follow!

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!

MS SQL DateTime convert examples

As I'm always searching the web with datetime conversions in SQL, I thought I'd post them here finally for reference. The function convert is used here, also found on MSDN.
The beneath functions are for string to datetime conversions.

For datetime to string conversions use varchar instead, e.g.:
SELECT convert(varchar, datetimeField, 105)

-- MSSQL Server string to date conversion - datetime string format sql server -- MSSQL string to datetime conversion - convert char to date sql server SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM) SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000 -- Without century (yy) string date conversion - convert string to datetime SELECT convert(datetime, 'Oct 23 12 11:01AM',     0) -- mon dd yy hh:mmAM (or PM) SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000    -- Convert string to datetime sql - convert string to date sql - sql dates format -- T-SQL convert string to datetime - SQL Server convert string to date SELECT convert(datetime, '10/23/2016',          101) -- mm/dd/yyyy SELECT convert(datetime, '2016.10.23',          102) -- yyyy.mm.dd SELECT convert(datetime, '23/10/2016',          103) -- dd/mm/yyyy SELECT convert(datetime, '23.10.2016',          104) -- dd.mm.yyyy SELECT convert(datetime, '23-10-2016',          105) -- dd-mm-yyyy -- mon types are nondeterministic conversions, dependent on language setting SELECT convert(datetime, '23 OCT 2016',         106) -- dd mon yyyy SELECT convert(datetime, 'Oct 23, 2016',        107) -- mon dd, yyyy -- 2016-10-23 00:00:00.000 SELECT convert(datetime, '20:10:44',            108) -- hh:mm:ss -- 1900-01-01 20:10:44.000 -- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109) -- 2016-10-23 11:02:44.013 SELECT convert(datetime, '10-23-2016',          110) -- mm-dd-yyyy SELECT convert(datetime, '2016/10/23',          111) -- yyyy/mm/dd SELECT convert(datetime, '20161023',            112) -- yyyymmdd -- 2016-10-23 00:00:00.000 SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm -- 2016-10-23 11:02:07.577 SELECT convert(datetime, '20:10:25:300',             114) -- hh:mm:ss:mmm(24h) -- 1900-01-01 20:10:25.300 SELECT convert(datetime, '2016-10-23 20:44:11',      120) -- yyyy-mm-dd hh:mm:ss(24h) -- 2016-10-23 20:44:11.000 SELECT convert(datetime, '2016-10-23 20:44:11.500',  121) -- yyyy-mm-dd hh:mm:ss.mmm -- 2016-10-23 20:44:11.500 SELECT convert(datetime, '2008-10-23T18:52:47.513',  126) -- yyyy-mm-ddThh:mm:ss.mmm -- 2008-10-23 18:52:47.513 -- Convert DDMMYYYY format to datetime SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105) -- 2016-01-31 00:00:00.000 -- SQL string to datetime conversion without century - some exceptions SELECT convert(datetime, '10/23/16',          1)                  -- mm/dd/yy SELECT convert(datetime, '16.10.23',          2)                  -- yy.mm.dd SELECT convert(datetime, '23/10/16',          3)                  -- dd/mm/yy SELECT convert(datetime, '23.10.16',          4)                  -- dd.mm.yy SELECT convert(datetime, '23-10-16',          5)                  -- dd-mm-yy SELECT convert(datetime, '23 OCT 16',         6)                  -- dd mon yy SELECT convert(datetime, 'Oct 23, 16',        7)                  -- mon dd, yy SELECT convert(datetime, '20:10:44',          8)                  -- hh:mm:ss SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) SELECT convert(datetime, '10-23-16',          10)                 -- mm-dd-yy SELECT convert(datetime, '16/10/23',          11)                 -- yy/mm/dd SELECT convert(datetime, '161023',            12)                 -- yymmdd SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13)        -- dd mon yy hh:mm:ss:mmm SELECT convert(datetime, '20:10:25:300',        14)           -- hh:mm:ss:mmm(24h) SELECT convert(datetime, '2016-10-23 20:44:11',20)            -- yyyy-mm-dd hh:mm:ss(24h) SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)       -- yyyy-mm-dd hh:mm:ss.mmm