Blog – Page 65 – Prologika

Geocoding with Power View Maps

November 29, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

As I wrote before, Power View in Excel 2013 and SharePoint with SQL Server 2012 SP1 supports mapping. The map region supports geocoding and it allows you to plot addresses, countries, states, etc, or pairs of latitude-longitude coordinates. The key for getting this to work is to mark the columns with appropriate categories.

Using latitude-longitude

If you have a SQL Server table with a Geography data type, you can extract the latitude and longitude as separate columns.

SELECT SpatialLocation.Lat, SpatialLocation.Long FROM Person.Address

Once you import the dataset in PowerPivot, make sure to categorize the columns using the Advanced tab.

The map region doesn’t support grouping on latitude-longitude so you can’t just place them in the Latitude-Longitude zones and expect it work. Instead, you have to add another field, such as address or both the Latitude-Longitude combination to the Location field. The map groups on the Location zone but uses the Latitude and Longitude to place the points.

Address geocoding

If you don’t have Latitude-Longitude, the map is capable of geocoding full addresses. Again, the trick here is to categorize the FullAddress column as Address. However, if you have invalid addresses, you’ll find that the map won’t show them. Instead, categorize the column as Place, which you can find in the More Categories section (thanks to Sean Boon from the Reporting Services team for the tip).

The map passes to Bing the fact that the field is mapped as Address so it should plot whatever we get back from Bing. The Bing Maps web experience isn’t identical to the API as you can’t pass the Address hint to Bing in the web experience. The Place category is more liberal in terms of what it will attempt to plot.

Book Review “Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model”

November 26, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

I’ve recently had the pleasure to read the book “Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model” by Marco Russo, Alberto Ferrari, and Chris Webb. The authors don’t need an introduction and their names should be familiar to any BI practitioner. They are all well-known experts and fellow SQL Server MVPs who got together again to write another bestseller after their previous work “Expert Cube Development with Microsoft SQL Server 2008 Analysis Services”. The latest book was published about five months after my book “Applied Microsoft SQL Server 2012 Analysis Services: Tabular Modeling”. Although both books are on the same topic, we didn’t exchange notes when starting on the book projects. In fact, I was well into writing mine when I learned on the SSAS insider’s discussion list about the trio’s new project. Naturally, you might think that the books compete with each other but after reading Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model” I agree with Marco and Chris that the books actually complement each other pretty well.

A central theme of my book is the continuum of Self-service, Team, and Organizational BI. I felt that it is very important to show how Tabular addresses the needs of both business users and BI pros. Indeed, the Tabular journey can start very unassuming, perhaps with a business user creating a simple personal model, gains popularity and evolves to a deployed model shared by teammates, and finally to a corporate model that is provisioned and sanctioned by IT. Because of this, the first part of the book covers PowerPivot for Excel, the second covers PowerPivot for SharePoint, and the third part covers Analysis Services Tabular. Since my book naturally targets different reader audiences (business users, power BI users, and BI pros), I felt that it was imperative to lower the learning curve as much as possible, such as providing step-by-step instructions for the exercises and video tutorials. Writing a book that targets such a broad base is not easy. To make sure that the book will be well accepted, I had readers who represented each of these groups review the manuscript and provide feedback.

On the other hand, Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model focuses on the professional side of Analysis Services Tabular and targets mainly BI pros. More than half of the book is devoted on DAX and you’ll be hard pressed to find a better coverage on this topic (a note to myself that DAX deserves more attention if I ever write a revision). Besides DAX, Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model covers equally well other aspects of Tabular and the author’s real life experience shows through. My favorite chapters are Chapter 11 “Data Modeling in Tabular” and Chapter 12 “Using Advanced Tabular Relationships”.

All in all, any serious BI pro willing to learn Tabular should have this book on the shelf… I hope next to mine.

SQL PASS 2012 Day 1 Announcements

November 8, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

I hope you watched the SQL PASS 2012 Day 1 Keynote live. There were important announcements and I was sure happy to see BI being heavily represented. For me, the most important ones were:

The availability of SQL Server 2012 Service Pack 1

For some reason, this announcement went without being applauded from the audience although in my opinion it was the most important news from the tangible deliverables. First, I know that many companies follow the conventional wisdom and wait for the first service pack before deploying a new product. Now the wait is over and I expect mass adoption of SQL Server 2012. At Prologika, we’ve been using SQL Server 2012 successfully since it was in beta and I wholeheartedly recommend it. Second, SP1 is a prerequisite for configuring BI in SharePoint 2013, as I explained previously. Indeed, I downloaded and run the setup and I was able to continue the SharePoint 2013 PowerPivot configuration. BTW, the build number of SP1 is 11.0.2100.60.

