GeoFlow Beta – Untapping 3D Visualization

At SQL Saturday 198, in Vancouver (Canada), I presented a Lightning Talk on GeoFlow. This is an add-in to Excel 2013 that is still in beta. In this session, I showcased a short tour of Earthquake incident data using Column Chart and  Heatmap with different location and zoom level.

The demo I used at SQL Saturday 198 is shown below.

 

A week later, at SQL Saturday 177 in Mountain View (USA), I decided to take up a challenge to the next level. I compared a few different geospatial analysis and visualization tools offered by Microsoft. In this session, I learned a lot from discussions with audience (That is the best session that  a speaker could ever ask for, IMHO). In fact in my session, Jason Horner (b | t) who is a geospatial expert, attended my session and made good points about GIS! I digressed.

This article summarizes what GeoFlow is / does and what it is not / does not based on my experience and a number of discussions with peers.

As I use the product more and more, I will endeavor to update the list. This is also a view of the current state of GeoFlow beta at the time of writing. Some of the items in list may change due to improvement of the product or as I learn more about it.

 

What GeoFlow is / does:

  1. It supports 3D Visualization of geospatial data / analytical geography data. It aggregate values (e.g. sales volume) on categories (e.g. product categories) across geographical data.
  2. It claims to handle millions of rows – but this does not mean handling millions of unique points. When dealing with 548 number of close proximity Earthquake incidents, I can see that the points start flickering.
  3. It supports Column bar chart with a number of different shapes, as well as Bubble and HeatMap chart types.
  4. It takes textual address such as Street, City, Zip, County, State and Country.
  5. It also takes Longitude and Latitude points.
  6. It allows zooming in-out using Ctrl key and mouse wheel. With the keyboard you can use Ctrl key and + sign to zoom in or Ctrl key and – sign to zoom out.
  7. It has time play feature that is great to see growth over time. However, it is not always easy to analyze data at time play when there are large varieties of data movement. This can be addressed by slowing down and focusing on shorter time play.
  8. It uses Scene to capture Time play or a snapshot the geospatial visualization.
  9. It has Tour management tool to capture multiple scenes.
  10. It supports a number of pre-selected themes that shows geographical names, such as country name, country boundaries.
  11. It relies on Bing to render the geospatial information.
  12. It has Search functionality which will display and zoom in to the location specified. Searching some locations may be directing us to unintended location (this is normal / common). As an example, when searching “Silicon Valley” on my demo directed me to India (20.00565, 73.765862). 
  13. It supports multiple layer overlay which can be handy to analyze multiple data sets.
  14. Its HeatMap feature can represent intensity based on aggregation value such as volume of sales.
  15. It allows Annotation text boxes on the map.

What GeoFlow isn’t / doesn’t:

  1. It does not support SQL Server Geography data type.
  2. It does not support location hierarchy or aggregation level (unlike Power View, where you can double click on Country and it will go to more granular geographical level as long as the model has been setup properly).
  3. It does not work without internet connection.
  4. It does not have a “Z” index. Such information may be handy to map air traffic. This conversation came up during SQL Saturday 177 – my audience is super smart!
  5. It does not support elevation information. Another gem that came up at SQL Saturday 177.
  6. It does not have any tool to export to mp4 or other video file format. Although you can use screen capture software to save the video.
  7. It does not support Chloropleth map.
  8. Currently GeoFlow does not seem to support discrete GPU; some laptops, Lenovo and Sony VAIO require the discrete GPU to be disabled. This means that it may not be possible to show case GeoFlow on a larger HD display via HDMI. The issue with VAIO laptop has been logged on Connect.
  9. It does not allow custom map background.
  10. Data refresh is not reflected on the map. A new separate workbook needs to be recreated. GeoFlow seems to import the data from the workbook once. This issue has been logged on Connect.
  11. It does not allow customizable tooltip; i.e. it only uses the information that is used for mapping. This suggestion has been logged on Connect.
  12. It does not support filtering on Category and Height values. The data must be prepared first for filtering prior to creating the first GeoFlow map due to the limitation on data refresh as outlined in number 10.
  13. GeoFlow is restricted to 1,048,575  rows (+ 1 table heading row) to be precise. This is because GeoFlow is based only on a table that can be created within Excel 2013.
