Microsoft Nods To PHP Community (Yet Again) With SQL Server Driver

Microsoft continued to cater to the PHP community last week, announcing a Community Technology Preview of SQL Server Driver for PHP 2.0.

For the first time, PHP developers can use PHP Data Objects (PDO) with the SQL Server driver.

"For PHP developers, this will reduce the complexity of targeting multiple databases and will make it easier to take advantage of SQL Server features (like business intelligence & reporting) as well as SQL Azure features (like exposing OData feeds)," said Microsoft's Ashay Chaudhary, program manager for the driver project.

The move was especially welcomed by developers working with Drupal, the popular open-source content management system that's written in PHP and powers Web sites around the world, including

"This driver allows you to install and run Drupal 7 using an SQL Server database, and it makes it possible to deploy Drupal on a full Microsoft stack (Windows Server, IIS, and SQL Server)," said a spokesman for Commerce Guys, an e-commerce company that works extensively with Drupal and worked on the driver project with Microsoft. In conjunction with Microsoft's announcement, the company presented a beta version of Drupal 7 running on SQL Server at the DrupalCon conference in San Francisco.

Chaudhary said the driver's support for PDO was developed as "a direct result of the feedback we received from the PHP community." Much of that feedback came in a survey Microsoft conducted last October. The new driver is the latest in a series of moves Microsoft has made to accommodate that PHP community.

The strategy seems to be working. "This is a great achievement, PDO support was much requested for the longest time by many in the PHP community, particularly Drupal folks," said one comment on the blog post that announced the new driver.

Another said, "WISP (Windows, IIS, SQL Server, and PHP) application development has been an interest of mine lately, and this definitely looks promising."

The driver CTP is available for download, with the final version expected later this year.

Posted by David Ramel on 04/28/2010 at 1:15 PM0 comments

Is Oracle's MySQL Move Really A Threat To Microsoft?

A lot of people believe that Oracle last week left no doubt that it will wield MySQL as a potent weapon to fight Microsoft for database market share. The company announced several new MySQL products at a conference in California and reaffirmed its commitment to the open-source software it acquired from Sun Microsystems earlier this year.

As reported by Reuters, "In a bid to woo customers from rival Microsoft Corp, Oracle Corp will boost investment in the widely used MySQL open-source database."

A ZDNet blogger said Oracle MySQL head honcho Edward Screven noted that "more customers deploy MySQL on Windows than on any other platform. That certainly gives Microsoft SQL Server a run for its money."

InformationWeek believes that Screven was "trying to lay apprehensions to rest" on the part of MySQL backers when he said: "We will make MySQL better. We plan to continue this level of investment. A lot of people questioned what motivation Oracle had in acquiring MySQL (as part of Sun Microsystems)."

In the ongoing database wars among the top vendors, this might give pause to database programmers who are choosing which technology to focus on, with Microsoft's SQL Server already being in second place behind Oracle's flagship product.

Or will it? Others aren't so sure.

Ken Hess wrote on that: "In essence, Oracle will continue on the same path with the commercial and community versions of MySQL just as MySQL AB and Sun did."

Or not!

He followed that up with:

"Uh huh. And I have some lovely beach front property in Arizona that I'd love to sell you. It isn't that I don't believe that Oracle will continue to support the MySQL Community version, it's that I don't think they'll continue to support it at the same level as they do the commercial version. The Oracle database doesn't need community support so why should MySQL?"

Expert analyst Laura DiDio, principal at ITIC, was even more skeptical when she told this site:

"Clearly the open-source community at large and the open-source developer community were hoping for specific reassurances from Oracle that it would continue to support, refresh and develop MySQL. Additionally, developers were also keen for Oracle to offer specific details regarding which MySQL features and functions, if any, will require a commercial license. The latter point is crucial for any organization building an in-house custom application or any third-party ISV developer contemplating whether to build a MySQL application or an application for Microsoft's SQL Server.