Note If you’re configuring PowerPivot for SharePoint 2013, you must also install a PowerPivot for SharePoint 2013 add-in (there is a new installer package called spPowerpivot.msi) in order to get the upgraded version of the PowerPivot Configuration Tool for SharePoint 2013. If you open the RTM version of the PowerPivot Configuration Tool for SharePoint, it will promptly complain that it doesn’t know a thing about SharePoint 2013 and redirect you to this page. Unfortunately, at this time, the link on this page points to the Community Technology Preview of the SQL Server 2012 SP1 Feature Pack and the whereabouts of the official SP1 release of the feature pack are not known (the Feature Pack was published with an incomplete list of files). I downloaded and ran the CTP version of and then ran the PowerPivot Configuration Tool for SharePoint 2013. It appears that the CTP version did a respectable job and all it was capable of successfully configuring PowerPivot for SharePoint. However, please wait for the official release of the SQL Server 2012 Feature Pack to avoid issues.

Power View for Multidimensional – OK, the cat is out of the bag on this one and Amir showed a demo. As a participant of the CTP program Power View for Multidimensional, I’m very happy about it. That’s all I can say at this point while waiting for the public technology preview. Unfortunately, Power View for Multidimensional didn’t make it to SP1 and it’s not known at this point when and how it will ship. But if you have multidimensional cubes (and who doesn’t) the wait will be worthwhile I promise.
Updatable Columnstore Indexes in SQL SERVER.NEXT – This a good news for users of columnstore indexes that will avoid dropping and recreating the indexes. This will be especially useful for columnstore indexes built on top of large fact tables, such as in the scenario I described here.
Hekaton – Plans to ship a long-due in-memory OLTP technology in SQL Server.NEXT.
Polybase – Another new technology slated for the next release of SQL Server 2012 Parallel Data Warehouse (expected in first half of 2013) that will allow you to run T-SQL queries joining relational data residing in PDW and Hadoop data. I guess this is the materialization of the David DeWitt’s Enterprise Data Manager idea that he talked about in his 2011 PASS presentation. I’m looking forward to his sequel which I suppose will go in details on this topic. Did we run out of cool names from the animal kingdom to succeed Hadoop, Mahoot, Pig, etc? I guess will find out in David’s talk.

Here is the list of the forthcoming live sessions.

UPDATE 11/8/2012

Here is a direct link to the release build of the PowerPivot Configuration Tool for SQL Server 2012 SP1.

Installing HDInsight Server for Windows

November 1, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

As you’ve probably heard the news, Microsoft rebranded their Big Data offerings as HDInsight that currently encompasses two key services:

Windows Azure HDInsight Service (formerly known as Hadoop-based Services on Windows Azure) – This is a cloud-based Hadoop distribution hosted on Windows Azure.
Microsoft HDInsight Server for Windows – A Windows-based Hadoop distribution that offers two main benefits for Big Data customers:
- An officially supported Hadoop distribution on Windows server – Previously, you can set up Hadoop on Windows as an unsupported installation (via Cygwin) for development purposes. What this means for you is that you can now set up a Hadoop cluster on servers running Windows Server OS.
- Extends the reach of the Hadoop ecosystem to .NET developers and allows them to write MapReduce jobs in .NET code, such as C#.

Both services are available as preview offerings and changes are expected as they evolve. The Installing the Developer Preview of Apache Hadoop-based services on Windows article covers the setup steps pretty well. I decided to set up HDInsight Server for Windows by installing the Microsoft Web Platform Installer on my Windows 8 laptop.

Note Initially, I planned to install HDInsight Server for Windows on a VM running Windows Server 2012 Standard Edition. Although the installer completed successfully, it failed to create the sites and shortcuts to the dashboards (Hadoop Name Node, Dashboard, and MapRaduce). This was probably caused by the fact that server was configured as a domain controller. There is an ongoing discussion about this issue on the Microsoft HDInsight forum.

The Windows 8 setup failed to create the shortcut to the dashboard. However, the following steps fixed the issue:

1. Open up an Administrator PowerShell prompt and elevate the execution policy of the PowerShell to accept scripts.

PS:> Set-ExecutionPolicy RemoteSigned

2. Navigate to the C:\HadoopFeaturePackSetup\HadoopFeaturePackSetupTools folder:

cd C:\HadoopFeaturePackSetup\HadoopFeaturePackSetupTools

Install HadoopWebApi

.\winpkg.ps1 ..\Packages\HadoopWebApi-winpkg.zip install -CredentialFilePath c:\Hadoop\Singlenodecreds.xml

Install the dashboard

.\winpkg.ps1 ..\Packages\HadoopDashboard-winpkg.zip install -CredentialFilePath c:\Hadoop\Singlenodecreds.xml

This should create the shortcuts on the desktop and you should be able to navigate to http://localhost:8085 to access the dashboard.

