GeoFlow, SQL Server and Millions of Rows

Jun 10th, 2013 Leave a comment

Over the past few months, I have been asked if it is possible to connect GeoFlow to SQL Server database; and the more intriguing question is, why don’t GeoFlow connect to Tabular Model straight away instead of using PowerPivot. Although I do not know if there is such a plan related to the latter question, I do know that you can visualise data in SQL Server database or Tabular Model using GeoFlow.

The key ingredient for exposing this data is by storing and modelling it in PowerPivot. Why so? Perhaps one of the reasons is that most organisations, big or small, and individuals have or know how to use Excel (yes, they will need Excel 2013). Buying Excel 2013 Professional edition or higher would be cheaper to afford than the cost of buying a license for SQL Server. So, PowerPivot is the middle ground and it is the best win-win situation, for now. Those organisations which have invested time and effort converting their PowerPivot workbooks to Tabular Models however, would probably appreciate being able to expose their data in Tabular Model using GeoFlow.

Exposing SQL Server for GeoFlow

I have recently posted a step-by-step instruction to expose data stored in SQL Server database and Tabular Model to GeoFlow at Microsoft Customer Reviews of STB Products.

In the step-by-step instruction, I did mention that I was using 22 Million of records. The sample workbook is about 580 MB and shared via SkyDrive. PowerPivot in the workbook contains daily data of Internet Speed tests for 5 years and about 2,200 unique locations. The workbook cannot be opened using Excel Web App due to the size and GeoFlow is not supported in Excel Web App either. So, you will need to have Excel 2013 Professional edition or above, and have GeoFlow installed.

I have also created a tabular model based on the 22 Millions records, but only exposing the aggregated data via DAX. The PowerPivot in this workbook contains about 1 Million of records, containing monthly data for 5 years worth of Internet Speed tests conducted in about 2,200 unique locations. The workbook is about 6.2 MB and shared through SkyDrive.

I am deliberately making the workbooks available via SkyDrive, in case in the future and wouldn’t it be nice if, GeoFlow can be supported in Excel Web App too

Wrap Up

GeoFlow Preview for Excel 2013 can connect to SQL Server via PowerPivot. This article has shown how GeoFlow can render geospatial information from millions of rows stored in SQL Server database and Tabular Model.

Revisiting GeoFlow Public Preview

May 8th, 2013 1 Comment

As you may already know, GeoFlow Public Preview for Excel 2013 has been out for nearly a month now. I have posted a couple of articles on GeoFlow, the Beta version and the Public Preview version. I would like to revisit the Beta post on “What GeoFlow isn’t/doesn’t”. In this post, I would also like to highlight top 5 new features in the Public Preview.

Revisiting The “Not” List

Below is a revised list of what GeoFlow isn’t/doesn’t, for the Public Preview version.

It does not support SQL Server Geography data type.
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).
It does not work without internet connection. A suggestion on being able to cache the map has bee logged on Connect.
It does not have a “Z” index. Such information may be handy to map air traffic. This conversation came up during SQL Saturday 177 – super smart audience!
It does not support elevation information. Another gem that came up at SQL Saturday 177.
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. This suggestion has been logged on Connect.
It does not support Chloropleth map.
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.
It does not allow custom map background / ESRI Shapefile. This suggestion has been logged on Connect.
~~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.~~
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. Update on GeoFlow Preview: A free text form tool tip / annotation box is available though.
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.~~ This has been logged on Connect.
~~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.~~
GeoFlow does not give the option to change the colors used for the markers (bubble or column stack bars) from the default colors associated to the themes. This suggestion has been logged on Connect.
GeoFlow does not have an “unwrap” option to flatten / view the globe as a whole. This suggestion has been logged on Connect.

New Features

Top 5 new features in GeoFlow Public Preview:

1. Top 100 chart

2. Geocoding validation on the selected columns

Clicking on 85% on the example above will display the following Geocoding Alerts window:

3. Better UI interface

4. Data Refresh

5. Special Effects on tour mode visualization

I will be presenting att SQL Lunch this Friday (May 10, 2013) at 9:30AM PDT to discuss the enhancements and the limitations of current GeoFlow Public Preview for Excel 2013. Hope you can join me then!

