Voice of the DBA

Category: Editorial

  • Python in SQL Server

    The Microsoft Data Amp event was this week, with a number of SQL Server announcements. We now know this is going to be called SQL Server 2017, which is no surprise. CTP 2.0 was also released, which includes a few improvements, but one in particular caught my eye. Python code can now run inside SQL Server, meaning we are not limited to the R language for data analysis. That’s exciting to me and I think that’s a great change. It’s one that’s been rumored for some time. I like Python, and think it’s a nice, elegant language. My son has been learning it in college and I’ve spent some time playing with it after the SDTIG got me interested with their beginning Python and Data Science modules. Since then I’ve spent a little time learning R, but find it to be a cumbersome language to work within. Having code embedded in a string in a SQL Server stored procedure made things harder, and I kept hoping that Python would be added.

    It’s there now and you can play with it. While you might debate whether this is a good use of SQL Server resources, I think for quick, short, specific analysis calls, it’s a good choice. Certainly complex calculations and data manipulations are not always efficient in T-SQL and having another option is great. You’ll want to be careful when you run Python or R code, but these are good options to have. There is also GPU processing support is available to take some load off your main CPU.

    CTP 2 is supposedly feature complete and there are more changes than just Python. Tempdb has a better setup experience, we get the first version of graph data and query support, resumable online index rebuilds, adaptive query processing, and more. You can read the announcement, which isn’t heavy on technical details, but gives you an idea of what’s changing. I’m not much of an SSAS person, but there are some enhancements in this platform as well.

    There are other improvements and announcements this week. Azure Analysis Services is generally available (no longer beta), as as Azure Data Lake Analytics and Data Store. DocumentDB is improved and some Cognitive Services are GA, and in general, Microsoft is all in on the analytics and deep learning.

    The world of data in the Microsoft world is growing and changing rapidly. We’ll have a new version this year, just a little over a year after SQL Server 2016. I expect that we will continue to see more, and new changes, occurring at a rapid pace in the future. None of us will completely keep up and become experts in all areas, but we have lots of options for new learning and experimenting with advanced features. I’d encourage you to take a little time here and there and read about and play with the new features. If you have Docker, you can get a version of SQL Server 2017 up and running without installing anything on your machine to see if there are any changes that might help your organization take better advantage of  the data you work with every day.

    Steve Jones

     

  • SQL Server Telemetry

    One of the things that I’ve seen blogged about and noted in the news over the last year is the amount of data being collected by various software systems. In particular, I’ve seen numerous complaints and concerns over what data Microsoft collects with its platforms. Perhaps this is because Microsoft is the largest vendor, and plenty of other software collects usage information, primarily to determine if features are being used or working correctly. I think much of this is overblown, but I can understand having sensitivity about our computer usage, especially for home operating systems.

    Microsoft conducted an extensive review and published data about what is being collected and why. Windows 10 and other products have undergone review and must comply with the published policies. There’s even a Microsoft Privacy site to address concerns and explain things. It’s an easy to read policy that explains what  Microsoft is collectin, if you’re connected to the Internet (if you’re not, don’t worry, no bloating files). That’s a huge step forward in an area that is evolving and something I wouldn’t have expected to see in the past. I am glad Microsoft is making strides here, even if I may not agree with specific items in their policies. I do think that most of the companies collecting this data are doing so to improve the products, not spy on customers. I’m sure some do, but likely smaller organizations with some sort of criminal intent.

    As data becomes more important, telemetry for software is potentially a data leakage vector where private, personal, or customer information might be leaked. Certainly as more speech and other customized services are used in businesses, I worry about what data could be accidentally disclosed. After all, it’s not that super powerful smart phone that is actually converting audio to text in many cases; it’s a computer somewhere in the vendor’s cloud.

    With databases, this has also been a concern from some people. I’ve seen the Customer Experience Improvement Program for years and usually opted in. I’m rarely doing something sensitive and I hope that with more data, Microsoft improves the platform. That’s the stated goal, and I’d seen them talk about this a few times. The SQL Server has moved forward and published an explicit policy that spells out what and when data is collected. It was actually just updated recently and all new versions of the platform must provide this information (if anything is different) and adhere to what they disclose. There is a chance that user data could leak into a crash dump, though users have the opportunity to review data before it is sent to Microsoft. I’m not sure how many will, but they have the chance.

    I would like to be sure that anything sent is secured, and perhaps have an easy way to audit the data sent in a session, but I know this entire process is evolving. One important item to note is that customers can opt-out of data collection for any paid for versions of SQL Server. That’s entirely fair, but if you have regulatory concerns, you should be sure that you don’t restore production data to development machines. You shouldn’t anyway, but just an FYI.

    Usage data is going to be a part of the future of software, especially as more “services” integrate into what we think of as software. Those services aren’t always going to be under our control and certainly part of the reason many of these are inexpensive is that additional data is captured about the people using the software. I hope all companies publish and adhere to some sort of privacy statement, and maybe that’s a good start. Rather than any regulation on specific privacy that must exist, start forcing companies to publish and stick to whatever policy they choose.

    Steve Jones

     

  • Liable for Data Loss

    When I first installed Windows 7, I was thrilled. Finally, Microsoft had slimmed down and improved the OS performance rather than continuing to bloat it larger. After Vista, Windows 7 was a welcome change. Windows 8 was different, but for me as a desktop user, it wasn’t much different. I moved to Windows 10 as a beta user and thought it performed well. A little slower than 7, but overall a good move. I was glad to get the upgrade notice on a second machine, but it was annoying. Trying to easily delay or avoid the change in the middle of some travel was hard. I certainly could sympathize with the users that complained they didn’t want the upgrade and couldn’t easily avoid it. I’m glad Microsoft changed this process a bit.

    There were people that accidentally, or felt forced, to upgrade. Among those, some of them lost data and decided to sue Microsoft. Let’s leave aside the Windows upgrade process, Microsoft’s decision, and the merits of this particular case. Those are separate issues from the one I want to discuss, which is the liability for data loss. At the core of the lawsuit, the time and information that people have lost is an issue that few of us have had to deal with in our careers. At least, most of us haven’t had to worry we are liable for the issues our software might cause.

    Are we moving to a place where a person, or more likely a company, is going to be held liable for the data loss from upgrades or patches? There is the ability of customers to initiate legal actions, but strong EULAs and prior legal decisions seem to indicate that much of the liability resides with customers and vendors aren’t at fault. Is that a good thing? I’m not sure, but I do think that as data becomes more important and is used to justify decisions or drive business actions, there will be a push to ensure that anyone performing data changes with their software during patches and upgrades is liable for issues.

    I’m surprised we haven’t seen more of accountability from software firms to date, but I think much of the legal issues have been settled without much fanfare and strong non disclosure agreements. I’m not sure this is the best solution for anyone, as to force some improvement and better quality for software, we need to take better care of our data. I don’t want us to move slower with software development or deployment, but I do want quality to improve.

    Steve Jones

  • More Power in Your BI

    I’ve seen lots of data visualization tools over the years. Cognos, Microstrategy, ProClarity, and more. While I haven’t been a BI person for most of my career, in many of my positions, someone has wanted to try some new tool and eventually I’ll get involved somewhat. I remember first seeing the OLAP cube browser in SQL Server 7, and showing my boss a quick demo. While it wasn’t something that you could let an end user have, it also got my boss excited, and they started a project as I was leaving to implement some OLAP for the sales department.

    When I first saw Tableau years ago (2008?) at TechEd, it was the first tool I’d seen that was really slick for the end user. I expected they’d grow quickly, and they have. Many people have looked at the Tableau tools as the standard for data visualization. For years nothing looked close, and as much as I appreciated Microsoft moving PowerPiviot and other tools to Excel, they weren’t as nice to use as the graphical tools.

    That changed with Power BI. When I first saw this tool, instantly I could start to see the power of the tool. Even altering and changing a tool had an ease and power that I hadn’t seen since the Tableau tools. The initial release had lots of limitations and was just on the web. It was slow, and refreshes from remote data weren’t great. There were security limitations, and I wasn’t sure that the vision for the product made sense from Microsoft, even though I could see tremendous potential.

    Things have improved, and I am somewhat amazed with the power of Power BI. A simple sales report is interactive. I can click on something intuitively and the values I see will adjust to focus on that item. What’s more, other graphs on the screen adjust. However, I don’t have to stick with simple graphs, I can add better imagry, such as this wine report, or this airline maintenance report that drives work. I was especially impressed with the analysis of Stephan Curry (which won the contest). Some of those reports I couldn’t imagine trying to build with other tools, especially SSRS.

    Power BI is a great tool, and if you need to provide visualizations for end users, I’d urge you to take a look. The Power BI Desktop tool is great for modeling and working with data. I find myself using that for quick looks at data, building a graph in a way that’s easier than in Excel. I went through Microsoft’s EdX course, and was somewhat amazed to see the vast capabilities available in the product. Guy in a Cube, Adam Saxton, has a YouTube channel where he and Patrick LeBlanc (@PatrickDBA) bring you constant training and tips on how to get the most out of the tool.

    What’s more, I keep finding more and more Power BI blogs and posts that I can add to Database Weekly every week. So many people are experimenting and finding ways to better analyze data with Power BI. This week we have a continent slicer, data privacy, collaboration, and more. With montly releases, I find that people are constantly digging into the possibilities and helping you learn with them. There are even developers building custom visuals that you can download. There’s even one for acquarium lovers. If you want to learn about these, Devin Knight blogs regularly about custom visuals and we include many of those links here and on SQLServerCentral

    Power Bi is a great way to empower your users, reduce your reporting load, and make everyone happier. Power BI is a great way to let users experiment with data and actually decide what reports are worth investing in more with IT resources to perhaps optimize the performance of certain visuals. Give it a try to day, especially the desktop version, and see how you can easily start to examine data that might be important to you. Even the SQLDBAWithaBeard finds Power BI helpful.

    Steve Jones