Category Archives: SQL Server Blogs

Four SQL MVPs at LobsterPot – including three in Australia

Today LobsterPot Solutions sets a new first. We are the only company to ever employ three current Australian SQL MVPs, giving us four awardees in total. Congratulations to Martin Cairney who joins Julie Koesmarno (AUS), Ted Krueger (USA) and me (AUS) as recipients of this prestigious award. This demonstrates LobsterPot’s ongoing commitment to the SQL Server community, that show that our consultants are truly influential in the SQL world.MVP_FullColor_ForScreen

From Microsoft’s website about MVPs:
Microsoft Most Valuable Professionals, or MVPs are exceptional community leaders who actively share their high-quality, real-world deep technical expertise with the community and with Microsoft. They are committed to helping others get the most out of their experience with Microsoft products and technologies.
Technical communities play a vital role in the adoption and advancement of technology—and in helping our customers do great things with our products. The MVP Award provides us with an opportunity to say thank you and to bring the voice of community into our technology roadmap.

This fits very closely with LobsterPot’s desire to help people with their data story. We help with the adoption and advancement of SQL Server, and help customers do great things with data. It’s no surprise that we see a high proportion of LobsterPot consultants are MVP awardees.

Getting Started with SSIS: Get the Software and Tools

People often ask me for tips on getting started with SSIS. My advice is always the same: Get a copy of SQL Server Developer Edition. At the time of this writing, the current version of SQL Server is SQL Server 2014. If you work in an organization that uses SQL Server, you may be able to obtain a copy of SQL Server 2014 Developer Edition from your organization. If not, I highly recommend purchasing your own copy. SQL Server 2014 Developer Edition is available at Amazon.com for about $50 USD. Developer…(read more)

SQL Server DML Triggers – Tips & Tricks – Part 1 (“Intro” & “Triggers activation order”)

This is the first blog post in a series of posts about Tips & Tricks of SQL Server DML Triggers.

Introduction

Triggers are often one of the main reason of quarrel between Developers and DBAs, between those who customize a database application and those who provides it. They are often the first database objects to be blamed when the performance degrades. They seem easy to write, but writing efficient Triggers is not easy. They have many side effects, but also a very important characteristic: they allow solving problems that cannot be managed in any other application layer.

A Trigger is a special type of stored procedure: it is not called directly, but it is activated on a certain event with special rights that allow you to access in-coming and out-coming data that are stored in special virtual tables called Inserted and Deleted. Triggers exist in SQL Server since the version 1.0, even before CHECK constraint. They always work in the same unit-of-work of the T-SQL statement that has called them. There are different types of Triggers: Logon Trigger, DDL Trigger and DML Trigger; the most known and used type is Data Manipulation Language Trigger, also known as DML Trigger. This series of posts treats only aspects related to DML Triggers.

There are many options that modify run time Triggers’ behavior, they are:

  1. Nested Triggers
  2. Disallowed results from Triggers
  3. Server Trigger recursion
  4. Recursive Triggers

Each of these options has, of course, a default value in respect to the best practices of Triggers development. The first three options are Server Level Options and you can change their default value using sp_configure system stored procedure, whereas the value of the last one can be set at the Database Level.

What do you think about Triggers? In your opinion, based on your experience, are they useful or damaging?

You will meet people who say: “Triggers are absolutely useful” and other people who say the opposite. Who is right? Reading the two bullet lists you will find the main reasons of the two different theory about Triggers.

People say that Triggers are useful because with them:

  • You can develop customize business logics without changing the user front-end or the Application code
  • You can develop an Auditing or Logging mechanism that could not be managed so efficiently in any other application layer

People say that Triggers are damaging because:

  • They are sneaky!
  • They can execute a very complex pieces of code silently!
  • They can degrade performance very much
  • Issues in Triggers are difficult to diagnose

As usual the truth is in the middle. I think that Triggers are a very useful tool that you could use when there are no other ways to implement a database solution as efficiently as a Trigger can do, but the author has to test them very well before the deployment on the production environment.