Wrap Up

If you are keen on Geospatial reporting, GeoFlow is definitely worth checking out. There are some limitations in the current Public Preview as discussed above. I think now is a great opportunity to test out the Public Preview and providing feedback / suggestions for a better product either via Connect or GeoFlow Forum.

Public Preview GeoFlow for Excel 2013

Apr 11th, 2013 Leave a comment

If you love geospatial data or geographical data, you should check the Public Preview of GeoFlow. It is an add in to Excel 2013 which renders 3D visualization of geographical and temporal data. The Beta version was announced late last year and I provided a review here. The Public Preview version is a much improved version, from performance, user interface, new features (such as chart) and many others. The Public Preview of GeoFlow was announced this morning and demoed at PASS BA Conference.

Without further ado, the download link is here:

http://www.microsoft.com/en-us/download/details.aspx?id=38395

Here is a teaser from me.

Stay tuned for more reviews from me!

If you are attending PASS BA Conference, check out Jason Thomas’ session (Geospatial Analysis Using Microsoft BI) on Friday (Apr 12, 2013). There may be a demo of GeoFlow there too.

I will also be doing Geospatial visualization talks at SQL Saturday 211 in Chicago on Apr 13, 2013 and SQL Saturday 201 in Orange County on Apr 20, 2013. I will show you how GeoFlow works live!

Official Links from Microsoft

GeoFlow Forum: Post and answer questions for the product team and community

GeoFlow Facebook Page: Share datasets, tours, and blogs

GeoFlow Product Page on Office.com: Download requirements, directs to Web Download page below

GeoFlow Download Page: Download the bits on Microsoft Download Center

Study in Human Behavior – Self Experiment

Mar 25th, 2013 Leave a comment

This article is a little different from my usual SQL Server topic. I have been interested in data visualization and sociology research. There are two common ways of conducting a study in Sociology or Human Behavior. Due to the increase of (online) social networking, people’s sentiments and opinions can be gathered / harvested for different types of social study. The PASS Business Analytics Conference in April 2013 offers a session on Social Text, Sentiment, and Tone Analysis by Ruben Pertusa Lopez and Paco Gonzales. The session reviews Natural Language Processing, text mining and data mining techniques to observe sentiment and tone analysis. Such study can be used for analyzing brand reputation, market predictions and automatic learning.

Another way of studying social behavior is through the traditional method of observation and/or questioning. This can be conducted towards a set of population or an individual, depending on the purpose. Both observation and questioning methods carry some pros and cons (source: Science Buddies). These are:

Observation
+ Generally most effective means for studying young children who are unable to respond to questions
+ More convenient and less intrusive for subject
+ Captures an individual’s genuine reactions
- Limited to collecting data about visible characteristics or behavior
- More time consuming to capture sufficient data for conclusions

Questioning
+ Valuable for collecting information on unobservable variables such as feelings, motives, perceptions, attitudes, etc…
+ Usually less time consuming method for capturing sufficient data
- The questions, or the mere fact of being questioned, may influence a subject’s responses.

Self Experiment Using Direct Questioning Method

Recently I have decided to undertake a human behavior experiment on myself to understand the complexity in the process of the questioning method. This includes defining variables to control, data gathering and generating data visualization that is easy to understand / to consume. So here it goes. (Please note that at a personal level, I learn quite a lot in the process)

Abstract: The human body is designed to respond to changing environmental stresses in a variety of biological and cultural ways. Altering lifestyle, biologically and in technology consumption, can affect productivity, social activities and mood in one week.

Method: Replacing two regular routines with another familiar routine at higher concentration. The two routines being replaced are public tweeting and chocolate consumption. The replacing routine is various physical exercises at more intense and regular intervals.

Caveats: People are unique; this experiment is highly unlikely to yield the same result on different people due to difference in biological and cultural difference. Larger population may provide better trend and eliminate data noise.

Note: The difficulty in this study is that I don’t live in vacuum. So there are other factors affecting mood, productivity and social interaction. So there are uncontrolled variables that have not been taken into account into the results. Even if I take the same experiment at another time, I may yield a different result. This could be due to past experience or other uncontrolled variables in life. Yes, human behavior research is not an easy process!

