Voice of the DBA

Tag: Database Weekly

  • Do you use DAX?

    It’s not that often that I see many posts about DAX. Despite the Data Analysis Expressions being used in Power BI, it seems that relatively few people call this out in their work. Chris Webb writes about it often, and this week is no exception. There’s also a short piece on time series from Philip Seamark and another one on optimizing expression from Marco Russo.

    DAX came out of the Analysis Services world and was the way that Microsoft expected people to analyze data in PowerPivot. That never quite became as popular as Microsoft hoped, but the expressions were folded into Excel and still heavily used in SSAS, but it’s really become the way that you can assemble amazing reports in Power BI. If you’re looking to get started, you might check out the Guy in a Cube, Adam Saxton, and his videos on DAX.

    DAX isn’t a native part of Excel, despite the fact that many of the functions are similar or even have the same name. You can use these expressions in the PowerPivot tab in Excel, which you can easily enable. Once you’ve done this, then the power of complex analysis using DAX is available for you.

    Should you learn DAX? That depends. If you need to build reports, or your users are asking for help with more complex analysis, then this might be a skill that helps your career. Your users will appreciate the way in which they can view data with more complex time series and statistical functions available. This seems especially valuable when looking at data across time, like comparing previous periods to current ones.

    As I look around, there are seemingly endless ways in which you can express your creativity with DAX to build more interesting reports. Some of these techniques are useful, some just fun (or perhaps silly), but a change of pace or a new view can help users become more engaged in your reports. They can be fun to build as well.

    Just remember that ultimately you need to use DAX to solve problems and generate insights. Whether you use Power BI, SSAS, or just PowerPivot, don’t build complex reports just because you can. Make sure you understand how the functions enable you to analyze data and the meanings behind their results.

    If you’re looking to get started, we have a great Stairway Series on DAX that can help you, in addition to watching the Database Weekly newsletter for new resources every week.

    Steve Jones

  • Are you GDPR Ready?

    The GDPR is being enforced as of yesterday. It’s been a law for two years, but fines are now going to be assessed for violations. If you’re like me, you’ve been getting a lot of different types of privacy policy updates, new opt-in requests in email, some notices in email with the burden on you to opt-out, and more. A few people joked about how many they’ve been getting, and certainly I’ve seen no shortage of updates. I’ve even seem some updates to services that don’t allow access to content, such as YouTube, without clicking some accept button.

    It’s interesting to see the various approaches being taken. Last week in London, Redgate held a SQL Privacy Summit and I was honored to host a panel discussion from various industry experts. They had different takes on the GDPR, though most of these people were pro-GDPR, happy that some proper data handling was being enforced. That’s the attitude that many DBAs in know in the EU, as they now have some legal reasoning why we should implement better data handling and security practices.

    However, I’ve also seen that there are different interpretations of how to deal with data. Do you need to ask all customers to opt-in? Can you continue to use data in development and test environments? Can you process data as you already have if you disclose what you’re doing? Is the burden on the company or the data subject? I’m sure we’ll see various decisions and rulings from regulatory authorities across the next year as data subjects complain and companies try to do the minimum level of work.

    The idea of data being somewhat co-owned by a business and an individual is fascinating  I see both sides, and I certainly would like to have some rights over data about me. I definitely think my address, my date of birth, and more should be secured and companies that use my data should have some liability if it’s disclosed. I’m not sure about rights over how it’s used, but that’s certainly a discussion that’s coming.

    I’ve already seen one organization file suit over access to data, because they’re being forced to consent to handling that they disagree with. That is going to be something I watch carefully. Can a company change their terms arbitrarily, ask me to consent in a take-it-or-leave-it fashion, and withhold access to data? Do I own my messages and data stored in services? Is it co-owned?

    Like it or not, the GDPR is forcing us to have some discussions and debates about digital information, which is good.

    We’re mostly ready at Redgate, and certainly continuing to do work. Ultimately, our reading of the GDPR (with some backing from auditors), is that we don’t have to be perfect today, but we need to be making an effort and be able to prove that we are doing so. So on a day after the GDPR went into enforcement, what are you doing? Do you think you’re ready?

    Steve Jones

     

  • Taking Shortcuts

    Many of us work in situations where we feel pressure to get work done at a pace that’s faster than we might like to move. In some sense, this is the nature of life, where customers and clients always want something done immediately. Whether they’re ordering food or building a house, it seems that quite often the customer expects the creator to just work harder and faster, without making mistakes, and deliver the goods quickly.

    That may or may not work, but if does often result in issues in software. While we can fix them, there can sometimes be larger issues, especially where sensitive data is involved. There was an incident recently that reminded me of this, though fortunately, it appears the data loss was minimal in scope and sensitivity.

    The mobile app at a recent security conference leaked data. The builders of the app embedded security keys and passwords that allowed anyone that registered to download a database of attendees. Fortunately this was a limited set and it appears only names were exposed. However, it could have been much worse, especially if this were a typical non-normalized database that might contain all data about an attendee in one row.

    I don’t know the timeline here for development, and I certainly don’t know the requirements. I do know that embedding keys and passwords into application is a bad idea, and even worse when those applications are going to be installed on customer devices. These are fundamental rules, and I certainly hope that whoever worked on this application, and anyone reading about this story, knows not to do this again.

    No matter how rushed we are, it’s important that we follow some practices and include some seucrity in our systems. I’d argue that data security ought to be number one and built into the system from the start. As the GDPR asks, we should be ensuring this is included by design and default. As much as it might seem that new legislation is overreaching and burdensome, I’d argue that mistakes like this one are all too common when we feel pressure to get work done. We shouldn’t be making these mistakes, nor should be be pressured to ignore security for the sake of expediency.

    Steve Jones

  • Opportunity or Restriction?

    I’ve been at the Redgate office all week, broadcasting SQL in the City and meeting with product teams. Quite a bit of our focus is on the GDPR law, with enforcement beginning in May. We’re building and enhancing tools to help you cope. That means I’ve been spending a significant amount of time trying to learn and comprehend the potential impact to data professionals, both to help guide customers, but also to ensure our software will help them ensure compliance in their data systems.

    Over the last year I’ve seen lots of doom and gloom, everyone needs to fix their systems and ensure data can be deleted on demand concerns in the media over the GDPR. Ultimately I think there is work to be done for many companies in the EU or those that sell products and services to the EU, but we don’t quite know to what extent we need to change existing applications and databases. Lawyers and solicitors will sort some of this out across the next few years, though I certainly think any data breaches in the next year will be dealt with more harshly by regulators if organizations haven’t made any effort to secure their systems.

    There are also some simple things that I think most of us should just do. When I first saw the addition of data classification in SSMS, it seemed fairly trivial. However, the more I’ve thought about it, this simple addition is a way of ensuring that I can easily spend a relatively little amount of time to just think about the information in a database. Just tracking this down can be a pain, and if the information isn’t recorded in an easy to access format, it’s easy to forget what items need our focus. Using Extended Properties is a great idea, as the information is kept with the database, but this means that a better interface than the table properties is needed. There are also a few potential problems doing things this way, but this is a good start to becoming better data stewards.

    That’s what we need. A few good starts. We need to see this as an opportunity to clean up practices and move forward in a way that shows us to be professional data professionals that take our responsibility for data security, accuracy, and usage seriously. This is a chance to move forward in a way that reduces our risk of losing data, of becoming the next “headline” corporation or government agency that makes a mistake. Instead, we can embrace this as an opportunity to find new ways of managing our data and extracting information while still complying with data privacy rules.

    I read a white paper that talk about the challenges of IoT data in a GDPR world. There are issues to be concerned about, but this also means there will be opportunity for those that devise better data handling methods, that learn to clearly disclose their purpose and practices. I suspect most people realize that there is a certain amount of information that companies collect, and that if there is some value or usefulness that company gives back, we can accept their data storage. Where individuals often become concerned is when organizations move their data to other parties without their consent.

    To me, this means it becomes more important for an individual company to understand and process their own data. There will be less movement of data between companies, and perhaps less ability to purchase and import data from others. We’ll need to help our organization extract more value from the data we are allowed to hold, meaning those of us that are data professionals will become more important. At least, that’s what I hope happens.

    Steve Jones