About Me

I now work for Microsoft Federal in Chevy Chase, MD.

Dedicated to providing customer-driven, results-focused solutions to the complex business problems of today... and tomorrow.

At SQLTrainer.com, LLC  we understand that the technical challenges faced by businesses today are much greater in both scope and complexity than they have ever been. Businesses today are faced with understanding both local IT infrastructures as well as cloud-based technologies.

What is SQLTrainer.com?

Founded in 1998 by Ted Malone, SQLTrainer.com is a technical consulting, training and content development firm dedicated to the following core principles:

  • Technology Alone is NOT the Answer! - Implementing a particular technology because it is interesting or "cool" will not solve customer problems.
  • Technology Solutions do NOT need to be Overly Complex! - Many times developers and technical practitioners will attempt to build the most clever solution possible. While this serves to stroke the egos of those involved, it doesn't provide a maintainable solution.
  • Consultants Should be Mentors First! - When looking to hire an external consultant, businesses should look to the consultant who's willing to train themselves out of a paycheck.

Why the name, SQLTrainer.com?

SQL (pronounced See-Quell) stands for Structured Query Language, which is at the heart of every modern-day relational database system. Since many technology solutions today rely on some form of database storage or interaction, it was only logical to find a way to incorporate SQL into the name of the organization. Given that one of our core principles is to be a mentor/training above everything, the name SQLTrainer made sense. Since we also wanted to represent our embracing of the cloud, it seemed logical to add the ".com", referring to the biggest "cloud" of them all.

Live Feeds

Wednesday, April 16, 2014 1:00:00 PM

At yesterday’s Accelerate your insights event in San Francisco, we heard from CEO Satya Nadella, COO Kevin Turner and CVP Quentin Clark about how building a data culture in your company is critical to success. By combining data-driven DNA with the right analytics tools, anyone can transform data into action.

Many companies, and many of our customers, are already experiencing the power of data - taking advantage of the fastest performance for their critical apps, and revealing insights from all their data, big and small.

Since SQL Server 2014 was released to manufacturing in April we’ve seen many stories featuring the new technical innovations in the product.  In-memory transaction processing (In-Memory OLTP), speeds up an already very fast experience by delivering speed improvement of typically up to 30x.  Korean entertainment giant CJ E&M is using In-Memory OLTP to attract more customers for its games by holding online giveaway events for digital accessories like character costumes and decorations soon after each game is released.   When it ran tests in an actual operational environment for one of its most popular games, the results were that SQL Server 2014 delivered 35-times-faster performance over the 2012 version in both batch requests per second and I/O throughput. 

SQL Server 2014 also delivers enhanced performance to data warehouse storage and query performance – NASDAQ OMX is using the In-Memory Columnstore for a particular system which handles billions of transactions per day, multiple petabytes of online data and has single tables with quintillions of records of business transactions.  They have seen storage reduced by 50% and some query times reduced from days to minutes. 

Lufthansa Systems is using the hybrid features of SQL 2014 to anticipate customer needs for high-availability and disaster-recovery solutions.  It has piloted the combined power of Microsoft SQL Server 2014 and Windows Azure has led to even faster and fuller data recovery, reduced costs, and the potential for a vastly increased focus on customer service and solutions, compared with the company’s current solutions.

Growth in data volumes provides multiple challenges and opportunities.  For executives and researchers at Oslo University Hospital providing ease of access to data is important.  Using Power BI for Office 365, they can analyze data in hours rather than months, collaborate with colleagues around the country, and avoid traditional BI costs.  For Virginia Tech the data deluge presents challenges for researchers in the life sciences where new types of unstructured data types from gene sequencing machines are generating petabytes of data.  They are using the power of the cloud with Microsoft Azure HDInsight to not only analyzing data faster, but analyzing it more intelligently and which may in the future provide cures for cancer.  For The Royal Bank of Scotland handling multiple terabytes of data and an unprecedented level of query complexity more efficiently led them to use the power of the Analytics Platform System (formerly Parallel Data Warehouse).  As a result, it gained near-real-time insight into customers’ business needs as well as emerging economic trends, cut a typical four-hour query to less than 15 seconds, and simplified deployment. 