"Oracle did state it would continue to develop and promote MySQL to compete against rival Microsoft SQL Server, but many corporate developers, enterprises and industry watchers remain unconvinced and will take a wait and see approach before committing to put their development monies and R&D efforts behind MySQL. Oracle's announcement was long on promises and short on specifics on the all important open-source code vs. commercial licensing aspects of MySQL. Oracle still has a long way to go to quell developers' well-founded fears."

So, as with a lot of open-source issues, this one has polarized much of the IT community.

What do you think? Was it a stiff tilt at Microsoft or just a lot of smoke and mirrors? Weigh in here or send me an e-mail.

Posted by David Ramel on 04/19/2010 at 1:15 PM1 comments

New Training Kit For SQL Server 2008 R2

The buzz today is all about Visual Studio 2010 finally being released, of course, but you data hounds may be more interested in next month's launch of SQL Server 2008 R2.

Some of the more interesting features of R2 are the emphasis on "self-service BI" that comes through the new PowerPivot plug-ins for Excel and SharePoint and new Datacenter and Parallel Data Warehouse editions.

Speaking of the former: some users of the CTP version of PowerPivot for Excel were victims of a kind of April Fools' joke that I found out about when I was tinkering with PowerPivot and a new data visualization tool last week. PowerPivot wouldn't work for me. I discovered that a bug in the November CTP made the add-in expire on April 1. That made a lot of people quite irate. "You are really killing me here," said one. "This expiration is a complete disaster that really pulled the rug out from under us," said another.

Comments like that make me wonder about the wisdom of using prerelease software for real-world business apps in which you are heavily invested and dependent upon.

I got around the bug by following one user's advice and setting my PC's system clock back—and it worked! (Which reminds me, I have to set that back to normal….) I used that trick long ago in my youth when I was trying to, uh, borrow certain software packages for testing and evaluation purposes only, but somewhere along the line developers got wise to it and it no longer worked.

Anyway, if you really want to sink your teeth into R2, another Microsoft announcement today may just be your ticket: The SQL Server 2008 R2 Update for Developers Training Kit - April 2010 Update was released, according to an 11:16 a.m. blog post by Roger Doherty.

The training kit was first released in February. The April update features a bunch of new presentations, demos, hands-on labs and videos focused on Reporting Services. Last month's update tackled StreamInsight, which is described as "Complex Event Processing technology to help businesses derive better insights by correlating event streams from multiple sources with near-zero latency."

So get all trained-up on the new version and stay tuned for more details to be released during the PASS European Conference 2010 next week in Germany. Should be interesting.

What streaming insights do you have? What about tricks to let you use games and apps for evaluation purposes only? How has prerelease software victimized your business in the past? Comment here or send me an e-mail.

Posted by David Ramel on 04/12/2010 at 1:15 PM0 comments

New Data Visualization Tool Costs Nothing, Does A Lot

Continuing my exploration of new ways of using data from the cloud, I have found a nifty tool chock full of features that you wouldn't expect for its price: free!

It's called Tableau Public by its maker, Tableau Software.

It's called a step toward "the holy grail of data" by Microsoft's MSDN blog on Dallas, which is the new cloud data repository.

I previously wrote about how Microsoft's new PowerPivot tool for Excel lets you download huge amounts of data and present it in tables and charts. Tableau Public goes even further. Its data visualization options are incredibly extensive, letting you build all kinds of flashy interactive presentations.

Following the 1-2-3 steps of Open, Create and Share, you can even put these interactive visualizations into your blog or other Web page. It opens up countless story-telling scenarios for bloggers, journalists, researchers and students, examples of which are on Tableau's How It Works page. The site's Gallery page shows that Tableau is being used by companies such as The Wall Street Journal and CBS Sports.

In less than 10 minutes, I downloaded a free Dallas data feed--InfoUSA New, Out-Of-Business and Historical Businesses--and set up the following visualization of failed businesses near my home in Massachusetts. Even with such a simple data set, it's easy to see that businesses near the city of Brockton are struggling more than other areas, which jives with local knowledge. Tableau Public also lets you overlay various demographics. I chose population growth, which shows failed businesses tend to occur in areas with lower population growth, which is logical.