Triggers activation order

SQL Server has no limitation about the number of Triggers that you can define on a table, but you cannot create more than 2.147.483.647 objects per database; so that the total of Table, View, Stored Procedure, User-Defined Function, Trigger, Rule, Default and Constraint must be lower than, or equal to this number (that is the maximum number that will be represented by the integer data type).

Now, supposing that we have a table with multiple Triggers, all of them ready to fire on the same statement type, for example on the INSERT statement: “Have you ever asked yourself which is the exact activation order for those Triggers?” In other worlds, is it possible to guarantee a particular activation order?

The Production.Product table in the AdventureWorks2014 database has no Triggers by design. Let’s create, now, three DML Triggers on this table, all of them active for the same statement type: the INSERT statement. The goal of these Triggers is printing an output message that allows us to observe the exact activation order. The following piece of T-SQL code creates three sample DML AFTER INSERT Triggers on Production.Product table.

USE [AdventureWorks2014];
GO

-- Create Triggers on Production.Product
CREATE TRIGGER Production.TR_Product_INS_1 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_1';
GO

CREATE TRIGGER Production.TR_Product_INS_2 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_2';
GO

CREATE TRIGGER Production.TR_Product_INS_3 ON Production.Product AFTER INSERT
AS
  PRINT 'Message from TR_Product_INS_3';
GO

Let’s see all Triggers defined on Production.Product table, to achieve this task we will use the sp_helptrigger system stored procedure as shown in the following piece of T-SQL code.

USE [AdventureWorks2014];
GO

EXEC sp_helptrigger 'Production.Product';
GO

The output is shown in the following picture.

 

Picture 1 – All Triggers defined on Production.Product table

Now the question is: Which will be the activation order for these three Triggers? We can answer to this question executing the following INSERT statement on Production.Product table, when we execute it, all the DML INSERT Triggers fire.

USE [AdventureWorks2014];
GO

INSERT INTO Production.Product
(
  Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
  ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
  RowGUID, ModifiedDate
)
VALUES
(
  N'CityBike', N'CB-5381', 0, 0, 1000, 750, 0.0000, 0.0000, 0, GETDATE(),
  NEWID(), GETDATE()
);
GO

The output returned shows the default Triggers activation order.

Message from TR_Product_INS_1
Message from TR_Product_INS_2
Message from TR_Product_INS_3


As you can see in this example, Triggers activation order coincides with the creation order, but by design, Triggers activation order is undefined.

If you want to guarantee a particular activation order you have to use the sp_settriggerorder system stored procedure that allows you to set the activation of the first and of the last Trigger. This configuration can be applied to the Triggers of each statement (INSERT/UPDATE/DELETE). The following piece of code uses sp_settriggerorder system stored procedure to set the Production.TR_Product_INS_3 Trigger as the first one to fire when an INSERT statement is executed on Production.Product table.

USE [AdventureWorks2014];
GO

EXEC sp_settriggerorder
  @triggername = 'Production.TR_Product_INS_3'
  ,@order = 'First'
  ,@stmttype = 'INSERT';
GO

At the same way, you can set the last Trigger fire.

USE [AdventureWorks2014];
GO

EXEC sp_settriggerorder
  @triggername = 'Production.TR_Product_INS_2'
  ,@order = 'Last'
  ,@stmttype = 'INSERT';
GO

Let’s see the new Triggers activation order by executing another INSERT statement on Production.Product table.

USE [AdventureWorks2014];
GO

INSERT INTO Production.Product
(
  Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
  ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
  RowGUID, ModifiedDate
)
VALUES
(
  N'CityBike Pro', N'CB-5382', 0, 0, 1000, 750, 0.0000, 0.0000, 0, GETDATE(),
  NEWID(), GETDATE()
);
GO

The returned output shows our customized Triggers activation order.

Message from TR_Product_INS_3
Message from TR_Product_INS_1
Message from TR_Product_INS_2