Many customers are getting benefits from individual technologies, but Warner Brothers Games are using multiple BI technologies to provide a true global enterprise vision into metrics for executive management.  They have used SQL Server to analyze structured data from finance and sales, HDInsight to analyze large amounts of unstructured data, such as social data and player trends, and SharePoint and the Power BI tools in Excel surfacing the data to executive management. The organization has gained new insights which helps drive new business strategies – you can watch the video here.

Whether you’ve already built a data culture in your organization, or if you’re new to exploring how you can turn insights into action, try the latest enhancements to these various technologies: SQL Server 2014, Power BI for Office 365, Microsoft Azure HDInsight, and the Microsoft Analytics Platform System.

Wednesday, April 16, 2014 8:30:00 AM

Yesterday we talked about how we are delivering real-time performance to customers in every part of the platform.  I’m excited to announce another example of where we are delivering this to customers in conjunction with one of our partners. Microsoft and Hewlett Packard broke two world records in TPC-H 10 Terabyte and 3 Terabyte benchmarks for non-clustered configuration for superior data warehousing performance and price-performance. Each of the world records showed SQL Server breaking the previously held record by Oracle/SPARC on both performance and price/performance1 by significant margins.

10TB: Running on a HP ProLiant DL580 Gen8 Server with SQL Server 2014 Enterprise Edition and Windows Server 2012 R2 Standard Edition, the configuration achieved a world record non-clustered performance of 404,005 query-per-hour (QphH) topping the previously held record from Oracle/SPARC of 377,594 query-per-hour (QphH) 1.  The SQL Server configuration also shattered the Price/performance metric with a $2.34 USD Dollar/Query-per-Hour ($/QphH) topping Oracle’s $4.65 $/QphH1

3TB: Running on a HP ProLiant DL580 Gen8 Server with SQL Server 2014 Enterprise Edition and Windows Server 2012 R2 Standard Edition, the configuration achieved a world record non-clustered performance of 461,837 query-per-hour (QphH) topping the previously held record from Oracle/SPARC of 409,721 query-per-hour (QphH) 1.  The SQL Server configuration also shattered the Price/performance metric with a $2.04 USD Dollar/Query-per-Hour ($/QphH) topping Oracle’s $3.94 $/QphH1

By breaking the world records for both performance and price/performance validates how SQL Server 2014 is delivering on leading in-memory performance at exceptional value. It also validates SQL Server’s leadership in data warehousing.

The TPC Benchmark™H (TPC-H) is an industry standard decision support benchmark that consists of a suite of business oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database have been chosen to have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.  The performance metric is called the TPC-H Composite Query-per-Hour Rating and the price/performance metric is the cost / performance metric. More information can be found at http://www.tpc.org/tpch/results/tpch_perf_results.asp?resulttype=noncluster

Eron Kelly,
General Manager
SQL Server


For more information:


1As of April 15, 2014.

SQL Server 2014 HP 10TB TPC-H Result: http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=114041502&layout=

2SQL Server 2014 HP 3TB TPC-H Result: http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=114041501&layout=
Oracle 10TB TPC-H Result: http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=113112501&layout=

Oracle 3TB TPC-H Result: http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=113060701&layout=

Tuesday, April 15, 2014 11:45:00 AM

Earlier today, Microsoft hosted a customer event in San Francisco where I joined CEO Satya Nadella and COO Kevin Turner to share our perspective on the role of data in business. Satya outlined his vision of a platform built for an era of ambient intelligence. He also stressed the importance of a “data culture” that encourages curiosity, action and experimentation – one that is supported by technology solutions that put data within reach of everyone and every organization. 

Kevin shared how customers like Beth Israel Deaconess Medical Center, Condé Nast, Edgenet, KUKA systems, NASDAQ, telent, Virginia Tech and Xerox are putting Microsoft’s platform to work and driving real business results. He highlighted an IDC study on the tremendous opportunity for organizations to realize an additional $1.6 trillion dividend over the next four years by taking a comprehensive approach to data. According to the research, businesses that pull together multiple data sources, use new types of analytics tools and push insights to more people across their organizations at the right time, stand to dramatically increase their top-line revenues, cut costs and improve productivity. 

A platform centered on people, data and analytics
In my keynote, I talked about the platform required to achieve the data culture and realize the returns on the data dividend – a platform for data, analytics and people. 