Update (April 1, 2013): GeoFlow is limited to 1,048,575 rows if the data model is based on an Excel table. However, when PowerPivot is used as the data source (e.g. connecting to a SQL Server database via PowerPivot), larger volume of data can be accessed by GeoFlow.  The caveat is that it will not be able to refresh the data from SQL Server and that the data must be in the Data Model.

Other GeoFlow Examples

Jason Thomas has written a very good introduction article on GeoFlow. You can check it out here.

Paul Turley has just started a community project with GeoFlow. The project is a short world tour with visits to a few SQL Server community members and leaders around the globe talking about their SQL Server communities.

 

Wrap Up

Still in beta, GeoFlow add in for Excel 2013 has shown good potential for visualizing geospatial data in 3D.

 

Further Reading

Ability to customize Tooltip on Connect.

Geoflow Error when GPU is enabled on Sony VAIO laptop on Connect

Data refresh issue – GeoFlow does not reflect changes on Connect

Legend Box Disappears when reopening GeoFlow on Connect

GeoSpatial Analytics Using Microsoft BI at PASS Business Analytics Conference, April 10 – 12, 2013; presented by Jason Thomas

Analytic Cartography: Master Geospatial Reporting at SQL Saturday Chicago, April 13, 2013; presented by Julie Koesmarno

 

PowerPivot 2013 Error: Data Model Damaged

I have been using PowerPivot in Excel 2013 and SQL Server 2012 SP1 Tabular Model side by side for over 3 months now. Recently I have just encountered the following error when creating new Excel 2013 workbook from scratch and adding a table into the Data Model.

We couldn’t load the Data Model. This may be because the Data Model in the workbook is damaged.

 PowerPivot Error - Data Model may be damaged

 

PowerPivot is unable to load the Data Model.

PowerPivot error: PowerPivot is unable to load the Data Model

 

It seems that I am not the only who is having the issue. Chris Webb (b | t) mentioned to me that he also has encountered it in the past. Others have mentioned that it’s been working for them without any issue.

I have recently logged the issue on Connect. Please vote if you encounter the same issue.

To summarize, the environment details I have this happened are:

Operating System: Windows 8 Pro (6.2.9200) – 64-bit

SQL Server 2012 SP1 (11.0.3128) – 64-bit

Office Professional Pro 2013: Microsoft Excel 2013 15.0.4454.1503 MSO (15.0.4454.1504) 64-bit

 

The workaround is simply to turn off SQL Server Analysis Services (Tabular) services. This should allow users to add data model to PowerPivot again. Although the workaround is simple and works, it becomes cumbersome for us who needs to use both PowerPivot and Tabular Model at the same time.

Hopefully this will be fixed soon.

 

Wrap Up

Having PowerPivot (Excel 2013) and SQL Server Analysis Services Tabular Model instance installed on the same machine may result in an error when opening PowerPivot. The error “We couldn’t load the Data Model. This may be because the Data Model in the workbook is damaged.” appears even though the data model is not damaged (or not even created yet). The work around is to stop the Tabular Model instance. The issue has been logged on Connect.

 

Further Reading

PowerPivot couldn’t load Data Model when SSAS Tabular Instance is running on Microsoft Connect.

 

Editing Published SSIS Package in SQL Server 2012

So, you have inherited a set of SQL Server 2012 Integration Services projects that have been deployed on a server. Your company do not use source control so you can’t easily see what was last published (Oh no!!). Or, you have deployed something on the server but you are not sure if it is the right version. Bottom line is you want to inspect or to edit a package that is already published on the server. Unfortunately at the moment  there is no way of just quickly downloading that single SSIS package.

SQL Server 2012 Integration Services now is now making use of  Project Deployment Model. For more information, read it here. Back to the issue at hand, if you want to get a copy of what is in the production, in summary, there are two “easy” ways of doing so.

A. Creating a new project in SQL Server Data Tools (SSDT) and import the project from existing catalog.

B. Exporting the project into ispac file via SQL Server Management Studio (SSMS).

 

Caveat: Both these options require access to the project on the server.

 

 

Option A: New Project

1. In the New Project dialog box of SSDT, browse to Installed Templates > Business Intelligence Integration Services on the left navigation pane.