Wrap Up

The traditional way of conducting traditional survey or observation on human behavior can be complex. In the self-experiment I have conducted, it seems to be error prone and is limited to a small amount of data analysis. I look forward to learning the large-scale and automated way of gathering and analyzing social interaction at the “Social Text, Sentiment and Tone Analysis” at PASS BA Conference in April. Hope to see you there!

GeoFlow Beta – Untapping 3D Visualization

Mar 18th, 2013 Leave a comment

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:

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.
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.
It supports Column bar chart with a number of different shapes, as well as Bubble and HeatMap chart types.
It takes textual address such as Street, City, Zip, County, State and Country.
It also takes Longitude and Latitude points.
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.
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.
It uses Scene to capture Time play or a snapshot the geospatial visualization.
It has Tour management tool to capture multiple scenes.
It supports a number of pre-selected themes that shows geographical names, such as country name, country boundaries.
It relies on Bing to render the geospatial information.
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).
It supports multiple layer overlay which can be handy to analyze multiple data sets.
Its HeatMap feature can represent intensity based on aggregation value such as volume of sales.
It allows Annotation text boxes on the map.

What GeoFlow isn’t / doesn’t:

It does not support SQL Server Geography data type.
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).
It does not work without internet connection.
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!
It does not support elevation information. Another gem that came up at SQL Saturday 177.
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.
It does not support Chloropleth map.
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.
It does not allow custom map background.
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.
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.
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.
~~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.

PowerPivot 2013 Error: Data Model Damaged

Mar 14th, 2013 11 Comments

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 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.

Power View for Geographical Analysis with Excel 2013

Nov 26th, 2012 Leave a comment

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 - 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

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

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

Nov 22nd, 2012 Leave a comment

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”

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

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.

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.

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!

DAX Studio Review

Oct 26th, 2012 6 Comments

One of the enjoyable and privilege components of my work is discovering new things and learning new things, especially in becoming more productive. DAX Studio is a great example of this.

DAX Studio is a result of great hard work and dedication from BI experts; Paul te Braak, Marco Russo, Darren Gosbell and Angry Koala. The product can be downloaded from Codeplex. The version in this review is 1.0.1.

This article is a review of DAX Studio based on my experience at work. It does not encompass all usage scenarios under the sun for DAX, however it should provide some insights on the pros and cons of the product.

DAX Studio is an Add-In

DAX Studio is an add-in for Excel 2010 (I believe the product would also work for Excel 2013 as well, but I am yet to test this). After installing DAX Studio, you will see that the button becomes available on the Add-In tab

Upon clicking on the DAX Studio, a dialog box would appear, requesting for connection to either an existing PowerPivot (default) or a tabular model instance.

Connect To detects no PowerPivot model

It is quite interesting that when we connect to the PowerPivot model linked to the workbook, the connection will say “Microsoft_SQLServer_AnalysisServ…”. Unfortunately the name is cut off from the drop down box and it’s not resizeable. Similarly it is not easy to pick a tabular database from the drop down list when they have long name with the same first few characters as shown below.

Perspective is selectable from the dropdown list on the right near where the Server Name drop down box.

Similar to SSMS, the following information information is shown on the bottom left of DAX Studio window:

Server Name ($Embeded$ when connecting to a Power Pivot model)
SPID
Version

DAX Studio Shines

DAX Studio allows you to browse through the objects that you have defined on the model. The calculated measures are shown under the table that it is created, not grouped separately like in SSMS under “measures”. In the version that I am evaluating, the calculated measures are listed based on the creation time/date. This could be a good thing for some users. Unfortunately, in my case, I am implementing over a hundred of calculated measures (it’s for a specific Accounting report, hence lots of measures) within my model, making it harder to find the measure I want sometimes. But at the same time, when I’m still debugging the newest calculated measure, it’s really good as I can go to the bottom of the list and pick the last calculated measure that I want to test.

I also find that the ability to see the hidden fields or tables in DAX Studio is very powerful. Quite often, my query requires those hidden column or tables as part of calculation. In addition, it makes life so much easier in debugging what the hidden columns that were used as part of the calculation.