It’s people asking questions about data that’s the starting point -- Power BI for Office 365 and Excel’s business intelligence features helps get them there. Data is key – data from all kinds of sources, including SQL Server, Azure and accessibility of the world’s data from Excel. Analytics brings order and sets up insights from broad data – analytics from SQL Server and Power BI for Office 365, and Azure HDInsight for running Hadoop in the cloud.

A platform that solves for people, data, and analytics accelerates with in-memory. We created the platform as customers are increasingly needing the technology to scale with big data, and accelerate their insights at the speed of modern business. 

Having in-memory across the whole data platform creates speed that is revolutionary on its own, and with SQL Server we built it into the product that customers already know and have widely deployed. At the event we celebrated the launch of SQL Server 2014. With this version we now have in-memory capabilities across all data workloads delivering breakthrough performance for applications in throughput and latency. Our relational database in SQL Server has been handling data warehouse workloads in the terabytes to petabyte scale using in-memory columnar data management. With the release of SQL Server 2014, we have added in-memory Online Transaction Processing. In-memory technology has been allowing users to manipulate millions of records at the speed of thought, and scaling analytics solutions to billions of records in SQL Server Analysis Services. 

The platform for people, data and analytics needs to be where the data and the people are. Our on-premises and cloud solutions provide endpoints for a continuum of how the realities of business manage data and experiences – making hybrid a part of every customer’s capability. Today we announced that our Analytics Platform System is generally available – this is the evolution of the Parallel Data Warehouse product that now supports the ability to query across the traditional relational data warehouse and data stored in a Hadoop region – either in the appliance or in a separate Hadoop cluster. SQL Server has seamless integration with VMs in Azure to provide secondaries for high availability and disaster recovery. The data people access in the business intelligence experience comes through Excel from their own data and partner data – and Power BI provides accessibility to wherever the data resides.  

The platform for people, data and analytics needs to have full reach. The natural language search query Q&A feature in Power BI for Office 365 is significant in that it provides data insights to anyone that is curious enough to ask a question. We have changed who is able to reach insights by not demanding that everyone learn the vernacular of schemas and chart types. With SQL Server, the most widely-deployed database on the planet, we have many people who already have the skills to take advantage of all the capabilities of the platform. With a billion people who know how to use Excel, people have the skills to get engaged on the data.

Looking forward, we will be very busy. Satya mentioned some work we are doing in the Machine Learning space and today we also announced a preview of Intelligent Systems Service – just a couple of the things we are working to deliver a platform for the era of ambient intelligence. The Machine Learning work originates in what it takes to run services at Microsoft like Bing. We had to transform ML from a deep vertical domain into an engineering capability, and in doing so learned what it would take to democratize ML for our customers. Stay tuned. 

The Internet of Things (IoT) space is very clearly one of the most important trends in data today. Not only do we envision the data from IoT solutions being well served by the data platform, but we need to ensure the end-to-end solution can be realized by any customer. To that end, Intelligent Systems Service (ISS) is an Internet of Things offering built on Azure, which makes it easier to securely connect, manage, capture and transform machine-generated data regardless of the operating system platform.

It takes a data platform built for the era of ambient intelligence with data, analytics and people to let companies get the most value from their data and realize a data culture. I believe Microsoft is uniquely positioned to provide this platform – through the speed of in-memory, our cloud and our reach. Built on the world’s most widely-deployed database, connected to the cloud through Azure, delivering insights to billions through Office and understanding the world through our new IoT service – it is truly a data platform for a new era. When you put it all together only Microsoft is bringing that comprehensive a platform and that much value to our customers.


Quentin Clark
Corporate Vice President
Data Platform Group

Monday, April 14, 2014 9:00:00 AM

Tomorrow’s the day! Tune in to hear from Microsoft CEO Satya Nadella, COO Kevin Turner, and Data Platform Group CVP Quentin Clark about Microsoft’s approach to data, and how the latest advancements in technology can help you transform data into action.

Who should watch?

Join us tomorrow morning at 10AM PDT if you like data or want to learn more about it. If you store it, you manage it, you explore it, you slice and dice it, you analyze it, you visualize it, you present it, or if you make decisions based on it. If you’re architecting data solutions or deciding on the best data technology for your business. If you’re a DBA, business analyst, data scientist, or even just a data geek on the side, join the live stream.

What will I hear about?

Data infrastructure. Data tools. And ultimately, the power of data. From finding the connections that could cure cancer, to predicting the success of advertising campaigns, data can do incredible things. Join us online and get inspired. You’ll see how your peers are putting their data, big and small, to work.