It took another couple of easy steps to generate the HTML to let me host the project right here.

This is an extremely bare-bones example, but you get the idea. Note the interactive options at the bottom (not all applicable to this example) that let viewers manipulate and even download the data.

Give it a try and let us know what you think. And please point out any interesting examples of data visualization that you've come across. Comment here or send me an e-mail.

Posted by David Ramel on 04/07/2010 at 1:15 PM1 comments

.NET Data Access In Web Apps 101

Ever wonder what all these new-fangled data-based technologies coming from Microsoft can do for you? Who can keep track of everything? There's ADO.NET, LINQ to SQL, ASP.NET MVC, Entity Framework and OData, to name a few. Not to mention the code names and name changes: Oslo is now SQL Server Modeling; Gemini is now PowerPivot; ADO.NET Data Services is now WCF Data Services; and of course, my personal favorite, ADO Data Services v1.5 is now Data Services Update for .NET Framework 3.5 SP1. The list goes on.

Well, for you Web devs, I found a great primer from last week's MIX10 conference in Las Vegas. It was in a session called Accessing Data in a Microsoft .NET-Connected Web Application.

A great service from Microsoft was putting the MIX10 sessions on video for those of us who couldn't go to Vegas because of outstanding warrants, etc. (JK; never been there).

Shayam Pather provides an excellent, step-by-step, hands-on demo starting out with the most basic .NET data access and ending with the new darling debutante, OData.

He writes some simple code, then looks at the patterns used in the code and shows you how to simplify/improve things by using new, "fancier" approaches and patterns.

For example, he starts with an empty ASP.NET MVC project in Visual Studio 2010 and quickly shows "the simplest way we can get data access going" with a connection string, select command, data reader and a little HTML.

After he does "the MVC dance" over this inline code, he points out the advantages of using model, view and controller to separate the data from the presentation from the mediator between the two. Any one can be changed without affecting the others.

He goes on to tackle a basic feature of the ADO.NET Entity Framework (ExecuteStoreQuery) that gets you into simple EF stuff by retrieving strongly typed objects out of your queries without having to deal with ORM or other modeling.