Based on my experience when using DAX Studio, there are two major aspects that I find very useful.

1. Performance of the DAX Studio when being used for querying

More often than not, I have a few pivot tables within one workbook. When I only need to inspect / debut a calculated measure, I wouldn’t want to refresh the whole pivot tables. So, with DAX Studio, I am able to perform a smaller set of unit testing independently from the rest of my workbook. I find that it’s a lot quicker for me to refresh the EVALUATE statement in DAX Studio than for the PowerPivot to refresh the data.

2. Analysing performance of queries

As part of unit testing, I am often is concerned with how long my query runs. I can go to the DMV tab and perform drag and drop on useful DMVs like:

select * from $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS

In addition to being able to see the different types of performance counter, DAX Studio also returns a message about errors and how long the query takes.

Let’s take a look at the Output generated by DAX Studio. The actions that can run on DAX Studio are:

Output query to Excel as a Table
Output query to Excel as a static data
Output query to Grid dialog box
Evaluate only (no output)
Clear Cache

There are also options on creating new sheet or overwriting existing sheet when Output query to Excel is chosen.

There is support for line numbers, saving the query and – most of all – syntax highlighting.

Where to from here?

As a BI Consultant working with Tabular Model and PowerPivot solutions on daily basis, I think DAX Studio is a superb companion. It helps in increasing developers productivity.

I think the product can be improved further. Here are some small areas of improvement which would be helpful (I’m talking based on my experience – if you share my view on areas of improvement, please vote or comment on the codeplex comment link).

1. More support for SSMS/VS short cut keys, e.g. changing case to upper case or lower case, Ctrl+K Ctrl+C to comment things

2. Keeping the column format as specified in the model. Even though the calculated measure is formatted as shown below, the output is not retaining the format.

Internet Total Sales format is Currency

The workaround for this is to use the “FORMAT” function in the DAX query, but this is not ideal.

3. Ability to sort the calculated measures – at the moment it’s in the order they’re created, which might not be the best.

4. Providing a warning dialog box – “You have unsaved query. Are you sure you wish to exit DAX Studio?”. I have been caught a few times accidentally closing DAX Studio instead of the Grid dialog box and lost some changes

5. Displaying DAX query plan

6. Adding integration to source control

7. Resizeable Database Name Dropdown Box.

Wrap Up

I have been using DAX Studio for over a month now and am very delighted that such product exists. This really helps in the development life cycle of Tabular Model and PowerPivot solutions. Is it overkill, given SSMS is available? No, querying PowerPivot via SSMS is not an option (not that I am aware of). Would there be more support for the product? Yes, I believe so. I would highly recommend downloading, using the product and providing feedback. The project is built and coordinated by BI experts in the world who are passionate and actively involved in implementing Tabular Model and PowerPivot solutions.

DAX Studio is a great tool for developing DAX query, unit testing and querying performance statistics. It is easy to use and can definitely help.

If you have any comments and feedback on DAX Studio, please post them on the Codeplex project so it can be tracked. I’m also happy to hear thoughts around the product or a similar one.

Power View Map in Excel 2013 Preview

Sep 4th, 2012 5 Comments

With Office 2013 preview launched in mid July 2012, there have been ubiquitous posts on the new Power View feature in Excel 2013. This is a very welcomed feature and I would sense that it will win Business adoption as an easy to use and “hopefully” affordable Self Service BI. Here’s my thought on a fairly early version of Power View feature in Excel 2013 Preview.

Power View

Since the first time Power View introduced in SQL Server 2012 and made available in SharePoint 2010, I’ve always been fan of it. With most tools if you have analytical mind and are great with visualization / design, most reports you’ll create will be useful and used – and in my opinion – Power View is definitely one of these tools.

Using the Australia Broadband Guarantee data set provided from http://data.gov.au/dataset/australian-broadband-guarantee/, I am able to quickly and painlessly create an interesting Power View Map in Excel 2013 that I can analyze the investment the Australian government has made, at country level and down to specific Suburbs / Postcodes. The map feature is currently not available in SQL Server 2012 RTM with SharePoint 2010. I do hope that it will be introduced as a Service Pack 1 to SQL Server 2012.