From a product perspective, we’ll celebrate the latest advancements in SQL Server 2014, Power BI for Office 365, SQL Server Parallel Data Warehouse, and Microsoft Azure HDInsight. And ultimately, we’ll explore how these offerings can help you organize, analyze, and make sense of your data – no matter the size, type, or location.

Where do I sign up?

Mark your calendar now or RSVP on Facebook so you’re ready to go tomorrow. When streaming goes live, you can join us here for all the action live from San Francisco.

When do things get started?

Tomorrow, April 15, at 10AM PDT. Be there.

See you tomorrow!

Wednesday, April 9, 2014 10:00:00 AM

 Guest blog post by: PASS President Thomas LaRock – a SQL Server MVP, MCM, and Head Geek at Solarwinds – is a seasoned IT professional with over a decade of technical and management experience. Author of DBA Survivor: Become a Rock Star DBA, he holds an MS degree in Mathematics from Washington State University and is a Microsoft Certified Trainer and a VMware vExpert. You can read his blog at thomaslarock.com and follow him on Twitter at @SQLRockstar.

*     *     *     *     *

April opened with the general availability of SQL Server 2014. But well before we could wrap our hands around the final bits of the new release, the SQL Server community has been getting an early taste of its exciting performance, availability, manageability, and cloud features, thanks to a grassroots launch and readiness program that has spread around the globe.

The Professional Association for SQL Server (PASS) and hundreds of our volunteers around the world have joined with Microsoft to host free SQL Server 2014 launch events and technical sessions that focus on what matters most to data pros. These sessions explain the new features in the release, how the features work, and how we can use them to benefit our companies.

From user group meetings and PASS SQLSaturday sessions to the ongoing SQL Server 2014 Countdown webinars with PASS Virtual Chapters, the launch of SQL Server 2014 has truly been a community affair – and we're just getting started. Whether you're already on the path to early adoption, preparing to take advantage of the new release soon, or gathering information for the future, here's how you can get involved and get the details you need to make smart decisions for your organization:

  • Connect with fellow SQL Server pros: Microsoft Data Platform Group GM Eron Kelly noted that for Community Technology Preview 2, there were nearly 200K evaluations of SQL Server 2014, including 20K evaluations with the new release running in a Microsoft Azure Virtual Machine. That's a lot of folks who now have first-hand knowledge about SQL Server 2014. Check out those blogging and speaking about their experiences and sharing at chapter meetings, and then get to know them and what they know.
  • Share your questions, issues, and solutions: Have you tried out SQL Server's new built-in in-memory OLTP features? How about the enhanced mission-critical and availability capabilities? Have questions about implementing a hybrid data solution that bridges on-premises and cloud technologies? And how and when should you use the new delayed durability setting or clustered columnstore indexes? Share your experiences – and what you don't know or need more information about – and help the community build up resources that enable us all to work better, smarter, and faster.
  • Learn how to get the most from your data: Go inside the new release with experts on the SQL Server product team at upcoming live SQL Server 2014 Countdown webinars and watch on-demand replays of those you missed. You can also learn more about SQL Server 2014 and Microsoft's data platform strategy at the Accelerate Your Insights online launch event April 15 with Microsoft CEO Satya Nadella, COO Kevin Turner, and Data Platform Group Corporate Vice President Quentin Clark. And remember to check with your local PASS chapter, Virtual Chapter, or nearby SQLSaturday event for more SQL Server 2014 launch and learning events happening worldwide.

I'm grateful to be part of one of the most passionate technology communities in the world and excited to participate in a SQL Server 2014 launch program that, at its core, is about empowering SQL Server professionals and their organizations to be successful.

Thanks to everyone who is helping connect, share, and learn about SQL Server 2014.

Tuesday, April 8, 2014 10:00:00 AM

With the recently disclosed general availability of SQL Server 2014, Microsoft brings to market new hybrid scenarios, enabling customers to take advantage of Microsoft Azure in conjunction with on-premises SQL Server.

SQL Server 2014 helps customers to protect their data and make it more highly availably using Azure. SQL Server Backup to Microsoft Azure builds on functionality first introduced in SQL Server 2012, introducing a UI for easily configuring backup to Azure from SQL Server Management Studio (SSMS). Backups are encrypted and compressed, enabling fast and secure cloud backup storage. Set up requires only Azure credentials and an Azure storage account. For help getting started, this step-by-step guide will get you going with the easy, three-step process.