2. Choose Integration Service Import Project Wizard option, enter the Name, Location and Solution Name accordingly. Then click OK to proceed to the next step.

Integration Services Project Wizard

3. Click Next on the Integration Services Import Project Wizard window

SSDT - Integration Services Project Wizard - Introduction

4. On the Select Source dialog, choose Integration Services Catalog. Then provide the details of the project on the server that you wish to import.

SSDT - Integration Services Project Wizard - Select Source

SSDT - Integration Services Project Wizard - Select Project

 

 

5. Follow the rest of the instruction.

SSDT - Integration Services Project Wizard - Validation

SSDT - Integration Services Project Wizard - ReviewSSDT - Integration Services Project Wizard - Results


6. Once the project is created, you can browse to the package(s) that you wish to inspect. In my example here, I have  4 connection manager files and 9 dtsx files.

SSDT - Integration Services Project Wizard - Project Created

This option is one of the safest / simplistic ways of getting SSIS project from the server. All the components in the Project, such as Project Variables and Project Connection Managers are kept in tact.

 

Option B: The shortcut

1. Navigate to the SSIS project on SSMS; i.e.  [Server Name] > Integration Services Catalogs > SSISDB > [Project Folder] > Projects > [Project Name] as shown on the picture below.
SSMS - Export SSIS Project

2. Right click on the [Project Name], then choose Export….

3. Choose the destination folder on “Save As” to save the .ispac file

4. Navigate to the folder where the file has been saved and rename the file extension to .zip file.

5. Browse the zip file and you should be able to see the .dtsx file(s).  Please note that in this example, I have 4 connection manager files and 9 dtsx files.

Browse zip file that was renamed from ispac

6. Now you can extract the desired .dtsx file(s) from the zip file.

Note: If the server is in Production, you could ask your DBA to export the .ispac file of the project (Step 1 & 2). Then you can continue with Step 3.

Special thanks to Josh Fennessy (b | t), for providing Option B.

 

Would it work for SSDT Visual Studio 2012?

Update – 6 March 2013: Both options work for Visual Studio 2012 too. (Read Microsoft announcement on SQL Server Data Tools – Business Intelligence for Visual Studio 2012). At the time of writing (a few minutes of exploring SSDT Visual Studio 2012), everything works the same for Option A. There is a major face-lift with monochrome look.

 

SSDT - What it looks like in Visual Studio 2012

 

Wrap Up

Integration Services in SQL Server 2012 introduces Project Deployment Model. This means that accessing a single SSIS package that has been published from the server is not as trivial. This article describes two ways of accessing the desired package(s) or the project as a whole from a server. Option A, which is importing the package from the Integration Services Catalogs is the safest way. Option B, is a shortcut method that is recommended only for advanced level users.

This article does not describe how the pros and cons of the new Integration services Project Deployment Model as it is covered in a couple of blogs as listed on the Further Reading section.

 

Further Reading

Can I deploy a single SSIS package from my project to the SSIS Catalog? by Matt Masson

Short Review on .ispac SSIS Deployment by Julie Koesmarno – my thoughts on Matt’s article above.

What’s New (Integration Services) on MSDN

 

 

SQL Saturday 198 – Calling SQL, Data and Snow Enthusiasts

There are 42 amazing sessions lined up for SQL Saturday 198. There will be a lot of speakers and attendees from different continents, America, Europe as well Australia! So, if you are a SQL Server professional or Data is your bread and butter, this is the event you would not want to miss this month! Register and build your schedule now.

If that doesn’t entice you, SQL Saturday 198 organizers sure know how to throw a great event. They are also organizing a ski trip on Friday, Feb 15, 2013. Check it out at Scott Stauffer’s website.

Josh Fennessy and I are providing you tips and tricks in “The Essential 8: Narrative Reporting Techniques“. This will be a preview to our PASS Business Analytics Conference in April 2013.

I am also honored to showcase “Power View for Geographical Analysis” next week at SQL Saturday 198. A sneak-peek interview video podcast, titled “The Power of Power View in Excel 2013″, with Karen Foster (Director | Microsoft) and me is available from Microsoft Technet Radio site.

 

 

A Lightning Talk demonstrating an upcoming GeoFlow add-in for Excel 2013 is also in the schedule. Here’s a little teaser from me:

 

 