In this first part you have learnt how to set the activation of the first and of the last Trigger in a multiple DML AFTER INSERT Triggers scenario. Probably, one question has come to your mind: “May I set only the first and the last Trigger?” The answer is: “Yes, currently you have the possibility to set only the first Trigger and only the last Trigger for each statement on a single table”; as a friend of mine says (he is a DBA): “You can set the activation only of the first and of the last Trigger because you should have three Triggers maximum for each statement on a single table! The sp_settriggerorder system stored procedure allows you to set the first and the last Trigger fires, so that the third one will be in the middle, between the first and the last”.

All examples in this post are based on AdventureWorks2014 database that you can download from codeplex website at this link.

In The Cloud: Azure Operational Insights for Server Monitoring

Microsoft is now offering a preview version of Azure Operational Insights, which is a cloud based server monitoring solution and replacement for System Center Advisor. I decided to try it out on my desktop’s default installation of SQL Server 2014. I picked this SQL Server instance because it’s not configured how I would configure a production server. I wanted to see what things Azure Operational Insights would find. I went to http://azure.microsoft.com/en-us/services/operational-insights/ to get…(read more)

Merry Christmas

I just wanted to take a moment to wish a Merry Christmas to you: people in the SQL Community; people I see at clients and the like; and especially those people I am incredibly privileged to have working at possibly the best SQL Consultancy in the world.

To those who I have represent my brand: I love you guys! You’re all passionate about providing the best experience for our customers, developing the SQL community, and doing amazing things to help people improve their data story. I couldn’t be prouder of you all. Sure, there are times when I lose sleep (and hair) over stuff, but I know that we have each other’s backs, and that’s a brilliant thing. I’ve often likened us to that story about the tiger in a cage. The best way to defend such a tiger is to let it out of its cage. If I can help enable you, and remove any obstacles that come between you and your ability to be phenomenal, then that’s what I’ll try to do. We all have our different styles, but together I think we can be an incredible force. It’s been a crazy year in many ways, including starting the LobsterPot story in the US (and Ted – you’ve been incredible!), but we have even more exciting times ahead, I’m sure. The Microsoft data stack is developing quicker than ever, and people are using it in bigger and better ways all the time.

Merry Christmas guys. Let’s continue to spread the SQL cheer… 🙂

@rob_farley

Retrieving N rows per group

Sometimes a forum response should just be a blog post… so here’s something I wrote over at http://dba.stackexchange.com/a/86765/4103.

The question was somewhat staged I think, being from Paul White (@sql_kiwi), who definitely knows this stuff already.

His question:

I often need to select a number of rows from each group in a result set.

For example, I might want to list the ‘n’ highest or lowest recent order values per customer.

In more complex cases, the number of rows to list might vary per group (defined by an attribute of the grouping/parent record). This part is definitely optional/for extra credit and not intended to dissuade people from answering.

What are the main options for solving these types of problems in SQL Server 2005 and later? What are the main advantages and disadvantages of each method?

AdventureWorks examples (for clarity, optional)

  1. List the five most recent recent transaction dates and IDs from the TransactionHistory table, for each product that starts with a letter from M to R inclusive.
  2. Same again, but with n history lines per product, where n is five times the DaysToManufactureProduct attribute.
  3. Same, for the special case where exactly one history line per product is required (the single most recent entry by TransactionDate, tie-break on TransactionID.

And my answer:

Let’s start with the basic scenario.

If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP.

First, let’s consider the whole set from Production.TransactionHistory for a particular ProductID:

SELECT h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800;

This returns 418 rows, and the plan shows that it checks every row in the table looking for this – an unrestricted Clustered Index Scan, with a Predicate to provide the filter. 797 reads here, which is ugly.

Expensive Scan with 'Residual' Predicate

So let’s be fair to it, and create an index that would be more useful. Our conditions call for an equality match on ProductID, followed by a search for the most recent by TransactionDate. We need the TransactionID returned too, so let’s go with: CREATE INDEX ix_FindingMostRecent ON Production.TransactionHistory (ProductID, TransactionDate) INCLUDE (TransactionID);.

Having done this, our plan changes significantly, and drops the reads down to just 3. So we’re already improving things by over 250x or so…

Improved plan

Now that we’ve levelled the playing field, let’s look at the top options – ranking functions and TOP.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
)
SELECT TransactionID, ProductID, TransactionDate
FROM Numbered
WHERE RowNum <= 5;

SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

Two plans - basic TOPRowNum

You will notice that the second (TOP) query is much simpler than the first, both in query and in plan. But very significantly, they both use TOP to limit the number of rows actually being pulled out of the index. The costs are only estimates and worth ignoring, but you can see a lot of similarity in the two plans, with the ROW_NUMBER() version doing a tiny amount of extra work to assign numbers and filter accordingly, and both queries end up doing just 2 reads to do their work. The Query Optimizer certainly recognises the idea of filtering on a ROW_NUMBER() field, realising that it can use a Top operator to ignore rows that aren’t going to be needed. Both these queries are good enough – TOP isn’t so much better that it’s worth changing code, but it is simpler and probably clearer for beginners.

So this work across a single product. But we need to consider what happens if we need to do this across multiple products.

The iterative programmer is going to consider the idea of looping through the products of interest, and calling this query multiple times, and we can actually get away with writing a query in this form – not using cursors, but using APPLY. I’m using OUTER APPLY, figuring that we might want to return the Product with NULL, if there are no Transactions for it.

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

The plan for this is the iterative programmers’ method – Nested Loop, doing a Top operation and Seek (those 2 reads we had before) for each Product. This gives 4 reads against Product, and 360 against TransactionHistory.

APPLY plan

Using ROW_NUMBER(), the method is to use PARTITION BY in the OVER clause, so that we restart the numbering for each Product. This can then be filtered like before. The plan ends up being quite different. The logical reads are about 15% lower on TransactionHistory, with a full Index Scan going on to get the rows out.

ROW_NUMBER plan

Significantly, though, this plan has an expensive Sort operator. The Merge Join doesn’t seem to maintain the order of rows in TransactionHistory, the data must be resorted to be able to find the rownumbers. It’s fewer reads, but this blocking Sort could feel painful. Using APPLY, the Nested Loop will return the first rows very quickly, after just a few reads, but with a Sort, ROW_NUMBER() will only return rows after a most of the work has been finished.

Interestingly, if the ROW_NUMBER() query uses INNER JOIN instead of LEFT JOIN, then a different plan comes up.

ROW_NUMBER() with INNER JOIN

This plan uses a Nested Loop, just like with APPLY. But there’s no Top operator, so it pulls all the transactions for each product, and uses a lot more reads than before – 492 reads against TransactionHistory. There isn’t a good reason for it not to choose the Merge Join option here, so I guess the plan was considered ‘Good Enough’. Still – it doesn’t block, which is nice – just not as nice as APPLY.

The PARTITION BY column that I used for ROW_NUMBER() was h.ProductID in both cases, because I had wanted to give the QO the option of producing the RowNum value before joining to the Product table. If I use p.ProductID, we see the same shape plan as with the INNER JOIN variation.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

But the Join operator says ‘Left Outer Join’ instead of ‘Inner Join’. The number of reads is still just under 500 reads against the TransactionHistory table.

PARTITION BY on p.ProductID instead of h.ProductID

Anyway – back to the question at hand…

We’ve answered question 1, with two options that you could pick and choose from. Personally, I like the APPLY option.

To extend this to use a variable number (question 2), the 5 just needs to be changed accordingly. Oh, and I added another index, so that there was an index on Production.Product.Name that included the DaysToManufacture column.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, p.DaysToManufacture, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

And both plans are almost identical to what they were before!

Variable rows