Storing backup data in Azure is cost-effective, secure, and inherently offsite, making it a useful component in business continuity planning. A March 2014 commissioned study conducted by Forrester Consulting on Microsoft's behalf about Cloud Backup and Disaster Recovery found that saving money on storage is the top benefit of cloud database backup, cited by 61%, followed closely by 50% who said savings on administrative cost was a top reason for backing up to the cloud. Backups stored in Azure also benefit from Azure built-in geo-redundancy and high services levels, and can be restored to a Azure VM for fast recovery from onsite outages.

In addition to the SQL Server 2014 functionality for backing up to Azure, we have now made generally available a free standalone SQL Server Backup to Microsoft Azure Tool that can encrypt and compress backup files for all supported versions of SQL Server, and store them in Azure—enabling a consistent backup to cloud strategy across your SQL Server environments. This fast, easy to configure tool enables you to quickly create rules that direct a set of backups to Azure rather than local storage as well as select encryption and compression settings.

Another new business continuity planning scenario enabled by SQL Server 2014 is disaster recovery (DR) in the cloud. Customers are now able to setup an asynchronous replica in Azure as part of an AlwaysOn high availability solution. A new SSMS wizard enables you to simplify the deployment of replicas on-premises and to Azure. As soon as a transaction is committed on-premises it is sent asynchronously to the cloud replica. We still recommend you keep your synchronous replica on-premises, but by having the additional replicas in Azure you gain improved DR and can reduce the CAPEX and OPEX costs of physically maintaining additional hardware in additional data centers.

Another benefit of keeping an asynchronous replica in Azure is that the replica can be efficiently utilized for read functionality like BI reporting or utilized for doing backups, speeding up the backup to Azure process as the secondary is in Azure already.

But the greatest value to customers of an AlwaysOn replica in Azure is the speed to recovery. Customers are finding that their recovery point objectives (RPO) can be reduced to limit data loss, and their recovery time objectives (RTO) can be measured in seconds:

  • Lufthansa Systems is a full-spectrum IT consulting and services organization that serves airlines, financial services firms, healthcare systems, and many more businesses. To better anticipate customer needs for high-availability and disaster-recovery solutions, Lufthansa Systems piloted a solution on SQL Server 2014 and Azure that led to faster and more robust data recovery, reduced costs, and the potential for a vastly increased focus on customer service and solutions. They expect to deploy the solution on a rolling basis starting in 2014.
  • Amway is a global direct seller. Amway conducted a pilot test of AlwaysOn Availability Groups for high availability and disaster recovery. With multisite data clustering with failover to databases hosted both on-premises and in Azure, Amway found that the test of SQL Server AlwaysOn with Azure replicas delivered 100 percent uptime and failover took place in 10 seconds or less. The company is now planning how best to deploy the solution.

Finally, SQL Server 2014 enables you to move your database files to Azure while keeping your applications on-premises for bottomless storage in the cloud and greater availability. The SQL Server Data Files in Microsoft Azure configuration also provides an alternative storage location for archival data, with cost effective storage and easy access.

If you're ready to evaluate how SQL Server 2014 can benefit your database environment, download a trial here. For greater flexibility deploying SQL Server on-premises and in the cloud, sign up for a free Azure evaluation. And, to get started backing up older versions of SQL Server to Azure, try our free standalone backup tool. Also, don't forget to save the date for the live stream of our April 15 Accelerate Your Insights event to hear more about our data platform strategy from CEO Satya Nadella, COO Kevin Turner and CVP of Data Platform Quentin Clark.

Tuesday, April 1, 2014 10:00:00 AM

Microsoft today released Microsoft SQL Server 2014, the foundation of our cloud-first data platform. SQL Server 2014 delivers breakthrough performance with new and enhanced in-memory technologies to help customers accelerate their business and enable new, transformational scenarios. In addition, SQL Server 2014 enables new hybrid cloud solutions to take advantage of the benefits of cloud computing with scenarios such as cloud backup and cloud disaster recovery for on-premises SQL Server installations.  SQL Server 2014 continues to offer industry-leading business intelligence capabilities through integration with familiar tools like Excel and Power BI for Office 365 for faster insights. 