It proceeds in complexity from there. One of the better parts of the video shows how a model can reduce a complicated SQL query with an inner join, left outer join, filter, etc., to some simpler code that even I can understand (the best part is actually a joke commemorating St. Patty's Day: "An Irishman walks into a bar..."). At the same time, he shows how much easier it is to deal with the results of the query and bounce from one slice to another.

That, my friends, is what these new-fangled data-based technologies from Microsoft can do for you.

It can be intimidating to tackle new technologies and abandon the tried-and-true tools you've used for years. But when you do, the rewards can be great. Check out the video. (That's the only way you'll see the punchline of the Irish joke.)

Have you tried some of this new data stuff? Has it helped? Tell me your story. Comment here or send me an e-mail.

Posted by David Ramel on 03/25/2010 at 1:15 PM0 comments

Is The New 'Open' Microsoft For Real?

It was all about openness at Microsoft's MIX10 conference this week. What's going on with the notorious bastion of proprietary, locked-in software long known for holding out against the open-source barbarians clamoring at the Gates of Redmond?

Get it? "Gates" as in Bill? Maybe with him (almost) gone, the company is changing direction.

At one point during a live Channel9 broadcast from Las Vegas, a Microsoft exec, discussing open data, glanced nervously at the camera and wondered aloud how much of this stuff he could talk about. He didn't drop any bombshells, but he did allow as to how he was in continual contact with companies such as Google about working together on opening up standards and technologies.

Microsoft Software Architect Doug Purdy, in the Day 2 keynote, said, "Many of us strongly believe that OData is the mechanism by which we can get open data for the open Web, and we are extremely excited about the opportunities that we have with it."

(In a blog post, he said, "We recognize that it will 'take a village,' so-to-speak, to ensure that open data for the open Web becomes a reality.")

John Resig, founder of JQuery, said in the keynote: "It's a pure JavaScript library ... completely open source. We worked very closely with the Software Freedom Conservancy, a nonprofit, to host the jQuery project, and all this code is released and out there in the open, and we're really happy that Microsoft has really taken the opportunity to embrace it and work with us to create an excellent library."

Microsoft VP Scott Guthrie introduced another open initiative: "We're going to be talking about a new open source that we're sponsoring called the Orchard Project, which is a lightweight CMS and blogging engine built on top of ASP.NET MVC 2 all delivered under open source." (See video intro of Orchard here).

Andrew J. Brust blogged about the new openness. "This is not your father's Microsoft, it would seem," he said.

Visual Studio Magazine's Michael Desmond blogged on "Openness on Display at MIX 10."

Kathleen Richards reported on the open-source Silverlight Analytics Framework announcement.

Some aren't buying into this whole Microsoft openness thing. Tim Anderson wrote: "Why would Microsoft talk about such things? Arguably it is a kind of smokescreen, talking standards while busily promoting proprietary stuff like SharePoint and Silverlight."

What do you think? Is the company serious? What's behind all this talk of open standards and open source? Comment here or send me an e-mail.

Posted by David Ramel on 03/18/2010 at 1:15 PM1 comments

Coding -- Art Become Drudgery

A recent post at The Reinvigorated Programmer blog titled "Whatever happened to programming?" stirred up a hornet's nest of comments on Slashdot, Reddit and other sites. Mike Taylor lamented the days of yore when he was writing games in BASIC and C and experimenting and creating and having fun.

"That was then," he said. "Today, I mostly paste libraries together.  So do you, most likely, if you work in software.  Doesn't that seem anticlimactic?"

I'm not in the biz, per se, but it does seem programming has lost something in these days of commoditized offshore code factories, rigid regimentation and plug-and-play developers populating dreary cubicle farms.

Where is the élan, the elegance, the art? Where are the brilliant mavericks, the rock stars?

I've often wondered if most programmers felt the same way. Looking at the hundreds of comments, there were of course many in agreement, such as this:

"I couldn't agree more. Writing software is not fun anymore, it's a job. The type of job that is occupied by people who don't even like computers, they're 9 to 5 programmers."

But a surprising number take task with Taylor, saying their jobs are creative and rewarding. Here's an example:

"I've found myself taking great pleasure from some of the higher level stuff I've done over the last 11-ish years as a pro."

Many posters suggested turning to open-source projects to regain whatever has been lost.

What about database-oriented programmers? Is that niche even more lacking in lustre? How do you feel about the state of the art in general or your job in particular? Comment here or send me an e-mail.

Posted by David Ramel on 03/10/2010 at 1:15 PM4 comments

TechFest 2010: Could This Stuff Really Happen?

You may not be able to attend yourself, but you should keep track of news coming out of Microsoft's TechFest 2010 if you're into high-tech futurism.

Since 2001, the invitation-only event has showcased futuristic projects in development at Microsoft Research. While some of these far-out projects fall by the wayside of the forgotten, others eventually bear mainstream fruition.

For example, in the 2003 event, Microsoft Technical Fellow Jim Gray (who later tragically disappeared at sea) presented his spatial search project titled "There Goes the Neighborhood! Spatial (or N-Dimensional) Search in a Relational World." Of course, SQL Server 2008 introduced spacial support.

Also in 2003, a project called MediaFrame was introduced, promising to allow "folks to sort, annotate and organize digital photos and video, as well as transmit images to devices and screens around a home. The project includes face-recognition technology to help sort photos." Sound familiar?

The Tablet PC and software that combines multiple photos into one panoramic view are other well-known examples of TechFest prototypes come to life.

They still haven't gotten that "Minority Report" thing down, though. Tom Cruise was manipulating 3D, holographic images with his hands way back in 2002. This year, you can check out a video of Craig Mundie "demonstrating a 3-D climate-change model that's controlled with gestures and voice commands." They're getting closer all the time.

What do you foresee in the future? What are your favorite predictive hits and misses? Comment here or send me an e-mail.

Posted by David Ramel on 03/03/2010 at 1:15 PM0 comments

Database Wars: Who Has the Edge?

I found all kinds of interesting stuff in Information Technology Intelligence Corp.'s recent 2010 Database Deployment Trends Survey, which predicts more companies will be upgrading their infrastructure this year, thus allowing database vendors a rare chance to persuade customers to switch their RDBMS allegiances.

Usually, the cost and hassle of switching database systems is prohibitive.

In a blog posted Thursday titled "Database Competition Heats Up," ITIC wrote:

A wholesale switch from one platform to another requires significant capital expenditure monies. Additionally, the business must also invest a lot of time and energy in converting to a new platform, testing new applications, rewriting scripts and re-training DBAs and getting them certified on the new environment. For CIOs, CTOs and IT departments this prospect has roughly the same appeal as having root canal without Novocain.

But with companies scrambling to remain competitive after two and half years of cutting costs amid the economic downturn, they will be upgrading their systems and possibly switching their vendors, ITIC said.

The research firm concluded: "What will distinguish the DBMS market this year is that the always intense and vociferous vendor rivalries will heat up even more over the next 12 months."

So I wondered which of the Big 4 database vendors have an advantage going forward, specifically related to the developer community. So I asked.

Here is the reply from ITIC principal Laura DiDio:

IBM and Microsoft are very well positioned at present and over the next 12 months. IBM's DB community is solid, stable and very loyal.

The fact that 72 percent of survey respondents said they hadn't switched DB platforms in the last three years and the fact that it's harder for very large enterprises in market segments like banking, financial and insurance (traditional IBM strongholds) to switch because of the legacy investment, bodes well for IBM. Many of these enterprises have mature DB environments that are stable for many years.

In Microsoft's case the improvements to the SQL Server 2008 platform which make it more enterprise ready and the fact that Microsoft has a very strong developer community, combined with a vibrant reseller channel, puts Microsoft in a good position to expand its presence into SME and enterprise organizations.

As for Oracle, the developer community -- particularly Open Source developers -- are watching and waiting to see if Oracle will remain true to its word and continue to support and develop for MySQL and integrate that platform into their other offerings as they've promised.

Oracle also faces other challenges. Their many acquisitions over the last three years could prove to be an unwelcome distraction and cause the company to not provide enough support to its developers. That said, on the plus side, Oracle's most recent second quarter financials were very strong. This was largely attributable to strong demand for new software license renewals and maintenance plans.

In the most recent quarter, Oracle reported revenue of $5.858 billion. Of that figure, $3.247 billion was software maintenance revenue and $1.653 billion came from new software licenses. Common sense dictates that if so many of Oracle's customers are upgrading their DB licenses, then they'll be upgrading their applications as well. So, while Oracle does have some specific challenges that IBM and Microsoft will undoubtedly attempt to exploit, it remains very strong.

Sybase has a strong following but it lacks the deep pockets of IBM or Microsoft to market on the same scale. And marketing and positioning will be key elements of DB success in 2010. It's going to be a real dogfight for market share and licensing renewals.

Are you planning an upgrade? Might you switch vendors? Who looks good to you? Comment here or send me an e-mail.

Posted by David Ramel on 02/22/2010 at 1:15 PM2 comments

SQL Saturdays: Free, Fun, Informative

To start up SQL Server Management Studio faster, you can turn off Online Help, error/usage reporting and certificate revocation checking. It also helps to exclude the SqlWB.exe and Ssms.exe executables from anti-virus and anti-spyware tools.

Those are all tips from SQL Server MVP Aaron Bertrand, who presented at the recent SQL Saturday #34 Boston 2010 event (actually held in Waltham, Mass.).

Those are all beginner tips, of course, but dozens of other presentations targeted intermediate and advanced users, covering diverse subjects such as career tips, storage design and SQL injection attacks.

SQL Saturdays are held across the country, and chances are one is coming to a  city near you.

The SQL Saturday brand name and Web site is licensed free of charge to local groups that want to organize an event, courtesy of Andy Warren, Brian Knight and Steve Jones, who started them off in 2007. The all-day events are a great way to network with peers and learn from experts.

For Adam Machanic, head of the local SQL Server user group and organizer of SQL Saturday #34 (also called New England Data Camp), the payoff for all of his hard work is the community participation.

"It was great to see so many new faces--people I've never seen come out to the user group--show up on a Saturday morning for a full-day, community-driven event," Machanic said of the data camp, which attracted some 350 attendees. "Some of these people even agreed to show up at 7 a.m. to assist with setup. For someone to do that without any prior interaction with me or the local SQL Server community is truly amazing and I think really illustrates the power and community nature of these events."

And community-driven certainly doesn’t mean quality-lacking. He pointed out that an entire track was dedicated to performance tuning and three SQL Server MVPs and two Microsoft employees were on the presenter roster.

To see for yourself, many of the session materials can be downloaded.

The next event is #33 in Charlotte on March 6 (yes, after #34; I forgot to ask why the events’ numbers have no correlation to the calendar schedule). Fifteen more are scheduled through August. Check them out.

Do you have any experience with SQL Saturdays? Or any other similar community-driven events? Comment here or send me an e-mail.

Posted by David Ramel on 02/11/2010 at 1:15 PM4 comments

Is Security Worth Risking Your Job?

It's 1:30 a.m., you're sweating bullets and your head is pounding. You can feel the stress wracking your body as you try to concentrate. The code-complete deadline for your part of the module is coming up, and the bean-counters are on everyone's back. Product has to ship on time -- period.

You've almost got that last, thorny problem licked, but testing all the different security scenarios with this new cloud-based database-conversion tool takes soooo much time. A potential "connection string pollution" vulnerability has popped up in the back of your mind, but you're unsure how dangerous it is -- or even if it's possible. It's a brand-new threat. It will take forever to investigate.

What the heck. No one will ever find it. You can't be the one holding things up -- again. There are thousands of talented coders out there who would jump at the chance to slide into your job at half your salary. You've got mouths to feed. Let QA worry about it.

Could this happen? Has it? Who's to blame for the sorry state of application security these days?

Here's a chilling quote:

"Security is not something app developers have prioritized in the past. Their focus has been getting a product that has a competitive edge in terms of features and functionality to market as quickly as possible. That's not a criticism, it's just a factor of commercial priority."

That just happens to have come from the world's biggest app dev shop, shipping more "product" than anyone else: Microsoft. David Ladd, principal security program manager at Microsoft, said it in a news release earlier this week announcing that the company is "helping the developer community by giving away elements of its Security Development Lifecycle process."

It couldn't come at a better time. "Today, in the middle of the worst economic downturn in thirty years, information security has an enormously important role to play," reads the 2010 Global State of Information Security Survey from PricewaterhouseCoopers.

"Not surprisingly, security spending is under pressure," the report states. "Most executives are eyeing strategies to cancel, defer or downsize security-related initiatives."

But the report is generally optimistic, finding that "Global leaders appear to be 'protecting' the information function from budget cuts -- but also placing it under intensive pressure to 'perform.' "

That makes sense to me. It would have to be the short-sighted executive indeed who would risk all the associated costs of a data breach just to meet a deadline. How bad can those costs be? Just ask TJX.

Then again, that executive has mouths to feed, and "commercial priority" and "intense pressure to perform" could cloud judgment.

What about your shop? Where do the priorities lie? Know any juicy stories about security breaches caused by economic pressures? I'd love to hear about it. Comment here or send me an e-mail. But for goodness' sake, don't put any sensitive information in it. There are bad guys out there.

Posted by David Ramel on 02/04/2010 at 1:15 PM0 comments

Hands-on PowerPivot: To the Cloud

I previously wrote about an interesting demonstration that involved a 44-milliion-record data set imported into PowerPivot, Microsoft's coming "self-service business intelligence" add-in for Excel 2010 and SharePoint 2010.

Connecting to a cloud data service, importing a feed into Excel and culling BI info from the data is remarkably easy. You can try it for free with the Microsoft Office 2010 Beta (requires registration) and PowerPivot for Excel 2010.

Here's how.

Click on the PowerPivot tab in the main Excel menu, then click on the PowerPivot window, Launch button. It will take a few moments to load, displaying the message: "Preparing PowerPivot environment, please wait."

Now in the PowerPivot window, click on From Data Feeds under the Home menu item. You are presented with two options: From Reporting Services and From Other Feeds. Click on the latter. This brings up the Table Import Wizard (Fig. 1), where you input the data feed URL.

Figure 1. Table Import Wizard. (Click image to view larger version.)

You can find online data feeds with a Web search. I used the Open Government Data Initiative, specifically the Crime Incidents data set under the District of Columbia "Container" (Fig. 2).

Figure 2. Crime Incidents data is found in the District of Columbia container. (Click image to view larger version.)

Interestingly, clicking on Advanced brings up a window with "Provider: Microsoft Data Feed Providers for Gemini" as the only option. This is one of several leftover references to the former code-name of the project, Gemini. The Advanced button brings up settings for Context, Initialization, Security and Source that adjust the connection string, along with a Test Connection button.

If you're not into Advanced settings, just paste in the source data set URL, click the Test Connection button, and a window comes up with "Test connection successful" (in a window titled "Gemini Client"; see Fig. 3).

Figure 3. Testing the connection with the cloud data. (Click image to view larger version.)

Click on Next. The Table Import Wizard shows a window where you can "Select Tables and Views" if you just want to download parts of the feed (Fig. 4). There is also a Preview & Filter button that lets you see the data as it will appear and deselect different columns to tailor what you want to download.

Figure 4. Selecting Tables and Views you want to work with. (Click image to view larger version.)

Click Finish. It takes a few seconds to import, then displays "Success"; under Status it shows you how many rows transferred (368 in this case).

Click close, and a PowerPivot window comes up with all the data in tabular format (Fig. 5).

Figure 5. Time for some PowerPivot magic. (Click image to view larger version.)

Click on PivotTable, and you are presented with various options for the number/type of tables to display, with or without charts, vertical or horizontal, and so on. I chose two vertical charts. You are asked whether you want to "Insert Pivot into New Worksheet or Existing Worksheet." Placeholders for charts come up, with a "Gemini Task Pane" (there's that code-name again) that lets you choose which fields to add to your charts, along with options for vertical and horizontal slicers, report filter, legend fields, axis fields and values.

I wanted to see which parts of D.C. are the most dangerous and when, so I chose Offense, Shift and Ward fields from the Crime Incidents data feed (Fig. 6).

Figure 6. Selecting Offense, Shift and Ward fields in the Crime Incidents table.

I then set up tables and charts to show crimes by Ward and by Shift, and with a simple formula constructed a pie chart displaying what percentage of crimes were committed during each shift. My resulting "BI" informs me that most crimes (38 percent) are committed during the Evening shift and most crimes occur in Ward 2 (Fig. 7).

Figure 7. Crimes by Ward and Shift, with a handy pie chart . (Click image to view larger version.)

There you go: practical, hands-on advice from the cloud, presented here free for your personal safety. Don't mess around in D.C.'s Ward 2 during the Evening police shift.

What danger areas are you avoiding these days? Comment below or e-mail me.

Posted by David Ramel on 02/01/2010 at 1:15 PM0 comments

Upcoming Events