From here, you can open the Interactive Console and your experience should be the same as Windows Azure HDInsight Service. David Zhang has a great coverage of how you can use the Interactive Console in his video presentation “Introduction to the Hadoop on Azure Interactive JavaScript Console”.

BTW, HDInsight Server installs a set of Windows services corresponding to the UNIX daemons when Hadoop is installed on UNIX.

Hadoop and Big Data Tonight with Atlanta BI Group

October 29, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

Atlanta BI Group is meeting tonight. The Topic is Hadoop and Big Data by Ketan Dave and our sponsor is Enterprise Software Solutions.

With wide acceptance of open source technologies , Hadoop/Map Reduce has become a viable option when it comes implementing the 100 of Terabytes to Petabytes of Data solutions. Scalability, Reliability , Versatility and Cost benefits of Hadoop based system is replacing traditional approach of data solutions. Microsoft has partnered with Hadoop vendors, have recently made announcements to make data on Hadoop accessible by Excel, easily linked to SQL Server and its business intelligence, analytical and reporting tools for business intelligence and managed through Active Directory.

I hope you can make it!

SharePoint 2013 and SQL Server 2012

October 26, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

As I mentioned before, Microsoft released SharePoint 2013 and Office 2013 and the bits are now available on MSDN Subscriber Downloads. I am sure you are eager to try the new BI features. One thing that you need to be aware of though is that you need SQL Server 2012 Service Pack 1 in order to integrate the BI features (PowerPivot for SharePoint and SSRS) with SharePoint 2013. If you run the RTM version of SQL Server 2012 setup, you won’t get too far because it will fail the installation rule that SharePoint 2010 is required. That’s because the setup doesn’t know anything about SharePoint 2013 and the latest release includes major architectural changes.

Then the logical question is where is SQL Server 2012 SP1 now that is a prerequisite for SharePoint 2013 BI? As far as I know there isn’t a confirmed ship date yet but it should arrive soon. I’d suspect Microsoft to announce it at PASS.

Business Intelligence on Surface

October 26, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

Microsoft has started shipping the cool Surface RT tablets this week with Surface Pro to follow in January. Naturally, a BI person would attempt BI in Excel only to find that BI and Power View (no Silverlight support) are not there as Kasper explains. It’s important to know that the RT version of Surface is limited to native Windows 8 applications only, it comes preinstalled with Office 2013, and you can’t install non-Windows 8 applications. If you are interested in BI or running non-Windows 8 apps, you need the Pro version. This means that you and I need to wait until January next year so no Surface for Christmas.

Windows 8 Hyper-V Support

October 13, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

I installed Windows 8 shortly it was released and I’ve been running it for almost two months now. I’m still in a learning mode to teach old dog new tricks. I have a few gripes here and there, such as the need for two screens (start screen and desktop), the absence of the start button and its search, the quest for dumbing down the OS, and the fact that my Quickbooks PDF converter doesn’t work anymore.

However, I do like the performance boost (Windows 8 really flies on a solid state disk). I also discover gems here and there and one of them is the Hyper-V support. For those of you who need to run virtual machines, such as to test prerelease bits or run other guest operating systems, you know that in the pre-Windows 8 era you had to rely on either VirtualBox or VMWare because Virtual PC wasn’t capable of running 64-bit guest Windows (required for SharePoint for example). Now, Hyper-V is built in Windows 8 and installing it is a snap. Steven Sinofsky wrote a nice coverage of Windows 8 Hyper-V in his blog “Bringing Hyper-V to Windows 8” which includes also a short video of how to install it. Trust me, you’ll really appreciate how simple the install process is.

Anticipating the forthcoming release of SharePoint 2013, today I stood up a Windows Server 2012 VM using Hyper-V on a Windows 8 host and I loved the experience. There is no need to install extensions to enable keyboard and mouse integration, or to go through bizarre steps to resize the virtual disk in order to free up more space. For example, expanding the disk to free up more space is a simple two-step procedure:

Use the Hyper-V manager to edit and expand the virtual disk.
Start the VM. Assuming a Windows guest OS, use the Disk Manager to extend the volume to include the newly allocated space.

Tip If you plan to stood up a VM for BI testing, plan for at least 40 GB virtual disk to install Windows Server 2012, SQL Server 2012, and SharePoint. This is where a second disk (I highly recommend a solid state disk) comes handy.

Office 2013 and SharePoint 2013 Go Live

October 12, 2012/0 Comments/in Blog/by Prologika - Teo Lachev

I have to admit this came as a surprise to me as I expected them to go live next year. Microsoft just announced that Office 2013 and SharePoint 2013 went live. Time for celebrating, learning, and adopting these great technologies!

Check my coverage about what’s new in Office and SharePoint 2013 BI.

Prologika Newsletter Fall 2012