We will also soon make generally available the SQL Server Backup to Microsoft Azure Tool, a free tool that allows customers to backup older versions of SQL Server to Azure storage.

Try SQL Server 2014 release today

Download and try the generally available release of SQL Server 2014 today on premises, or get up and running in minutes in the cloud. And, please be sure to save the date for the live stream of our April 15 Accelerate Your Insights event to hear more about our data platform strategy from CEO Satya Nadella, COO Kevin Turner and CVP of Data Platform Quentin Clark.


Eron Kelly

General Manager

Data Platform Group

Friday, March 28, 2014 1:06:00 PM

Data enthusiasts & Microsoft talk about Big Data in the enterprise at Gigaom Structure Data 2014

Last week in New York, our data solutions experts spent a few days with more than 900 big data practitioners, technologists and executives at Structure Data for a conversation how big data can drive business success.

The rich conversations with attendees at the event were inspiring, and the broad range of speakers was impressive.  Our discussions over the two days in New York centered on what the Big Data solution looks like inside an enterprise and the challenges around accessing and processing big data to make better data-driven decisions. 

Structure Data attendees want to combine data from multiple sources to do a couple key things -- to gain deeper insights and to ask new questions and get answers.  However, without the right technology to support that, it can be very challenging to do this.  That's where Microsoft comes in -- and where we continued the dialog with attendees as our data experts used a huge Microsoft touchscreens to show how easy it can be to transform big data to insights using simple, front-end tools (like Excel or Power BI for Office 365) and back-end technology for scale, power and speed (like Windows Azure HDInsight and SQL Server). 

Microsoft Research Distinguished Scientist John Platt also spoke at Structure Data and shared the latest on our work in machine learning, which is quite pervasive throughout many Microsoft products. If you missed it, take a moment to watch the short chat here

Our data experts also gave attendees an insiders’ view at how Microsoft’s Cybercrime Center is using data to fight worldwide organized crime and BotNets. (See the video below for more.) 

Take the first step and learn more about Microsoft Big Data solutions:

Or, connect with us on Facebook.com/sqlserver and Twitter @SQLServer and learn how Microsoft’s approach to data, big and small, helps employees, IT professionals and data scientists quickly transform data into insight and action.

And don't forget about the April 15th Accelerate your insights event where Microsoft will unveil the details of new capabilities for the appliance that has both scale-out relational data warehouse and Hadoop in the same box, thus evolving PDW from a solution built for high performance relational data warehousing to a true turnkey Big Data Analytics appliance.

Thursday, March 27, 2014 10:00:00 AM

Mark your calendar now to join us online on April 15 for the Accelerate your insights event, streaming live from San Francisco, California at 10:00 AM PDT.

Wondering what your data can do for you? Join us to online find out how to drive your business in real-time, from apps to insights. You’ll hear from several of Microsoft’s top executives, including Chief Executive Officer Satya Nadella, Chief Operating Officer Kevin Turner, and Corporate Vice President of the Data Platform Group, Quentin Clark.

Save the date to watch the keynotes streamed live on 4/15:

-        Mark your calendar

-        RSVP on Facebook

Join us as we share Microsoft’s data platform vision, and how the latest advancements in our data technologies can help you transform data into action.

See you there.

Wednesday, March 26, 2014 11:52:00 AM

In my recent post, Simplifying Business Intelligence through Power BI for Office 365, I described how the Power BI cloud service has changed the way I personally work. The ability to do “self-service BI” without any IT involvement—and to have the infrastructure transparently provisioned in a cloud computing environment—has enabled me to drop any dependency on specific hardware, and I can use just about any Web browser. We have heard from many business users that this self-service approach is extremely empowering, and it enables rapid progress and insights.

One potential down side of self-service BI is that the reports, charts, and graphs that users develop with such tools can be cut off from other data and reports. Self-service BI solutions can also become out-of-date and are, as such, unreliable. These are serious limitations of self-service BI tools. For self-service tools to be convenient, as well as reliable and trustworthy, there needs to be a way to easily include user-created reports as part of a larger solution, and also to operationalize these solutions. Without such capabilities, self-service tools are, at best, good for prototyping.

Power BI is a complete system, tying together the empowering aspects of self-service with the operational and collaborative capabilities that are critical for an enterprise-class solution. Power BI also provides governance and data stewardship capabilities, as needed, to enable management oversight. The result is a managed self-service system.