And last but not least,, SQL Saturday 198 is fantastic way of networking with other passionate SQL Server and Data Professionals at both local and international level.

Hope to see you in beautiful Vancouver next week!

 

Malibu SQL Server User Group

SQL Community in Malibu!

Great news for new and experienced SQL Server professionals! Malibu SQL Server User Group will be having our first meeting in March 2013. We are currently finalizing the event and would be announcing the speaker / presentation details.

Please visit our website on: http://sqlmalibu.sqlpass.org

Our initiative survey is also available for you to fill in.

 

Join SQL Malibu

By joining, you can get an up to date info about our User Group meetings. If you are not a PASS member, you can register first.

Once you have registered or if you are an existing PASS member, you can add us to your chapter membership through the dashboard.

Join Local Chapter via Dashboard

 

 

Simply type “Malibu” on [Filter by Keyword] field to find us.

 

Speakers wanted!

If you are interested in speaking or have any questions about the Malibu SQL Server User Group, please contact me on sqlmalibu at outlook dot com

Are you new and wanting to test what it feels like to be a speaker? Give me a shout, I have plenty of ideas to ease you in to speaking at our friendly Malibu SQL Server User Group!

 

Hope to see you there in March!

 

 

24HOP Back Again With Business Analytics

In 12 hours back to back live sessions and 12 hours on-demand reply, industry experts will take to the virtual stage to deliver a series of one-hour webcasts focused on data analytics and visualization, big data innovations and integration, information delivery, advanced analytics, and much more! This 24 Hours of PASS event is a preview of PASS Business Analytics Conference to be held in April later this year.

Starting at 1:00 PM GMT / 8:00 AM EST /  5:00 AM PST on Jan 30th 2013 (or midnight on Jan 31st, 2013 Sydney), the live sessions are listed below:

January 30th, 2013 13:00 GMT
Session 01 Live: PowerPivot for Excel 2013 in Action
Presenter: Alberto Ferrari

January 30th, 2013 14:00 GMT
Session 02 Live: Putting the Business into Business Analytics
Presenter: Jessica Moss

January 30th, 2013 15:00 GMT
Session 03 Live: Big Data on the Microsoft Platform
Presenter: Andrew Brust

January 30th, 2013 16:00 GMT
Session 04 Live: Why? Where? Spatial Analysis in Excel 2013
Presenter: Peter Myers

January 30th, 2013 17:00 GMT
Session 05 Live: Finding Rare Needles in the Haystack – Predictive Modeling with Unbalanced Data
Presenter: Paul Bradley

January 30th, 2013 18:00 GMT
Session 06 Live: Ensuring Compliance of Patient Data with Big Data and BI
Presenter: Denny Lee

January 30th, 2013 19:00 GMT
Session 07 Live: Dashboard Design Dos and Don’ts
Presenter: Stacia Misner

January 30th, 2013 20:00 GMT
Session 08 Live: What Is Big Data?
Presenter: Mark Whitehorn

January 30th, 2013 21:00 GMT
Session 09 Live: Marketing Forecasting with Excel
Presenter: Wayne Winston

January 30th, 2013 22:00 GMT
Session 10 Live: Visualizing Data with Power View
Presenter: Sean Boon

January 30th, 2013 23:00 GMT
Session 11 Live: Analyzing Data with Power View
Presenter: Jen Stirrup

Date/Time: January 31st, 2013 00:00 GMT
Session 12 Live: From Data to Insight – Views from Microsoft Finance
Presenter: Marc Reguera

 

 

I am very excited to host not one, but two 24 Hours of PASS Sessions tomorrow. The sessions I’ll be hosting are two of my favourite topics, Putting the Business into Business Analytics (Jessica Moss) and Why? Where? Spatial Analysis in Excel 2013 (Peter Myers).

See you there!

 

 

 

Starting 2013 with BI Talks

After the great PASS Summit 2012 finished, I have been counting down to the next SQL community events. Partly because I have learning withdrawals, and mostly because I miss my SQL Family!

Luckily, there are two SQL Saturdays scheduled near* my area. One of them is SQL Saturday #198 in Vancouver, Canada (16 Feb 2013) and another one is SQL Saturday #177 in Silicon Valley, US (23 Feb 2013). What I love the most about SQL Saturday is that it’s the best bang for your buck! If you look at the schedule for SQL Saturday #198 (Vancouver) here, you can immediately recognize many of the speakers are MVPs! Same goes to SQL Saturday #177 (Silicon Valley), the Partial List of Speakers shows that you will be in for a treat.

 