Again, ignore the estimated costs – but I still like the TOP scenario, as it is so much more simple, and the plan has no blocking operator. The reads are less on TransactionHistory because of the high number of zeroes in DaysToManufacture, but in real life, I doubt we’d be picking that column. 😉

One way to avoid the block is to come up with a plan that handles the ROW_NUMBER() bit to the right (in the plan) of the join. We can persuade this to happen by doing the join outside the CTE. (Edited because of a silly typo that meant that I turned my Outer Join into an Inner Join.)

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
)
SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM Production.Product p
LEFT JOIN Numbered t ON t.ProductID = p.ProductID
AND t.RowNum <= 5 * p.DaysToManufacture WHERE p.Name >= 'M' AND p.Name < 'S';

image

The plan here looks simpler – it’s not blocking, but there’s a hidden danger.

Notice the Compute Scalar that’s pulling data from the Product table. This is working out the 5 * p.DaysToManufacture value. This value isn’t being passed into the branch that’s pulling data from the TransactionHistory table, it’s being used in the Merge Join. As a Residual.

image

So the Merge Join is consuming ALL the rows, not just the first however-many-are-needed, but all of them and then doing a residual check. This is dangerous as the number of transactions increases. I’m not a fan of this scenario – residual predicates in Merge Joins can quickly escalate. Another reason why I prefer the APPLY/TOP scenario.

In the special case where it’s exactly one row, for question 3, we can obviously use the same queries, but with 1 instead of 5. But then we have an extra option, which is to use regular aggregates.

SELECT ProductID, MAX(TransactionDate)
FROM Production.TransactionHistory
GROUP BY ProductID;

A query like this would be a useful start, and we could easily modify it to pull out the TransactionID as well for tie-break purposes (using a concatenation which would then be broken down), but we either look at the whole index, or we dive in product by product, and we don’t really get a big improvement on what we had before in this scenario.

But I should point out that we’re looking at a particular scenario here. With real data, and with an indexing strategy that may not be ideal, mileage may vary considerably. Despite the fact that we’ve seen that APPLY is strong here, it can be slower in some situations. It rarely blocks though, as it has a tendency to use Nested Loops, which many people (myself included) find very appealing.

I haven’t tried to explore parallelism here, or dived very hard into question 3, which I see as a special case that people rarely want based on the complication of concatenating and splitting. The main thing to consider here is that these two options are both very strong.

I prefer APPLY. It’s clear, it uses the Top operator well, and it rarely causes blocking.

@rob_farley

Invalid Quorum Configuration Warnings when failing over SQL Server Availability Group

At a client site today and they asked me about a warning that they got every time they manually failed over their SQL Server availability group.

It said: “The current WSFC cluster quorum vote configuration is not recommended for the availability group.” They were puzzled by this as they had a valid quorum configuration. In their case, they had a two node cluster using MNS (majority node set) and a fileshare witness.

The problem with that message is that it is returned when the node voting weight is not visible.

Windows Server 2008 failover clustering introduced node-based voting but later an option was provided to adjust the voting weight for each node. If the cluster is based on Windows Server 2008 or Windows Server 2008 R2, and KB2494036 has not been applied, even though each node has a vote, the utilities that check voting weight are not supplied a weight value. You can see this by querying:

SELECT * FROM sys.dm_hadr_cluster_members;

This will return a row for each cluster member but will have a missing vote weight.

Applying the KB hotfix will make this DMV return the correct values, and will make this invalid warning disappear.

Online certification exams are now available in Australia

I’ve been hoping this would happen for a while and now it’s here (in beta).

Whenever I take a certification exam, I find it removes my ability to work for most of a day, so I tend to schedule myself for two or three exams in a day, to avoid the overhead. It also means that I tend to limit the number of exams that I would take.

Online proctoring of exams changes all that for me. If I can just schedule an exam for lunch time or night, or weekend from my own office, I’ll be much more inclined to take more certification exams.

