sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009
What is the key to advancing your career in the Microsoft data platform? Here is some advice from some of the most successful people in the industry…
Every year we have some big community events that bring together community leaders from all over. These are international user group and community leaders who write books and speak at conferences. we had our local Oregon SQL Saturday “SQL Train” which is a chartered train – some coaches that one of our speaker’s chartered to bring all of our speakers and attendees up to the PASS summit after Oregon SQL Saturday, and then the big PASS summit (the big conference up in Seattle). I had a chance to sit down with a number of our speakers, community leaders and attendees and just ask questions about what brought them there, and advice that they would give people in the industry about how to get the most value out of that experience …and this is what they said:
Well-run monthly user group meetings and big annual events local events like SQL Saturday don’t just happen by themselves. It takes a lot of planning, a lot of volunteers and a lot of coordination to make these events successful. Part of that effort are the annual leadership planning meetings that we have during the week of PASS summit. Here are some short clips from those meetings where several hundred local leaders from all over the world got together to share ideas, to exchange notes and to coordinate to be able to make these events successful. Leaders cross-pollinate, exchange ideas and they work together to make this a great community. Why? …because somebody did that for us when we were getting started and we want to give back to the community. So, get involved; join the leadership committees at your local user groups, volunteer at SQL Saturday. Volunteer to do a short speaking engagement. Just get up and talk to some of your peers. Get started by volunteering in the community so that you can be part of the ongoing great community we have around the Microsoft data platform.
Since starting the Guy In A Cube series over three years ago, Adam Saxton has become the front man for Microsoft Business Intelligence feature announcements and Power BI how-to tutorials. Joined by Patrick Leblanc last year, the Guy In a Cube series features over 200 short video tutorials with at least two new clips added every week. The Guy In A Cube YouTube channel currently has over 13,000 followers.
I always look forward to spending time with both of these guys at community events. I first met Adam several years ago when he was a Senior Support Escalation Engineer for Microsoft who helped with a number of tough BI server setups. Patrick did senior level consulting work for customers in the field before joining Microsoft as Data Platform Solutions Architect. Adam and Patrick are natural born entertainers. With their deep knowledge and expertise with database technology, security and the entire BI, analytics and reporting stack; they offer one of the top online resources for learning and keeping up with the rapidly-expanding Microsoft BI platform.
At conferences and events, I rarely see Adam without a camera in his hand and so was a real treat to get them in front of my camera for this interview. We chatted about how they got started and how they continue to use their series to highlight new and important features, and to provide guidance to support the Microsoft BI community.
In this interview during the 2017 PASS Summit, we continue to explore the topic of this series of blog posts on Microsoft BI solutions for the enterprise. Patrick and Adam share their guidance about managing large-scale solutions, version control and multi-developer projects using Power BI, Analysis Services and SQL Server.
It’s official. The PASS Summit schedule was announced and I will be doing three sessions: A full-day preconference, a regular session and a panel discussion with other industry professionals. Come back to this post for follow-up information and selections of content from these sessions after the event.
The PASS Summit is my favorite event of the year and I always leave with so much valuable insight and information. PASS is my annual battery recharge for the rest of the year. It is a vibrant community of professionals and industry leaders who love to share and exchange learnings. Not just an opportunity to attend sessions presented by top-notch experts and product owners from Microsoft but Summit presents many real opportunities to sit and talk, meet and socialize with these industry influencers. My perspectives change and I go back to my clients, classrooms and projects with solutions and a fresh new approach. I’ve been going to the PASS Summit for twelve years and every year is literally the best Summit ever. In the industry, there is no better investment of time and energy compared with what you will gain by attending. I look forward to meeting many of you at Summit. Please introduce yourself and say ‘Hello’. Continue reading →
I am thrilled to be presenting a full-day preconference session before the PASS Summit, on October 31st. Please join me for a deep tour of the new capabilities and BI platform integrations for SQL Server Reporting Services 2016 & 2017. The session will also review the essential skills and tasks to setup and configure the report server and web portal, report design and modern report solution planning. Continue reading →
During the month of April, I will be delivering three full-day Power BI hands-On workshops. Each of these events will be the Friday preceding these SQL Saturday events. Seating is limited and many of these workshops tend to book-up. Follow the links to register.
The format will be the same for each event. These are intermediate-level workshops. If you’re new to Power BI, just a little self-study should get you ready to optimize your learning experience.
Event Description:
In this Power BI hands-on Workshop, we will quickly review the essentials and learn some advanced techniques to transform, model and analyze business information with Power BI Desktop. Techniques and best practices presented are from several prior workshops and years of field experience. Learn to use self-service and enterprise-scale Power BI capabilities; gain valuable skills to integrate, wrangle, shape and visualize data for analysis.
If you’re just getting started, please pick-up an intro book or use online resources to familiarize yourself with Power BI Desktop so you can take your skills to an intermediate level in the workshop. If you have the basics, be prepared to take your skills to the next level; learn to address data and reporting challenges with advanced design techniques. At the conclusion of the workshop, you will have a complete solution built from real business data, shaped, cleansed & modeled; with a dashboard and interactive report visuals ready for analysis.
Topics & skills include: Power Query/M, modeling and calculations/DAX, standard & custom visuals, R visuals. Data sources: CSV, JSON, SQL Server; query folding, scheduled refresh & DirectQuery.
Attendees should have at least intro-level experience w/Power BI Desktop or Power Pivot. Bring laptop w/latest Power BI Desktop ver. installed (PowerBI.com), 64-bit Windows & 4 GB of RAM (8 GB recommended), 2 GB free storage. Power BI subscription recommended, not required.
As I visit businesses, consulting clients and training classes, to teach data reporting and analytics; there is a recurring conversation. It is one that I have had for eighteen years. The audiences change and the technology implementations are a little different over time, but the essential conversation is still the same.
This happened again last week as I was trying to explain the unique characteristics of Multidimensional and Tabular storage to a client. I’m developing a training course where I needed to explain the concepts once again – and then it hit me! …these unique data storage and analytic technologies do what we dream about in science fiction stories and achieve capabilities we imagine existing in the distant future. Channel surfacing on television this week, I watched an episode of Timeless, a Star Trek rerun and a Star Wars movie – where time-travel, space-warp travel and teleportation were commonplace realities. Although fictional as they were portrayed, I think these concepts are very real in our technology landscape. Please indulge me as I explain.
We live in a Linear world. We reside in a place, work in a place, store and move stuff from place to place. Centuries ago, if man needed to move something (perhaps just himself) to a distant place, he would walk, ride a horse or take a sailboat. In weeks or months, he would arrive in another place. Today, we get in a car, navigate the streets, perhaps highways and Interstates and then arrive in a different place within minutes or hours. For longer trips, we board a large metal tube, sit very close to several complete strangers as we climb up into the sky; some period of time goes by and then we somehow arrive in a distant place along with our stuff. At 35,000 feet where the air is very thin, a jet airplane can travel many times faster; leaving an altitude where certain physical laws restrict speed, only to re-enter that atmosphere after it has arrived. To someone from the distant past, this experience would seem fictional.
image: nasa.gov
On a daily basis, I sit or stand in front of my computer monitors, and see and speak with people in distant places. We have live conversations about the weather in their time zone or hemisphere. Through some strange but now common process, technology converts our speech, images, directions, documents, effort and thoughts into 8-bit network packets and electrons that travel almost instantly through space; leaving the earth for a short period, to be transported and reassembled somewhere else.
Years ago, when I wanted new stuff, I would drive to the store. If they didn’t have what I wanted or if it was too expensive, I would spend the day driving to different stores. Today, I “go” to the store on my computer and the very next day, my stuff arrives on my doorstep. We are continually inventing ways to bend space and teleport things within the changing confines of reality.
Data storage is much like the real world. We store terabytes and petabytes (numbers we can’t even explain) in a small space. But, to navigate through the linear storage structure of relational databases and files, the data might as well be thousands of miles or kilometers apart. In order to perform time-variance and statistical calculations, program code must access a group of records in one location to aggregate them, and then find a related group of records – perhaps millions of rows apart – to perform another aggregation. The query might need to perform this operation dozens, hundreds or thousands of times; before it can calculate the final results. One of the best examples is a time-series calculation where a query must “visit” records for each time period to perform an aggregation. The effort is compounded when the goal is to compare aggregate values and variances for parallel and relative periods (e.g. “one year ago”, “three years ago”, etc.) and then perform forecasting or regressions.
image: ctdn.com
In relational storage, the query engine must “drive” from place to place, navigating the streets (file groups, files, pages and rows) to find the records. In an analytic structure (like an Analysis Services cube, or SSAS Tabular model or Power Pivot/Power BI in-memory model), the query engine performs a calculation operation and then teleports to a new location to find a different group of related records.
image: space.com
In a multidimensional cube, the storage processing code quite literally folds space to create the cube structure and pre-calculate some of the aggregate values. Rather than leaving all the data in a linear table, it actually moves data segments close together, reshaping the data structure (thus, the term “cube”). This approach required a great deal of complexity in both the software and the resulting data structure. Advances in computer hardware and software techniques helped simplify the approach. The in-memory tabular model exists in linear, yet fragments of compressed space – where the actual distance between adjacent records is much closer than it appears to be. When the query process is done looking up a group of records it warps to the next group of related records using a memory address. Instead of traveling through space, it steps out of it, like the jet airplane leaving the atmosphere, to re-enter in a different location. It takes much less time to run queries because it is not moving through all the rows of linear tables.
By using unique and innovative data storage methods (to reduce storage space and access data faster without moving through data), we can, in effect; perform time-travel, warp space and teleportation.
I recently needed to replicate all of the reports on one report server to a different server. I started downloading each report one at a time to a file system folder, moving them to the other machine and then uploading each RDL file. Needless to say, that’s a slow and painful process. Since a new set of PowerShell command lets (Cmdets) were introduced for Reporting Service just a couple of weeks ago, I decided to give them a try and share the results.
Aaron Nelson, long-time Microsoft Data Platform MVP and PowerShell fanatic, has literally been hounding me for the past few weeks to take some time and look at a new PowerShell library and scripts originally developed by Parth Shah from the SSRS product team. Jaime Tarquino also played a big role in automating the build process. It’s the personal commitment of folks at Microsoft like Parth and Jaime that really make a product like SSRS shine.
Aaron can be pretty persistent and I admit it’s ironic that I’ve been too busy finishing up the final editing work on the Wrox SSRS 2016 book and the new edX SSRS course to look at these RS PowerShell CmdLets but I’m glad that I did because they’re an absolute gold mine.
Back to my project… I have developed several demo and lab exercise reports for the new edX SSRS course: Analyzing and Visualizing Data with SQL Server Reporting Services. The development environment is an Azure virtual machine that I recently didn’t have access to while travelling and needed to move all the reports to a local VM to continue my work. I needed to make sure that current versions of these dozens of reports are on both servers. Using a couple of new PowerShell commands, this was quite easy. Here’s how it worked:
Step 1: I open the PowerShell ISE console on the server where I need to archive the reports (shown with the script I’ll run in the next few steps)
Step 2: run a command to download and install the rstools library (see line 1):
Step 3: Line 5 downloads all the reports in the “DAT214x Course” folder on my Azure VM report server to a local file system folder:
Out-RsFolderContent …
Step 4: On the destination server, I also run the command to install the rstools library…
Step 5: After copying the files to a local folder (or you could use a UNC path to access a shared folder), run Write-RsFolderContent to upload all the reports to the same destination folder:
Progress is displayed while the files are uploaded:
With shared data sources in the same places, all of the files are uploaded and working:
This saved me hours of time and provided peace of mind, knowing that I could easily automate large migrations in the future.
Here is the complete list of commands. You can get help, execution details and optional parameter information by prefixing the command with the word “help “:
You need to be running PowerShell as Administrator in order to do this.
My favorite command among them is this one which will upload an entire folder worth of Reports & Datasets to an SSRS server for you. Here’s an example using Jamie Thomson’s SSIS Reporting Pack (attached):
Since the Reporting Services product team announced last year that it was their intention to provide an on premises deployment option for Power BI reports, there has been tremendous interest. Despite their determination to provide clarity around their efforts , there has still been some speculation about how and when we’ll see this capability. I don’t want to jump on the bandwagon and add to the rumors or debate the details. The fact is that product leadership have been more than forthright about their progress and must work through the usual rigor of packaging and supporting a major feature addition when it is ready for release.
At the PASS Summit in Seattle a few weeks ago, Riccardo Muti and Chris Finlan announced that the team planned to release a new preview of the enhancements to SQL Server 2016 Reporting Services that could support Power BI reports hosted within the SSRS web portal. Today, they are delivering the goods with an Azure virtual machine that we can use to preview these features. Riccardo was very clear that the VM would be a very early preview and that the actual released product wouldn’t be announced until it was ready. The question on the minds of several community leaders is whether these features will be available in some kind of update for SQL Server 2016 or whether it will be in the next version of SQL Server. I checked with Riccardo Muti, Group Program Manager for SSRS and he says “We’re releasing Power BI reports in ‘SSRS vNext’ as part of the SQL Server vNext wave, but don’t assume ‘vNext’ must mean a couple of years.“ …and, “it’ll be here in 2017“. Now we have a definitive answer.
Dan English posted about this today in an article titled On-Premise Power BI–A Microsoft Story. I encourage you to read his entire post and understand his thoughts on the matter. At the PASS Summit session, I had the impression that there would be an interim update for SQL Server 2016 but after reading a lengthy email thread today, that doesn’t seem to be the case. My conclusion is that plans can change and there has not been on official announcement on the release plan.
The official announcement was posted today on the Reporting Services product team blog by Chris Finlan, titled Technical Preview of Power BI reports in SQL Server Reporting Services now available. Chris told me about this plan a few weeks head of the announcement and said that they weighed their options carefully before they decided to go down this route. The trade-off was that this decision would likely raise unanswered questions and speculation out in the community. In the end, they decided it was best to err on the side of being open and transparent with their customers.
From Chris’ post on the team blog:
As we brainstormed creative ways to let people try this functionality as early as possible, we had three very specific goals we wanted to achieve:
Provide access to the new functionality publicly as early as possible while ensuring the end-user experience was something you’d find valuable
Create a self-contained experience and environment that allowed users of any skill level an easy way to get started
In no way disrupt or delay the initial preview of a downloadable and installable version
By using the Azure Marketplace to distribute this early technical preview, we feel we have not only met those goals, but also established a repeatable way to distribute content in the future. For users who would prefer to run this technical preview on an on-premises server, you are welcome to provision a virtual machine and then download the image as a .vhd file and use Hyper-V functionality to do so.
“Professional SQL Server 2016 Reporting Services and Mobile Reports” from Wrox Press is content complete! The book is well into the editing stage– still, another grueling phase of the publishing process, but there is light at the end of the proverbial tunnel. What is it like to write an 800+ page technical book? I’ve been trying to come up with the best metaphor to describe a year of persistent weekend-and-evening content development – building labs, demonstrations and exercises; performing research and and hounding my friends on the product team. A big thanks thanks to Chris Finlan, Riccardo Muti and Robert Bruckner on the SSRS product team for their continued help and support.
I’ve admittedly been a bit quiet on the community front for a while I’ve been heads-down on this book project but I hope to start redirecting more energy to community content.
Intense book writing can be a life-sucker:
This is Paul’s brain…
…this is Paul’s brain after writing a technical book:
Don’t get me wrong – I enjoy book writing. I’ve spent a lot of my career writing technical books and courseware because I enjoy the challenge. Writing helps me see things from different perspectives and at different levels of depth and difficulty for different audiences.
Give my brain a couple of weeks to heal and I’ll channel some of that energy into blogging and producing more community-facing training content.
So, what’s in the book? This is the Fifth Edition of the Wrox Professional SQL Server Reporting Services series which I’ve been leading and co-authoring since 2003. Amazon still has a working title listed but this is normal. As a next-edition, some of the 2012 edition evolved forward but most of the book is all new material. Four chapters are dedicated to the new Mobile Report features introduced in SSRS 2016. Before someone asks… when will the book be out? I don’t know exactly; there’s still work to be done. Soon. How’s that?
Over the next few weeks, I’ll post excerpts from the book to give you a taste of what’s coming and provide some valuable information about SSRS 2016.
Here’s a short except from Chapter 2: What’s New in SQL Server 2016 Reporting Services?
Reporting Services was released as an add-in tool for SQL Server 2000 back in early 2004. At the time, the feature set was light by comparison to the product today but the foundational architecture was hasn’t changed significantly. Essential features included basic charts, sub-reports data regions and single-value textboxes.
The second release in 2005 added a self-service report authoring tool called Report Builder (later named Report Builder 1.0) paired with a semantic modelling tool in the designer. The original modelling and ad hoc report tool has since been deprecated but it inspired more capable replacement technologies like Power Pivot and the later generations of Report Builder. Not to be confused with the original Report Builder tool, Report Builder 2.0 and 3.0 produce report definition files compatible with the report project tools that are integrated with Visual Studio; originally called Business Intelligence Development Studio (or “BIDS”).
Report Builder and Designer Enhancements
The report design experience for standard “paginated” reports hasn’t really changed much over the past few product versions but there have been incremental improvements. Report Builder is restyled to conform to Office 2016 standards. The installation process for Report Builder changes to an “Evergreen” app. This means that Microsoft maintains updates for frequent download, rather than the old “ClickOnce” installation from your on-prem server. Similar to prior versions, users can elect to install Report Builder from the Report Portal menu. Report Builder is updated with a modern look-and-feel, simple and sleek, Changes are mostly cosmetic while the fundamental features are the same.
The Visual Studio integrated Report Designer is now part of SQL Server Data Tools (SSDT), a downloadable add-in for Visual Studio. Although the tool set hasn’t changed significantly, there are some subtle changes to the way SSDT is installed and the way updates are delivered. First of all, confusion about the name “SSDT” is dispelled because the former “SSDT” (the previous version add-in for database projects) and “SSDT for BI” (the previous version add-in for SSIS, SSAS and SSRS projects) are now a combined package, simply called SSDT. Secondly, you can simply download and install a version of SSDT that will work with the current version of Visual Studio or a few versions back. The SSDT add-in will be updated frequently and you will have the option to install updates on-demand from within Visual Studio.
Modern Browser Rendering
One of the most significant product improvements in 2016 may be one of the least apparent under casual observation. The entire HTML rendering engine has been completely overhauled across the platform. The report portal, used to navigate and manage report content, and the actual report content are rendered to modern HTML 5 standards, which are supported by all modern web browsers. The shift to modern HTML output means that web content produced by Reporting Services is consistently consumable on any device, regardless of the operating system or web browser, so long as it supports modern standards. The benefits are readily apparent when reports simply work on smartphones of any type, on tablets and laptop and desktop machines; regardless of the brand or operating system.
Parameter Layout Control
You will have improved control over parameter formatting and placement. Since the inception of Reporting Services about twelve years ago, parameters have always been arbitrarily arranged in a narrow bar at the top of the browser window, from left-to-right, and then top-down. The report designer has a grid to manage the placement of parameters, in the parameter bar, in any configuration, within definable rows and columns.
Mobile Reports
The addition of mobile dashboards to the SSRS platform is based on the Datazen product acquisition from ComponentArt in 2015. Mobile reports are primarily designed to enable data interactivity in dashboard-style reports created by a mobile report developer. Managing this expectation is important because this tool is significantly different than conventional Reporting Services.
Mobile reports can be viewed in the browser but are optimized for phone and tablet devices through native, installed applications running on all the major mobile OS platforms. They are not a replacement for high-fidelity paginated reports created with Reporting Services, or self-service analytics in Power BI; they serve an entirely different purpose.
At first, the mobile dashboard experience may seem to be a simple drop-in of the Datazen product; but, it is apparent that some integration with the SSRS architecture has already taken place, and more adaptations are likely on the horizon. The first notable difference is the Datazen server is entirely replaced by the SQL Server report server, and queries are now managed as SSRS shared datasets.
KPIs
New Key Performance Indicators (KPIs) integrated with the new Report Portal are also based on the Datazen product acquisition. These KPI visuals are created and managed entirely within the portal. In additional to the standard traffic light style comparison of actual vs target values, KPIs can include a trend line or segment chart. The KPI is driven by data from one or more shared datasets that were created in the SSDT report designer. For ease and simplicity, any value of the KPI can be entered manually through the design page.
Although they are visualized in the Report Portal, KPIs are delivered to mobile devices through the Power BI mobile applications on every popular device platform.
Native Printing Control
The previous printing capability in SSRS replied on an ActiveX control which was only supported on Windows desktops and in certain web browsers. Even in tightly controlled Windows server environments, system administrators would rescind ActiveX support and disallow report printing from the server. The modern printing solution uses the PDF renderer to printable output and then the Adobe document viewer to perform the actual printing
PowerPoint Rendering
Users have had the option to export and render report content to Excel for several SSRS versions. Output to Word was added in SQL Server 2008 and then both of these rendering options were improved and updated in the 2008 R2 version. Now a third Office application format will be supported with the introduction of PowerPoint document rendering.
Most report items and data regions are converted to individual image objects in the resulting PowerPoint sides with one side generated per report page. Additional slides are created based on the report content size and layout. Textboxes are created for titles and report text, which support some report actions and textbox properties.
Integrated and Improved Report Portal
A new Report Portal web interface is introduced to replace the Report Manager. Like Report Manager, the portal is an ASP.NET web application used to access, run and manage reports in the web browser. The new portal has a look-and-feel we are accustomed to seeing in other modern apps from Microsoft these days; with responsive design for constancy on different device form factors. Report Portal will be the home for mobile reports, KPIs and paginated reports – the new name for RDL reports authored with Reporting Services. In the future, we may see support for additional content types.
Report Portal supports in all modern web browser by emitting responsive HTML5 with adaptations for mobile devices and screen orientations.
New Charts and Visual Enhancements
With the addition of two new chart types, visualization improvements are inched forward in Reporting Services. The new Sunburst and Treemap charts apply multi-level field groups visualized in both color and visual boundaries.
New and updated report visuals are likely to be an area of focus for future Reporting Services enhancements, given the success of self-service BI tools like Power BI. The design interface is identical to existing chart types and the only real difference is that groups of rows are visualized in these unique formats. The Sunburst chart is also capable of consuming unbalanced hierarchies with slices generated for different levels only where data points exist.
Standardized, Modern Browser Rendering
At first, you may not notice significant changes from previous versions, but the HTML renderer has been completely overhauled and updated. Now, reports are rendered to HTML 5 standards whereas, they should consistently maintain the same appearance and behavior in all modern browsers that support the HTML 5 standard such as Microsoft Edge, IE 11, and newer versions of Google Chrome, Safari and Firefox. This change is a welcome improvement which should clear up many problems with inconsistent and quirky report layouts while using different web browsers and devices. By the same token, the change means there is no specific backward-compatibility for outdated browsers; consequently, reports that may have worked (or partially worked) in an old version of Internet Explorer may no longer work until the user upgrades.
Power BI Dashboard Pinning
For organizations who have invested in the Power BI cloud service, the Power BI integration feature allows users to pin graphical SSRS report visuals to their online dashboards. In order to use this feature, an administrator must register the report server with an existing Power BI subscription, and a report user must have access to the Power BI subscription.
Reporting Services Configuration Manager includes a new page to manage Power BI Integration. This is where you register the report server instance with the Power BI subscription. When a report with “pinnable” items (such as images, charts and gauges) is viewed in Report Portal, the Power BI icon is displayed on the toolbar.
“pinnable” items are highlighted in Report Portal. When a visual is selected, you are prompted to select a Power BI dashboard and the refresh frequency. This schedules an Agent job on the report server to push updated visuals to the dashboard at the selected frequency.
The Select frequency of updates option utilizes the SSRS subscription architecture by scheduling a SQL Server Agent job on the database server with the report server catalog. The Agent job re-queries the report data and then a report server component refreshes the Power BI dashboard tile with an updated report visual.
Pinned report visuals appear on the dashboard alongside the Power BI report and Excel visuals. Clicking on one of these visuals will drill-through to the report back on the on-premises report server. This gives users a seamless navigation experience between cloud-hosted Power BI content and selected report visual elements on your own report server.
The integrated Power BI experience is a big step forward in providing a completely integrated IT-hosted and self-service reporting, BI and analytics solution.
I just finished posting a 4-part series demonstrating how to use Power BI with on-premises data. The running time for all four parts is about 44 minutes. I cover using the Power BI Enterprise Gateway to connect published Power BI reports to on-premises data sources:
SQL Server database connected with DirectQuery
Large SQL Server database with clustered column store index connected with DirectQuery
SSAS multidimensional/cube on-prem with direct connect
SSAS Tabular with large data volume & multiple partitions on-prem with direct connect
The four-part series is on my YouTube channel using the following links:
Part 1
Part 2
Part 3
Part 4
Adam Saxton also gives a nice walkthrough of using SQL Server DirectQuery with Power BI that may provide an additional perspective and information about securing and sharing the solution.
I’d also like to give a plug for a terrific Power BI architecture diagram poster created by Dustin Ryan. I love the simplicity of this comprehensive graphic. You can download the PDF file from his blog.
How does the official end of support for SQL Server 2005 affect companies using Reporting Services, Analysis Services and other BI features?
I was recently interviewed for a CIO Magazine article by freelance writer Andy Patrizio about what the end of support means for CIOs. Thanks to the SQL Server marketing team for including Data Platform MVPs to represent the product. A link to the article is included at the end of this post. By one estimate, there are 13 to 15 million Windows Server 2003 machines currently in use and one-in-six have some edition of SQL Server 2005 installed. That’s at least two million SQL Server 2005 instances that will no longer receive support or patching as of April, 2016. The rules of HIPAA and PCI will require these machines to be upgraded to remain in compliance, not to mention to be safe from malicious attacks and other security threats. Many of these legacy systems may be supporting old, special-purpose apps that just don’t scream for an upgrade. Production databases will benefit from the innumerable enhancements made to the product in the past ten years. For app-support databases, these can be consolidated and moved to centralized servers to replace all those old dusty machines in the custodial closet. Licensing costs apply to hardware and not to individual databases so consolidating old databases can usually save money up front.
My consulting clients often cite cost and stability as their primary reasons to stay on older database platforms. In the past, a common rule of thumb was is to remain one version behind and to skip every-other version, upgrading the last version only when a new version is released. This practice might make sense for Quicken, desktop Windows or for database software ten years ago but it doesn’t make much sense today when the advantages outweigh the risks. Sure, wait for the first service pack and by all means, do what you must to manage licensing costs, but mission-critical data systems are the heartbeat of modern business and need to perform and function.
There are many good reasons to upgrade from SQL Server 2005. Thinking specifically about BI and reporting capabilities, here are a few:
The BI platform began to mature in 2005 and really became fully-baked in the versions that followed. Reporting Services was introduced for SQL Server 2000. SSRS 2005 was more of the same but the best features like advanced charts, sparklines, gauges, KPI indicators and maps were added in 2008 R2. The product stabilized and drifted for the next few versions and now we’re going to see big improvements again in SQL Server 2016. If you don’t plan to move to SQL Server 2016 any time soon, at least upgrade SSRS to SQL Server 2008 R2. The report models and Report Builder 1.0 designer introduced in SQL Server 2005 didn’t last and have since been deprecated and replaced with more capable technologies. If you’re using this tool, stop and move on. It’s actually not supported now and simply won’t work in the future.
Analysis Services got a big architectural makeover in SQL Server 2005 but it took another three years to fully stabilize the product. SSIS also started in SQL Server 2005 but it took another full version to stabilize and mature. Outside of the BI tooling, the whole SQL Server relational engine is far more stable, secure, high performing and fault-tolerant.
The bottom-line is that you absolutely must upgrade your data platform at some point. The reasons are so obvious and self-evident that it almost doesn’t warrant further discussion. The question is not “if” but “when” and how often to upgrade. Moving to the most recent version of SQL Server may or may not be right for your business. An analysis of your specific requirements and the return you will get on your investment should help you decide whether you move to THE MOST RECENT version or A MORE RECENT version of SQL Server. Whatever the case, you need to have a plan and you should review, revise and execute that plan every few years. An end-of-support event like this is a good motivator and chance to put that plan into motion.
Thank you to the Microsoft MVP Program for awarding me MVP for six years running! I love this program and look forward to another year of great community events and opportunities to advise and mentor.
This year I had the privilege to speak at numerous SQL Saturdays, conferences and user groups, help develop a certification program for the University of Washington, contribute to books and papers, moderate forums, test new products and advise Microsoft product teams, direct the Oregon SQL PASS Chapter, and work with consulting clients to develop database and BI solutions using cutting-edge technologies. I’m planning the Oregon SQL Saturday this year and will be co-presenting a pre-conference at the PASS Global Summit.
I’m always looking for new opportunities to support the SQL Server and Microsoft Business Intelligence community. If you’re looking for a speaker, writer or need some help, please contact me.
Watch below for live updates during the conference
Stay tuned and you’ll find out what’s going on during this week in San Jose. I’ll be blogging from the conference as announcements are made and as the rest of the community buzzes about new developments in the community and here at the BAC in San Jose, California.
If you haven’t downloaded the pocket Guidebook, do it now. Just go to http://guidebook.com/g/passbac/, install and then search for PASS Business Analytics.
Tuesday, May 6
I arrived this evening, flying in from Portland. I met up with my long-time friend and co-author, Grant Paisley from Sydney, Australia. It all starts to happen tomorrow with the all-day pre-conference sessions. This is where the deep learning takes place. Every year, people who have serious interest in learning to use a technology or business tool spend a full day with some of the most respected industry professionals. They go deep and thorough, usually showing how to apply bet-practice skills to build a complete solution or tackle some serious issues. Registration for the preconference sessions will still be open on Wednesday morning until the rooms are at capacity.
The Power BI iOS app will be available this summer Reporting Services will run in Power BI – by the end of summer Power BI will work with on-prem data sources without using scheduled data refresh
Forecasting & Time series analysis – line chart provides time-series prediction with error ranges & outlier correction adjustments – WOW!
Demo: Hawaii tourism survey:
Interesting insights about the spending per island and where visitors come from
Question: “why do Japanese tourists show more affection than other cultures?” – answer: More Japanese honeymoon in Hawaii
Julie, a volunteer from the audience, is doing the next unrehearsed demo…
WE HAVE HEAT MAPS in Power VIEW!!!! — Jun Underwood is hyperventilating
Drag a chart data point out to create another visual and drill-down – very cool!
Power View snaps and resizes
Bump and Shake gestures added – automatically separates a chart into multiples or combines two charts into one visual by dragging them together with two fingers on a touch screen.
In conclusion, the features shown today will be introduced in the next couple of months through Power BI in Office 365.
———————————————————————————————————
Attended a great session presented by Devin Knight for beginners about building an end-to-end BI solution using Power View
Devin is a very good presenter and was able to very clearly articulate the value of the Power Pivot and Power View toolset through live demonstrations.
———————————————————————————————————
Great quote from Gartner in Michael Trjedor’s session on Microsoft Analytics & Visualization: "By 2015, organizations that build a modern information management system will outperform their peer financially by 20 percent." – Gartner, Mark Beyer, "Information Management in the 21st Century"
———————————————————————————————————
Rob Collie: Your own Data Revolution
“It doesn’t have to be industrial scale to be industrial strength”
“The human prison”: The human brain is naturally trained to work within the boundaries established from past experience. If only certain questions could be answered by a system/report/spreadsheet/database/model/whatever, we’re not likely to ask other questions.
“apples and oranges”: Low hanging fruit…We often make unfair comparisons and establish benchmarks (e.g. KPI targets). There are often/usually more variables to consider in a comparison.
These tools & techniques allow our brains to thin the way they want to rather than the way they’ve been trained to think.
“The Data Gene”: 1/16 people have the data gene – in all demographics & at all levels of the organization.
You’re a data/business analytics conference… That’s not normal.
The people who mock those who built the pyramids are “normal”.
Data gene is an interest not a level of intelligence, no PhD necessary
There is no cure
———————————————————————————————————
Alberto Ferrari: DAX Solutions from the Field
“I’ve stopped believing in Santa Clause, the Tooth Fairy and Microsoft Marketing”
“The primary key in the fact table is your worst enemy”
The first step to optimizing performance should always be to reduce unnecessary distinct column values
Use Kasper’s memory size Power Pivot model to analyze the memory footprint of tables & columns
Use SQL Server Profiler, clear cache
Build the model with necessary columns
Find columns using the most memory
———————————————————————————————————
Dejan Sarka: Advanced Analytics in Excel 2013
if you would like to get a complete picture of the end-to-end data analytics capabilities in Excel, watch the recording of this session. Dejan covered all the essentials at an advanced level. This excellent presentation moved very quickly. Emphasis on data mining/predictive analytics.
FridayKeynote
It’s 8:05 AM Pacific Time on the 9th of May, 2014 and the keynote session is beginning…
This session is highly visual and to get the message, I will update this post with pictures that become available after the conference.
David McCandless, keynote speaker, is visualizing a billion dollars using a tree map/heat map
“Debtres” … animated tetris visual that assembles a heat map
Trendline example: what trends up in the Spring near Easter and Christmas and then a little each holiday and weekend?
…relationship breakups via FaceBook
Data must be visualize in context (e.g. pure sum vs ratio… )
Strip out the labels and leave the colors and shapes to set the visual context
This series of video sessions will demonstrate how to create a business intelligence solution with sets of business data from multiple sources. In the first session, I import business records from 5 different D&B data feeds using the Windows Azure Marketplace with desktop BI tools in Excel 2013 ProPlus. In future sessions, I will combine public data sets, use features of Power BI and migrate to an enterprise scale BI solution with SQL Server, SSAS tabular and SharePoint.
D&B data fees in the Windows Azure Marketplace can be used to get detailed, current demographic and financial information about specific businesses or companies in different regions and categories. These business records can be used for things like market analysis, sales, verification and research.
Session 1 – Getting started (48:00) Analyzing Dunn & Bradstreet business records data from the Windows Azure Marketplace using Excel 2013 Power Query, Power Pivot and Power View to create a basic dashboard
To watch this video with more playback controls, you can also view it on ScreenCast here.
Session 2 – Enhancing the Solution (32:00) Combining Dunn & Bradstreet Customer Records with Public & Enterprise Data Geographic visualization with Power Map, publishing to Power BI and using Q&A to search a model with natural language
Good technology books usually come in one of two forms. Some of the books on my shelves go deep technically, contain useful tidbits of code but read like the phonebook. Just a few are interesting and insightful. This book is a rare gem that does both. Paul te Braak is well-known in the Business Intelligence community for his expertise and contributions and he delivers a unique guide that starts with the basics and proceeds to cover all of the essentials with depth and practical examples to solve some challenging business problems.
You might expect a book that introduces fundamental concepts to gloss-over advanced topics and avoid complex applications. This book covers the essentials of data modeling and analysis with Excel and Power Pivot in language that is plain and easy to understand but it doesn’t stop with the basics. It covers practical and useful applications without insulting the reader’s intelligence. As an experienced data modeler, I found several useful techniques and new methods to use the tools and language. Paul’s coverage of practical techniques spans the spectrum of business applications and product features. This is a rare book that is not only a good tutorial with many hands-on examples that can be repeated by the reader but it’s also a great reference of useful techniques and code samples.
Highlights include:
The integration of Excel features such as ranges, tables, pivot tables and pivot charts with the additional features of Power Pivot and Power View that extend and enhance these capabilities
Examples and instructions are directed at Excel 2010 users and the author compares some the different features in Excel 2010 and Excel 2013.
Fundamentals of the DAX calculation language
Importing data as text, different date formats and implied data type columns
Beyond the basics, a schema.ini file is used to define column data types
Importing data from a database, working with database tables, views and queries, managing connections and challenges encountered running the table import wizard multiple times
Data feeds using OData and using a Reporting Services report to provide a data feed
Decisions a designer makes to enable the user’s experience when browsing a model. This includes sorting values, navigating hierarchies that enable drill-down interaction.
DAX “X” functions (SUMX, MINX, etc.) to perform row-level aggregation
Working with parent-child hierarchies using specialized DAX path functions.
Advanced browsing features, adjusting pivot table options to optimize the user experience
Building and using KPIs and using alternate table relationships
Time calculations and date functions. This chapter covers running totals and totals to date
Date part aggregate functions (MTD, YTD, etc.),
Essential data math and comparisons
LastYear and PriorPeriod functions, TotalYTD
Manufacturing calendar, working with “445” dates
Creating a dynamic relative time measure, using a shell dimension table
Using DatesBetween to show the average value for the past 10 days
Apply advanced modeling technique to bin, sort and rank values for reporting
Expand concepts introduced in chapter 3, using the DAX “X” functions to perform row iteration in advanced financial applications
Defining and working with many-to-many relationships. This is often no trivial task to completely understand many-to-many relationship requirements and to apply a working solution that provides the intended results
Addressing inventory and stock-keeping challenges
Conditional aggregation at different levels
Budgeting and forecasting vs actuals
Programming Excel to enhance the users experience
Excel VBA event programming to respond to slicers
Using cube functions
Interacting with charts and slicers
Building solutions for the enterprise
Using the SSDS Tabular designer
Migrating Power Pivot models to Tabular server solutions
![clip_image002[7]](https://i0.wp.com/sqlserverbi.blog/wp-content/uploads/2016/07/clip_image0027.jpg "clip_image002[7]")
![clip_image004[9]](https://i0.wp.com/sqlserverbi.blog/wp-content/uploads/2016/07/clip_image0049.png "clip_image004[9]")
