PowerPoint–My Dashboard and Report Design Tool

At some point I think that I am becoming a Microsoft Office specialist as opposed to a BI Architect.  All of this work in Excel and now PowerPoint.  Okay, done with the ramblings.  As I have noted in a couple previous posts, I am working with a team on the Modern Apps Live! conference which is in Vegas next week.  Well, this is another “lesson learned” that I wanted to pass along as a result of doing that work.  (Hope to see you there.)

Using PowerPoint 2013

So I had to create two types of data visualizations for this conference.  Usually, I would use paper or white board to sketch it out and then proceed to make it a reality.  Somewhere along the way, I heard that Microsoft uses PowerPoint to lay out UIs.  Not sure if it is true or not, but it seemed easier and less expensive than Blend or Visio, so I thought I would give it a try.

So, I first needed to create a summary report for a poll within the app that was created.  I used the standard tools with in PowerPoint such as tables, charts, text boxes, and images to mock up my report.  What I liked was I was able to add notations to the mockup for future reference.

I had some frustration creating the charts as I wanted them to be representative.  But overall not a bad experience.  The next task I was taking on was working with the dashboards I was going to create in Excel 2013.  I still wanted to lay it out so I knew what I would be trying to design.  This was when I stumbled onto the Storyboarding menu.

I actually like using the shapes in this toolset better.  Turns out this is available when you install Visual Studio Ultimate, Visual Studio Premium (my version), or Visual Studio Test Professional.  More on that can be found on MSDN – Storyboard Using PowerPoint.  This can be integrated into TFS and directly associated to work items.  I am not a UX expert, but I like the ability to add tabs like I will have in Excel and there is even a SharePoint page background.

However, as you can see, even if you don’t have Storyboarding you can still effectively build up a PowerPoint slide to look like the report, dashboard, or even SharePoint page.  I was not sure if I would be able to embrace this, but in the end I really like the simplicity and using PowerPoint allows for comments, versioning in SharePoint, and other mechanisms to support dashboard design.

I also wanted to pass along another blog post I found from Jason Zander on the Windows Azure team on the same subject:  My Favorite Features: Creating Storyboards with PowerPoint.  Hopefully this gives you another simple way to mock up reports and dashboards when you can’t find that User Experience Pro.

Steps to Preload Data into Tables with SSDT

I am working as the data architect and developer on a modern app build with a the team from Modern Apps Live! in Vegas.  The goal of the project is to provide guidance to build modern applications and use this application as a reference.  While the conference is focused on the why of the build, we have learned some interesting things about how as well.  This is one of those how items.

In this post, I needed to preload some data into the database.  I wanted to include this process in the database project I had created.  However, I quickly found out that this was not a straightforward as I thought it would be.  Here are the steps I followed and any of the gotchas along the way.

1. Create Scripts for the Load Queries.

I started out with scripts that included a DELETE statement followed by an INSERT statement.  However, this created problems when data existed, particularly when the table is a list table used as a foreign key.

Next, I tried MERGE.  This worked great.  This gives me a way to handle new records that are required for the lookup or any changes made to existing data.  Here is the script I used:

merge dbo.MVCategory as target 
using ( 
        select 1, 'Fun' 
        union 
        select 2, 'Technology' 
        union 
        select 3, 'Entertainment' 
        union 
        select 4, 'News' 
        union 
        select 5, 'Sports' 
        union 
        select 6, 'Off-Topic' 
    ) as source (CategoryID, CategoryName) 
    on target.CategoryID = source.CategoryID 
when matched then 
    update set target.CategoryName = source.CategoryName 
when not matched then 
    insert (CategoryID, CategoryName) values (source.CategoryID, source.CategoryName) 
;

After going through this process on my own, I also found the same recommendation from the SSDT team at Microsoft as noted here: http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx

2. Add the Scripts to Your Project

This step is pretty straight forward.  You can either create the script files and add them to your project or you can create them within your project as script files.

3. Change the Build Action to None

This was one of the key pieces I missed.  After I added the scripts to the project and then ran a build, it was broke the build.  Each of these files which were merge scripts reported an error during the build.  It turns out this is called out in the article I reference above as well.  SSDT (SQL Server Data Tools) is designed to build database objects not manipulate data.  One other area of grief caused by this is that you can break the build in the solution if your project is part of a bigger solution such as mine.  As a result, you will get grief from the other developers, you can trust me on this one.

The image below shows where to set the Build Action property to NONE.  This will exclude these files from the build in this format.

4. Add a PostDeployment Script to Your Project

If you do not already have a PostDeployment Script, you need to do this at this point.  This is a specific type of script task that can be found in the Add menu.

5. Add SQLCMD Statements to the PostDeployment Script

The final part of the process is to add SQLCMD statement to the PostDeployment script to execute the files you have created.  As noted in the help in the template, you can execute the scripts by calling a single SQLCMD statement for each script.

:r .\PreLoadMVCategory.sql

The :r {filename} syntax will expand the script for execution during a publish call or DACPAC creation.

I hope you find this useful as well.  This is a common task required in creating solutions.

Join Me at Modern Apps Live! Las Vegas

If you are familiar at all with Visual Studio Live! then you should check out this new conference.  At the Vegas conference, I am participating in this unique three-day event which is a series of sessions that build on each other as the conference progresses called Modern Apps Live! The goal is to do a end-to-end application build during which the presenters build the application as well as focus on Application Lifecycle Management, best practices, tools, and a variety of technologies.

I will be presenting on the database design and business intelligence components of the solution and would love to see you there.  To make this even sweeter, if you register with the following code, MVSPK3, you will qualify for one of the following offers:

• The 5 day all-access Best Value Conference Package for just $1,595 – a savings of $500.00 off the standard price of $2,095! (*Includes pre and post conference events.)

OR:

• The 3 day Modern Apps Live! Conference for just $1,295 – a savings of $400.00 off the standard price of $1,695

This is for new registrants only.  Make sure to use the code by February 27, 2013, to take advantage of the full discount.

Also keep in mind that you will be able to take advantage of the Visual Studio Live! sessions as well as this is a cohosted conference.

I hope to see many of you there!