There are some rules:

  • You’ll be recorded—both video and audio—for the duration of the exam.
  • You can’t take notes during the exam.
  • You can’t eat, drink, or chew gum while you take the exam.
  • You can’t take a break—for any reason.

They seem reasonable to me and if you don’t like them, you can always attend an in-person exam. Better make sure you get to the rest room beforehand though Smile It’s not available in all countries yet but fortunately Australia is one of the countries in the list. I’m not sure how the countries are chosen because I notice that our Kiwi buddies aren’t in the list yet. I’m sure that will change over time.

Regardless, this is a really good initiative. Well done Microsoft Learning.

You’ll find more information here: https://www.microsoft.com/learning/en-us/online-proctored-exams.aspx

Telerik Dineissimo Sample App–Interesting Marketing Approach

I’ve liked the Telerik tools for a long time. I see many of my clients using either their controls for Webforms, and many using KendoUI for newer MVC development. I suppose it’s a challenge for such vendors to work out how to best market their products, but also a challenge to show developers how to best use them.

With KendoUI, I particularly liked the Kendo Dojo idea, where you could just interactively learn to use the framework directly from inside your own browser without having to install anything locally.

Today, I’ve noticed they released a sample application called Dineissimo but what caught my eye was the marketing approach. Basically, you need to visit: https://www.telerik.com/campaigns/platform/run-hybrid-app and download the app. You then need to find a particular part of the code, then run the program and use a QR (quick response) code to get things going. Finally, in the app, they get you to edit your profile and add a selfie or other image before sending off the details. And for a bunch of early takers, they’ll send an Amazon gift card. There’s a walk-through here: https://www.telerik.com/campaigns/platform/run-hybrid-app

I like the fact that they are releasing a bunch of source code to show how to use their product but it’s the marketing approach that interests me. It combines getting genuine interest in the product, making sure it’s actually seen, then getting the user further involved.

Nice job Telerik.

The Power BI dashboard in public preview – available also without Office #powerbi

Microsoft released a new version of Power BI in preview mode, including many new visualizations that are immediately available to all existing subscribers also in production, such as the long waited treemap, combo charts (combining line chart and column chart), and more. These features are available only in HTML5 visualizations, so you can only use the new features online. Microsoft shown these visualizations several times this year (PASS BA Conference in San Jose, and PASS Summit in Seattle), so now this is finally available to anyone. But there is much more!

Power BI Dashboard is a new service, now in public preview (unfortunately only in United States, not sure about which other countries are supported by now, certainly not Europe), that does not require an Office 365 subscription and, more important, provide a design experience on desktop also without having Excel or Office at all. In other words, there is a separate Microsoft Power BI Designer that enables you to:

  • Import data with Power Query
  • Create relationships between tables
  • Create data visualizations with Power View (running the latest HTML5 version locally in a desktop application)

This very first release does not include the full data modeling experience we are used to in Power Pivot, so you cannot create calculated columns or measures, but hopefully this will come in the next updates. In this way, you can use Power BI with a separate “data model” environment that is not tied to Excel. You can have an older version of Excel, or no Excel at all, and still design your data model with the Designer.

The goal of this app by now is to simply offer an offline design experience, and I have to say that performance of data visualization is very good. With the Designer you design data models and reports. Once published in the Power BI web site, you can “consume” data, but you can also modify the report and “pin” objects to a dashboard, so that you can build your own custom dashboard, such as the Retail Analysis Sample you can see below.

image

You can create datasets getting data from several SaaS applications, such as Dynamics CRM, Salesforce, GitHub, ZenDesk, SendGrid, and Marketo. You can also connect to live Analysis Services through a new gateway named Power BI Analysis Services Connector and use new native mobile apps for Power BI. Support for iPad should be already available (again, depending on countries, it seems not available in Europe by now). Future support for iPhone and Windows tablets has been already announced.

This is a very interesting evolution of the Power BI platform and I look forward to use it with real data and real users! Many tutorial videos are available on YouTube.