Sample Data and Map

The Australian Broadband Guarantee (ABG) was an Australian Government initiative designed to help residential and small business premises access high-quality broadband services regardless of where they were located. The program targeted premises unable to access commercial metro-comparable services, particularly those living in remote parts of Australia

More information on the project: http://www.dbcde.gov.au/__data/assets/pdf_file/0017/128204/Australian-Broadband-Guarantee-2010-11-Guidelines-July-2010.pdf

The dataset I’m using has a very straight forward data structure. My motive here is to see which areas have the funding gone to? Does the amount of funding yield many connections? Is there any isolated area that gets funding?

Zoomed at the Nation level:

Zoomed at a State level, with funding > $1 million filter: (hovering on one of the bubble gives me a descriptive tooltip)

I can zoomed in further to the actual postcode as the granularity of the location is postcode, as shown below:

Advantages and Disadvantages of Using PowerView Map

Although it’s still at its early stage as Office 2013 is only released in a Preview version, I consider Power View Map feature has the following advantages and disadvantages.

Advantages of PowerView in Office 2013 preview as at the time this post is written:

1. It integrates with Excel. So it allows for rapid prototyping and development.

2. No knowledge of Power Pivot is required. Power View can be created based on a table on a worksheet and it automatically creates Power Pivot data based on the table. Knowing Power Pivot and being able to configure relationship and data categories, will help for performance (so it seems based on my testing).

3. The map feature recognises longitude and latitude pairs which are easily attainable. It can also use information such as Suburb and State.

4. It uses Bing Map as the map layer, so there is no need to acquire ESRI shape files or converting to Spatial data type.

5. Performance of the Power View map feature is amazing. When plotting over 2000 locations, I could barely notice the wait time.

6. Most importantly, the ability to interactively zoom in/out the map – from world to road level – is fantastic. This is something that is not available in Report Builder or SSRS yet.

Disadvantages of PowerView in Office 2013 preview as of the time this post is written:

1. No support for Spatial Data type. So there is no user defined zone that can be layered on top of the map.

2. Location, Longitude and Latitude values are all needed. Location could be PostCode, City/Suburb or Country.

3. The Location category is US Centric. This meaning that if State is used as the Power View Map Location, and there is a State with the same name or abbreviation in US, it would point to US. As an example, “WA” is Western Australia in Australia (http://binged.it/QzFaDn), but it is also Washington in US (http://binged.it/QzF7HG); which is over 9,000 miles away or over 14,000 kilometers.

4. Unable to connect directly to the Analysis Services; i.e. it is required to import the data into Power Pivot first or Excel table, then create a Power View map based on the imported data.

There are a few areas that still don’t work properly. The application crashed a few times on me when converting the initial pre-built table created by Power View to a map, as well as when the column detects text (larger size) instead of numbers for aggregation. I think in time, these issues will be ironed out.

Wrap Up

Office 2013 seems to offer a number of nice features, especially for self-service BI solutions using Excel 2013. I have no doubt that small businesses will start adopting Excel 2013 more and more in the near future, for data analytics using the new readily available Power View feature in Excel.

Exposing SQL Server for GeoFlow

Wrap Up

Further Reading

Revisiting The “Not” List

New Features

Wrap Up

Further Reading

Further Reading / Research

Self Experiment Using Direct Questioning Method

Wrap Up

Further Reading

What GeoFlow is / does:

What GeoFlow isn’t / doesn’t:

Other GeoFlow Examples

Wrap Up

Further Reading

Wrap Up

Further Reading

1. Earthquake Last 30 Days

2. PASS Chapter and SQL Saturday Growth

3. Internet Sales by Customer Occupation

Wrap up

Upgrading Excel 2010 workbook with PowerPivot to Excel 2013

DAX Studio is an Add-In

DAX Studio Shines

Where to from here?

Wrap Up

Further Reading

Power View

Sample Data and Map

Advantages and Disadvantages of Using PowerView Map

Wrap Up

Further Reading

Follow me on Twitter

Upcoming and Past Events

Archives

Recent Posts

Categories