Also, if you are in the Business Analytics arena, the PASS BA Conference 2013 in Chicago is surely not to be missed. It’s a one stop shop for business analysts, data scientists, data architect and BA/BI practitioners. Not convinced yet? Twelve of these talks are going to be previewed for free on the upcoming 24 hours of PASS.  So check them out!

 

I am very delighted to also announce that I will be speaking at all those three events with the following topics:

1. Power View for Geographical Analysis for SQL Saturday #198.

2. The Essential 8: Narrative Reporting Techniques, with an expert BI architect and a friend, Josh Fennessy, for both SQL Saturday 198 and PASS BA Conference.

3. Analytic Cartography: Master Geospatial Reporting for SQL Saturday #177.

 

That’s my way of starting 2013!

Hope to see you, SQL Family, in some (or all!) of these events.

 

Standing Firm – Story of a WIT

This is my first blog for T-SQL Tuesday. The theme for this month is Standing-Firm, which could be a story relating to one of these words: resolve, resolution, or resolute. I picked resolute.

My story is in relation to Women In IT/Engineering. Looking back, the first woman in Engineering, I have known, is my mum. Growing up, I was aware about this but never really gave it the proper recognition it deserved. Today I would like to do that.

I was born and grew up in the tropical country of Indonesia, where, as a child I learned so much about the limitations and boundaries that females could face. I became interested in data, specifically business data, in my early teens as my parents owned a business supplying standard and custom nuts and bolts. While their business was small it did well enough to keep all four children fed and educated.

One of my role models and inspirations is my mum. For 45 years, she has managed the day-to-day goings on within the workshop, negotiating deals, organising stocks and giving advise to engineers. Her understanding did not come from school (as she only finished year 8), but from practical experience that she learned from my dad and peers. There were no associations in Indonesia designed to help women in Engineering or IT. She came from a poor family, with little education, but she did it all. She raised four children and embedded these principles when we were growing up: passion, determination and helping others.

My mum has lived a very interesting and admirable life. For just over 14 years while I was still living under the same roof as my parents, my mum always said that “Our gender should never inhibit us from pursuing our dreams”. I held on to that, moved out of home and left Indonesia to live in Australia so I could pursue studying IT in high school and university. While studying IT, it was not uncommon that I would be the only girl, or the only one from a non-English speaking background. This didn’t quiver my passion for or my determination to excel in IT. Even with entering IT at a professional level, it is still not uncommon to be the only female in my area. I have become so used to this situation that I don’t see gender difference as an issue.

Map of places I have lived in

In 2012, I was determined that I wanted to learn more and to help others, by blogging and speaking at community events. I was nervous, not because I was a female, because I considered myself inexperienced compared to some others. Even so, if my blog posts or presentations could help even one person, I would be happy. My determination to help others has helped me to overcome my nervousness and learnt more about SQL Server. PASS and #SQLFamily have also been very supportive in this.

This year, I want to become a more confident speaker and a more knowledgeable SQL professional who helps and learns from #SQLFamily. I also want to be involved more with Women in IT activities via PASS and on personal level. My Women in IT/Engineering inspiration started with my mum at a young age, and I would like to help other females to pursue their professional dreams. We should be able to show our abilities within the workplace and still embrace our female nature (for me this would include my inability to open jars, but that’s why jar opener was invented, right?). We are all unique, female or male.

 

This post is dedicated to my mum.

“Our gender should never inhibit us from pursuing our dreams” – Paulin Koesmarno

Wrap Up

Thank you to Jason Brimhall (b | t) for hosting TSQL Tuesday – Standing Firm this month. The original invitation is available here.  The rules for participation are in the invite. You can also read others’ submissions for the theme this month.

If you are a PASS Member and interested in Women in IT events and activities, please check our website here.

 

Power View for Geographical Analysis with Excel 2013

Earlier this month, I have presented “Power View for Geographical Analysis” as part of my Pacific Northwest US tour. My apologies for posting this fairly late in the month, following the presentations, as I have a few other commitments earlier this month.