In this post, I want to talk about how Power BI enables me to create complete business insight solutions that are based on fresh, reliable data. I will not cover data governance capabilities, keeping that as a topic for another day.

Pulling It Together

For me, a Power BI site is the place where I can pull together a full solution that gives an “at a glance” view of visualizations and insights that are available for a set—or sets—of data. It also is the place to enable operations such as refreshing the data for the solution. I use the “Featured Reports” row at the top of a Power BI sites application to highlight the reports and visualizations that may be most important and that I want to call out visually.

Power BI Q&A is a good way to ask ad-hoc questions using natural language, but to be really effective, it is important to provide starting points from which users can start exploring a dataset. For this, I use the “Featured Questions” capability. Featured questions enables me to come up with starter questions, which can be used in two ways:

1. To lead users into the Q&A experience from the Power BI site app main page through the Featured Questions row on Power BI sites.

2. To guide users to what they can ask on the Q&A page itself. The “About this data” slide out panel on the right side of the Q&A page lists several types of questions that users can ask.

Adding specific featured questions can be done from the Featured Questions view (accessible from the  icon next to the Q&A text box and in the “About this data” slide out panel).

Keeping It Fresh

Power BI leverages Excel workbooks to create data models and compelling interactive visuals. Data is mashed up in Excel and can come from various different data sources. Typically, once I mash up data and have some interesting visuals to share, I upload the Excel workbook to a site on the Power BI service, and as simple as that, the solution is available in the cloud. So far so good, right—except that the data in the solution is “as of” the upload time. It can quickly get stale and might not make sense later, if the data is time-sensitive. Of course, it is possible to re-upload the workbook with refreshed data, but that is a manual and potentially unreliable solution. What is required is a way to automatically refresh the data. Power BI enables this via the “Scheduled Data Refresh” feature. Scheduled Data Refresh enables automatic updates on a regular schedule, keeping the data fresh and the reports reliable, and this is critical for real world solutions.

Like everything in Power BI, the process for setting up the Scheduled Data Refresh is self-service in nature. The ellipsis (…) menu for each workbook on the Power BI site is where a data refresh is configured and monitored.

Setting up the actual schedule is straightforward, with simple options. Of course, the flip side to simplicity is that the options are somewhat limited. Over time, we will add more customizations, and the overall goal is to enable business users to set up the refresh without requiring help from someone in IT.

Once this is set up properly, the refresh job runs as defined in the schedule, and switching to the “history” tab provides a quick view of jobs that have already run and the results of each run (including any errors).

In the example, my data source is an Azure SQL Database, and as such, it is simple to establish the connection. The connection I defined in my Excel workbook just works in the cloud after uploading to the Power BI service, and the refresh operation also just works. However, in many cases, data comes from an on-premises source, and in these cases, the refresh is not as simple. To refresh data from an on-premises source, the first step is to install and configure the Data Gateway; then, add the data source to the Data Gateway. I will not cover this in detail here; it deserves a write up on its own.

A Complete BI System

With this up and running, the overall solution is now a workable and practical implementation. Power BI sites provide the “face” of the solution and allows the user to get productive quickly. The Scheduled Data Refresh ensures data freshness and reliability so critical to business insights. Power BI converts the Excel workbook from an island of data to a full participant in a BI solution, with fresh, reliable data that the business can rely on. Add this to the rest of the capabilities in Power BI related to data management, and we have a complete BI system that enables the business user to get powerful insights, and at the same time, still enables administrators to have a degree of oversight and control.

Read more about using the Power BI sites application with your SharePoint Online site.

Kamal Hathi, Director of PM, Data Platform Group


Check out the website to learn more about Power BI for Office 365 and start a free trial today. For those who want access to the upcoming SQL Server 2014 release as soon as possible, please sign-up to be notified once the release is available.  Also, please join us on April 15 for the Accelerate Your Insights event to learn about our data platform strategy and how more customers are gaining significant value with SQL Server 2014.  There also will be additional launch events worldwide so check with your local Microsoft representatives or your local PASS chapter for more information on SQL Server readiness opportunities.

Site Map | Printable View | © 2008 - 2014 SQLTrainer.com, LLC | Powered by mojoPortal | HTML 5 | CSS | Design by mitchinson