Without further ado, below are the sample files:

1. Earthquake Last 30 Days

Download Excel 2013 file.

The data is retrieved from the USGS website. This particular workbook was manually prepared (i.e. data copied and pasted from the webstite). The workbook has been cleansed and manipulated using Excel flash fill functionality and contains from 11 September 2012 to 11 October 2012.

The workbook does not retrieve the data from the USGS website automatically.

Earthquake Analysis - About 1000 miles radius of Portland

Earthquake Analysis - About 1000 miles radius of Portland

 

Earthquake Analysis - by the hour

Earthquake Analysis - by the hour

 

2. PASS Chapter and SQL Saturday Growth

Download Excel 2013 file.

Taking the data from 2 different sources, Dan English’s SQL Saturday workbook and Karla Lundrum’s report from PASS HQ, this demo shows how to combine them together.

There is also a section here that shows implications of incomplete Location hierarchy. In this example, Australia has complete Country – StateProv – City definition, whereby Singapore is missing StateProv information.

PASS Chapter and SQL Saturday Growth

PASS Chapter and SQL Saturday Growth

 

 

Data Integrity - Incomplete StateProv Level

Data Integrity - Incomplete StateProv Level

 

3. Internet Sales by Customer Occupation

Download Excel 2013 file.

A simple Adventure Works sample which shows well structured data model with good quality data. In this case we analyse Internet Sales by an attribute that doesn’t directly link to the sales, such as Customer Occupation.

Internet Sales by Customer Occupation

Internet Sales by Customer Occupation

 

Wrap up

These sample files contain static data – i.e they do not retrieve data from data sources auotomatically. Hence the data is as at the time of preparation. The aim of the sample files is to provide an overview of Geographical Analysis techniques using Power View in Excel 2013. As I have mentioned in my sessions, having good data model and data quality is important to build good interactive analysis using Power View. However, in real life, we may be far from a perfect data quality as shown in the Earthquake example and the PASS Growth example. However, with creativity we can generate something meaningful for analysis.

Hope you enjoy the sample files.

Note: the workbook should work with Office 2013 Preview, if it does not, please let me know.

My version of Excel 2013 is: Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.

 

 

Measure Selection Using Slicers – Excel 2013

I enjoy reading Jason Thomas’ blog, especially on SSRS. He has very good techniques that he describes very well.

In his latest post, Measure Selection Using Slicers, using Excel 2010 he explores how you can allow measure selection using slicers, which I think is really great. I have used the technique multiple times in Tabular Model.

The Excel 2013 version of his is here, in case you are thinking of testing it out on Excel 2013. I upgraded the PowerPivot first when I opened the original workbook in Excel 2013. Below is a reusable set of steps that you can use to upgrade an Excel 2010 workbook with PowerPivot to Excel 2013.

Caveat: upgrading from Excel 2010 to Excel 2013 may be problematic Read Jason Thomas discussion here. This discussion is related to having different Excel table names to the PowerPivot table names.

Upgrading Excel 2010 workbook with PowerPivot to Excel 2013

1. Open the workbook. in Excel 2013. Ensure you click on “Enable Content”

Open Excel 2010 workbook in Excel 2013

If you click on one of the slicers, you may get an error similar to the following.

“The workbook has a PowerPivot data model created using a previous version of the PowerPivot add-in. You’ll need to upgrade this data model with PowerPivot for Excel 2013″.

 

2. Click on PowerPivot tab, and choose Manage

Click on "Manage" PowerPivot

You will get a warning similar to when you click on a slicer. Clicking OK will open up a dialog box to upgrade.

 

3. Click OK on the PowerPivot upgrade option.

Upgrade PowerPivot to Excel 2013 Dialog Box

 

4. Once PowerPivot is finished upgrading, a dialog box will appear to notify you that the workbook is still running in compatibility mode. Choose Yes to upgrade the workbook fully.

Upgrade the Workbook to Excel 2013

 

The workbook will be closed and reopened as an Excel 2013 version.

The version I am using is Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.

Update: I spoke with Jason Thomas and we confirmed that we are running different versions of Microsoft Office 2013. I have downloaded mine from my Technet Subscription which is the Microsoft Office Professional Plus 2013, whereas he obtained his copy from Office Preview website.

 

Enjoy!