“TORONTO — Customers’ names, shipping and billing addresses, order dates, methods of payment and the last four digits of the credit cards used were on full display for orders of a $1,500 laptop computer in Ontario, a $700 spin bike in Saskatchewan, and more than $1,100 worth of baby gear in B.C.

These are just a few samples of Walmart.ca orders CTVNews.ca was able to call up on the retailer’s website Tuesday that demonstrated the ease with which customer information could be accidentally exposed.”

https://www.ctvnews.ca/mobile/sci-tech/data-exposure-flaw-on-walmart-ca-revealed-personal-customer-information-1.5256105

Be Careful Testing Flaws

One of the stories I talk about is a Canadian man who discovered the same type of flaw in a provincial government website. He, being a concerned by lazy* user, wrote a script to demonstrate the flaw. When he reported the security risk, rather than being thanked, he was arrested for hacking.

So when you find these sorts flaws, be careful what you do in testing or demonstrating them.

Do Your Customer Service Reps Know How To Respond?

I have reported exposed customer data and security flaws dozens of times. Most of the time, I get zero response. Or instructions how to stop seeing other peoples’ data. But I’ve only once had my reports taken seriously. This was mostly do yo the fact that the person I reported it to had no process for dealing with the report, even people who work in IT.

I recommend you, data professional, ask if your public-facing staff have a process for handling reports.

In today’s news, we found out that Quest Software has acquired erwin Inc. This is a similar acquisition to Idera’s buying Embarcadero. Quest is primarily a systems tool vendor as Idera is. This purchase is also interesting because Quest also owns TOAD, the very low-cost leader in data modeling/database design space. erwin Inc is the vendor of erwin Data Modeler and other data management tools.

I Think We’ve Done This Before…

It’s going to be interesting to see how this integration pans out. I’ll update as more details come out. I’m also curious if they will maintain the “lowercase” product branding. I don’t mind it, but is sure makes it a challenge to write about.

What do you think this means for the data modeling tool industry?

Quest Software, a global systems management, data protection and security software provider, has announced the acquisition of erwin, Inc. The deal adds data modeling, data governance, and business process modeling solutions to the Quest portfolio, providing customers with end-to-end capabilities to better understand and govern data.

https://www.dbta.com/Editorial/News-Flashes/Quest-Software-Acquires-erwin-Inc-144595.aspx

PASS faces a uncertain future.  We won’t know for sure what is going on until they announce their plans, but until then, there are some time-sensitive steps I’ve decided to recommend to you. 

This post had a few contributors, by the way.  It’s the result of several decades worth of experiences running professional associations, groups, and events.  I have personally lived through the almost complete closing down of another professional society, so I’m sharing all this in the hopes of helping the community continue, no matter what happens. That group had thousands of users, dozens of chapters, and at lot of vendor partners. Many of these groups were caught off guard when the websites and access to data went away, all of a sudden. Please keep this in mind, though: I’m hoping that PASS is able to carry on under a new culture and governance structure.  I’m rooting for it, even.

I’m not saying, either, that the PASS governance entities would ever do anything malicious to harm us. I’m just pointing out that in times of turbulence, certificates can expire. Domains can expire. Hosting bills can go to the wrong e-mail address, resulting in servers being shut down (this happened at that other association). Chaos is bad for all organizations and stuff happens.

If you run a PASS user group, a virtual chapter or a SQL Saturday, you should taking steps for potential future without PASS. If you are a speaker, there are also steps you should be taking. The issue for all of these organizations is that PASS likely owns all of their membership data, and chapters/events are prohibited from downloading that information, and in most cases cannot. If there is a bankruptcy, this data may be considered an asset of PASS and likely be sold at an auction or transferred to a creditor. I hope that does not happen.

What can community leaders do to protect themselves and their organization? Let’s break it down by the type of organization.

User Groups

For the most part user groups operate mostly independently from PASS. Per the chapter agreement, they must be separate corporate entities, with separate boards and financing. However, many PASS chapters rely on PASS to distribute email to their chapter lists which are also maintained by PASS. User group leaders do not have access to this data, especially the contact data for their own members.

What many user groups have done in recent years is use services like Meetup, or Eventbrite to manage their events.  These services support promotion of your event, plus pre-registrations for your meetings.  I used both of these and prefer Meetup for their better promotional aspects. Some of my meetings had 50% new attendees because they found out about my user group via Meetup.

While there is a nominal cost associated for pro tier ($200/yr in the US, Canada, UK, and Australia, and $120/yr for the rest of the world), Meetup has the benefit of using their social media graph to connect your members with other groups in shared areas of expertise. I’ve signed up as a user with a technology interest and I get notifications about various technology events in my area. Meetup has been very much worth the cost just for reach.

You’ll want to do this right now, because as long as the PASS website services are still up you can still e-mail your members.

The other consideration you need to have as a user group leader is your website. Once again some chapters have long hosted their own website, but many chapter rely on PASS hosted websites. There are a number of services you can use to host a website—if you haven’t used WordPress before, you might want to read a tutorial.  There are other free or affordable website hosting options out there, but I recommended WordPress for it’s numerous plugins and regular updates. While it’s not required, I recommend you consider registering a new name and domain for your user group, as you will likely not automatically be allowed to use the PASS brand if PASS is dissolved or sold. All this is unknown at this point.

You may also want to establish some sort of mailing list for keeping in contact with your members.  That might include establishing an account at groups.io (I really like their discussion lists features), setting up a LinkedIn Group, or something like that. I personally no longer like Facebook as a option. Nor do I see it as appropriate for professional groups.

SQL Saturday Events

The potential for losing SQL Saturday events pains me most of all—SQL Saturday brought a lot of new people into the community, and the events were held all over the world. I’ve spoken at events on five continents and met all sorts of wonderful people, most of them via SQL Saturdays. Many organizers in Europe have already started doing their own events outside of PASS, and websites like Sessionize make this easy to manage. If I were running a SQL Saturday, I would want to recreate any important data I would need to transform my local full-day events to non-PASS events.  The most important of which are your sponsor contacts. Please read my legal disclaimer below before considering copying data from any PASS resources.

In recent years, PASS has not been as involved in SQL Saturdays, but the events are still quite popular. I think once the COVID vaccine is broadly distributed, these events will continue to operate, perhaps under a new umbrella.

Virtual Chapters

I had no idea how much training went through PASS Virtual Chapters—but it’s over 200 hours in 2020. Unfortunately, virtual chapters will be the hardest to decouple from PASS as they are 100% owned by PASS.  And their entire management system is through PASS’s website, and PASS licenses the GoToWebinar software used for the virtual training. I don’t have any great answers here, other than it might be a good idea to talk to some of your fellow virtual chapter leaders about the possibility of banding together and getting some sponsorship to pay for a website and teleconferencing software. If you can show that you are regularly getting 50-100 (or more) IT pros you can likely gain enough sponsor interest to manage those costs. However, taking in income also means you would need to establish a corporation making this process even more challenging.  And don’t forget that many of you, via MVP, MCT, or other status have access to Microsoft Teams, which is a great service to use for virtual meetings. At the time of this writing, Teams is free for everyone, although that may not be true in the future.

The virtual chapter video archives exist under the PASSTV YouTube account, which will may or may not exist if the organization itself goes away. The other thing you should consider is recreating a list of speakers who you have worked with in the past, if you don’t have one already. This way you have a starting point for any efforts moving forward.

Speakers

If you have spoken at a SQL Saturday, you know all your sessions are saved on the SQL Saturday website.  PASS built a great feature set there that lets you submit the same info, with customizations, to several events without having to rekey them every time. But if that goes away, you may lose some of your important data.

Personally, I have already put my best abstracts and metadata into Sessionize because it’s used by so many modern events.  I recommend you start using it as well, since it’s free and it does a good job of being a speaker profile system for all kinds of uses. I really do like this service.

Legal Disclaimer

You know I’m not an attorney and therefore cannot give legal advice. I have, as an expert witness, had to deal with a lot of litigation cases where data ownership and data quality come in to play.  So please be sure that any data you have access to right now is actually owned by you (user group, SQL Saturday, etc.).  To know this, you’d have to refer to any agreements or contracts you have signed, as well as get proper legal advice.  Whatever you do, don’t try to take data that does not belong to you. Please, just don’t.

I think the best way to avoid any issues is to recreate data you have used in the past.  That could mean giving your members the opportunity to self-register for other services or lists, for instance. It could mean going to speakers’ websites to collect their preferred contact mechanisms if they want to continue a relationship with your new user group. It might mean starting from scratch.

Finally….

The end or major shift of any community organization is a very sad day for a lot of people, but community starts locally first. Taking these steps can help you preserve your user group or weekend data events. Virtual Chapters face larger hurdles, but have a valuable product, so hopefully they have a viable future.

If you have other ideas for helping the community carry on, no matter what, please comment.

What is the Status of Enterprise Data Architecture?

This question proposed by an attendee was one I’ve been wondering about myself. I’ve not been working on large, enterprise projects for the last few years, so I’m not sure what the status us. I’m certainly seeing fewer requests for data modeling support (I’ve received only one in the last year). I’ve not had recruiters contacting me for help finding candidates.

I know that Data Governance initiatives have raised the need for having data architects and I’ve had a few discussions with companies about trying to fill those needs.

My recommendations for those looking for data modeling/data architecture work is to brush up on your data governance skills, or if you have them, making sure they are included in your resume.

What About Enterprise Data Modeling? How Has it Changed?

Certainly there are few projects where software applications and custom databases are being built. Serverless applications, commercial off the shelf and government off the shelf implementations have grown.

But that’s not the only reason I build data models. All those implementations still have data requirements. All have configurations and customizations that organizations need to understand.

The other areas where I’ve seen a good deal of data modeling is on integration projects, where a canonical data model is required to publish data to and consume data from. In fact, this is a great place to leverage industry standard data models.

Data News

We also talked about a couple of data profession items in the news.

This story, about a data manager for the state of Florida who alleges she was terminated/reassigned because she was asked to adjust data in a way she believed, was harmful to the public.

And then there was this confusing bar chart issued by the state of Georgia with misleading/awkward (depending on what you think happened) on the trends in Covid19 data.

Data Quality Tools

It’s been a long time since I had a client that uses specific data quality tools. I figure this is because I’ve spent the last decade working on troubled projects — those that can’t get into production. Those projects barely have any enterprise-class tools, often choosing to use manual processes and queries over more useful applications.

Some of the tools/vendors we chatted about:

• Informatica
• SAP
• Oracle
• Microsoft
• Tamr
• Infosphere

What is a Data Engineer?

This is the second conversation I’ve had recently about what this title actually means. During a recent webinar I did for the DMV Data Warehouse Meetup, an attendee said that he is a data engineer and that this title is scoped around data integration work such as moving and transforming data.

I’ve heard other definitions that come closer to a data professional who works will all kinds of data platforms and tools. For instance, my Microsoft certification is for an Azure Data Engineer Associate.

What is a Data Lake? A Data Warehouse?

In that same Meetup presentation I was introduced to the term “Data Lakehouse, a combination of Data Warehouse and Data Lake. So in office hours we chatted about what these terms mean and what other terms we have for…collections of data for consumption.

I gave this talk at WinOps London to a new audience. The discussion was fantastic as it usually is when an “outsider” comes in to talk about why data people need to be part of DevOps, even if we aren’t developers or operations professionals.

I think many DevOps professionals can be scared off by terms like Data Modeling or Data Governance, but it doesn’t have to be that way. Building a culture of data protection is something we should all embrace. My talk is focused on how to have those discussions and how to fit data professionals into DevOps approaches.

Oracle Blockchain Tables

This new feature in Oracle 20c offers a BLOCKCHAIN TABLE, one that supports blockchain properties within a database. It’s not the same as a full-blown blockchain service, but it does provide features for trust, and immutability. It will be interesting to see how these new blockchain tables are used in production systems.

Logistics and Data (Models)

I Tweeted about my recent experience with an Amazon delivery that was out for delivery in my city but never delivered. I waited another day, then logged in to Amazon to find out what was going on. My delivery has been delayed until 14 May. With no reason. This logistics failure made me wonder what was going on with the data about this shipment. How could the package be on a truck and yet delayed for more than a week? We discussed, “it’s always a data problem” and how logistics systems are being crushed by all the extra load of customers wanting to shop online rather than in-store.

On a side note, we also chatted about availability of alcohol sales in various locations.

Data Model and Table Definitions

We talked about where those beautiful, meaningful, and helpful attribute/column definitions are used. In my history of working with DBAs, I’ve found too many of them “don’t want their databases cluttered with all that text.” We also talked about getting these descriptive properties from databases into data models and vice versa. I wondered if developers knew that many of their tools could input definitions of columns from data models to help them use the right column when they write their code. I think it’s funny how getting and using definitions in tools, whether they be data modeling, development, database, or client tools, has been an ongoing struggle for decades.

“Team Data — data architects in particular, whatever their official title may be — have a job to do. That is to take more aggressive steps to protect data assets on behalf of customers and constituents.

The responsibility falls to data architects because few in most organizations are thinking about protecting data from the data point of view, so to speak. The lawyers and the security group think about it from the standpoint of data breaches, applications, SQL injections, and so on.

This article also has a video of my talk. Let me know what you think.

Data Discovery

An attendee is evaluating Azure Data Catalog, an Azure service I’ve been doing presentations and workshops on for the last year or so. This service allows you to connect to dozens of Microsoft and non-Microsoft data sources to “reverse engineer” these resources and annotate them with tags, comments, and stewardship information. This is a feature-lite, version 1 service. I’m hoping to see additional features added, as right now it’s an affordable resource for data discovery.

I will be blogging in the future more about this service.

Data Modeling Tools and Azure Synapse

Since I can connect to Azure Synapse (formerly Azure Data Warehouse) with SQL Server Management Studio (SSMS) as it shares a similar feature set to Azure SQL Database, it didn’t occur to me that I couldn’t connect with the major data modeling tools. I wonder why that is? Perhaps because users of modeling tools aren’t asking for this support? I promised to look into this with ER/Studio Data Architect, erwin, and PowerDesigner.

I should mention that I have authored a Azure Data Engineer Associate certification prep course that includes Azure Data Warehouse content. You should check it out.

Data Gravity and Kubernetes

I was a panelist on a recent NetApp-sponsored event on the successes and challenges of using Kubernetes, an open source, container orchestration tool. In this event, the topic of why persistent data is still an issue with Kubernetes was mentioned by more than one panelist. I suggested it could be because Data Gravity causes companion systems to need to bend themselves to work with large datasets than datasets to change to meet the systems around them. More on this topic in a future blog post.

What Will Make Virtual Conferences More Engaging?

While it seemed I was the only one who had spoken at or attended multi-day virtual events, we did discuss what might make these sort of events more effected and more acceptable to attendees.

Ugly Data Models

Too many data models I see refused to use better colour combinations, professional fonts and readable designs. In a recent webinar I did for Idera, I showed some examples of ugly and hard to use data models.
Many data modeling tool vendors have not updated their sample data models to take advantage of their newer design features, resulting in sample models setting “standards” that no modeler should use.

Data Governance

My webinar was about Data Modeling and Data Governance. I asked the question of whether we should consider data governance a “special” program or just the regular part of how we do data management.

SQL Server Management Studio (SSMS) Data Discovery and Classification

I demonstrated how to use SSMS to perform data sensitivity classifications on SQL Server databases. This is the third time this week this has come up, so I’m going to have to do some demos for you all soon.

Tech Conferences

I asked the question of whether or not participants thought we would be seeing in-person tech conferences before spring of next year. We discussed the availability of vaccines and their use as a contributing factor to this topic.

Graph Theory and Data Models

We talked at lot about how data models (and most models) are graphs and how we should have graph analysis and diagramming features to do graph processing against our models. I suspect I will be writing about this more in the future (as well as talking about it during Office Hours.

• Transaction vs. Analytical People
• Software Defined vs. Data Defined
• Data as Infrastructure
• Lies about Data
• Good Systems Doing Bad Things
• FAQs about this session
• …and more

I’d love to hear about points you think I should have included. I’m also always collecting data anomaly examples.

First Workaround: Change Windows Display Scaling Settings

At first I tried changing my Windows Display Scaling settings. I had to go all the way up to 300% so that I could read tiny text and icons in the ribbon. But the trouble with this workaround is that it scales all other applications, which made them unusable. It was an okay fix, but required lots of changing of my Windows Display settings.

Best Workaround: Change the Application Settings

I did some searching and found a community forum post by Joy Ruff on how to change settings of the ER/Studio application itself. I’ve annotated her instructions below.

“This is a known issue with 18.1 and 18.2, scheduled to be fixed in 18.3.0.

Meanwhile, please find the following steps to apply in order to resolve the issue. We will be working on updating the application so that this step will be done automatically in a future release.”

https://community.idera.com/database-tools/data-modeling/data-modeling–architecture/f/er-studio-data-architect/71240/icon-size

Step 1: Go to ERStudio Data Architect 18.1 shortcut link on the Desktop or directly to the exe in the install folder.

I chose to go right to the application, which on my machine is installed at C:\Program Files\Idera\ERStudio Data Architect 18.2 \ Your application may be installed elsewhere, so find the erstudio.exe you are running.

Step 2: Right click on the shortcut/exe and go to Properties.

Step 3: Switch to the Compatibility tab and click on “Change High DPI Settings”

Step 4: Set this property to “System” as shown in the screenshot.

Step 5: Click OK and Apply or OK to apply the settings change

As long as you’ve done this, your ER/Studio Data Architect application should be readable according to your system settings.

Step 6: Launch ER/Studio DA and it should appear correctly

Hope that helps!

Thanks to Joy for posting this workaround.

SQL Server Upgrades

Upgrading to SQL Server 2017, then having the ability to take advantage of running SQL Server on Linux, running with Kubernetes, and other new features.

Data Vault

Data Vault: This is something I have on my list of things to learn more about. I joked that Data Vault followers are a bit of a cult, but only because people seem to be such great fans of this approach.

Database-related Math

Understanding the math behind relational theory and graph theory. This is something I learned several decades ago. I’m not even sure I’d be able to read any of it now. But I remember the concepts, which helps me in my work today.

DBMS Naming Constraints and Limitations

Naming standards and constraints for DBMSs, such as Db2’s current limits, Oracle naming standards (used to be 30, now up to 128 for many objects) and SQL Server’s luxurious 128 character limit on table and column names.

Join us if you want to drop in and talk data.

“The answer is Data Gravity. Consider Data as if it were a Planet or other object with sufficient mass.  As Data accumulates (builds mass) there is a greater likelihood that additional Services and Applications will be attracted to this data. This is the same effect Gravity has on objects around a planet.  As the mass or density increases, so does the strength of gravitational pull.  As things get closer to the mass, they accelerate toward the mass at an increasingly faster velocity.”

– Dave McCrory, Data Gravitas Blog

He was specifically writing about the new way we architect systems in cloud solutions. But those of us in the data profession have experienced this even in on-premises systems: as data gets larger, the less likely we are to move data to applications, but to bring applications to the data. The reason? Cost, benefits, and risks. As data volumes increase, the costs to move data around in both currency and time means it’s cheaper to move services and applications to the data. Moving a half petabyte of data to multiple locations is bound by physics. Literally. The cost in time and money to move that much data around, keep it in sync (or mostly in sync) is non-trivial.

Data Modeling and Data Gravity

Is data gravity just a storage admin question? No. Data modelers and architects who understand their role in helping organizations make good data decisions also understand they must manage data about data volumes. You won’t find this responsibility described in most data modeling and design courses, though. Nor will you find it in most data modeling text books.

But most data modeling tools have a spot for you to collect (some via reverse engineering databases) the volume information as well as to project growths in these volumes over time. In McCrory’s term, we are measuring the mass of that data, which impacts the gravity of the data in relation to other IT resources.

Data Gravity Metrics are a Data Modeler Responsibility

We have a responsibility as data modelers to gather, curate and manage this data. It’s a type of business requirement and it’s critical physical design data that should be maintained in our data models.

This information is also an important metric for designing for data lifecycles: which data do we keep in hot storage, which to we keep in cold storage and which to we actually delete. Again, these are business requirements with physical design implications. Data models are the ideal place to manage this information.

Question for Data Modelers

Do you collect and manage this data in your data models? If not there, where? If you don’t, who does?

• How enterprise data management has changed over the last twenty years
• How various data modeling tools are doing: erwin, ER/studio, PowerDesigner
• What features we like in these tools
• How staff at data modeling tool vendors have changed
• The role of data governance in an age when fewer data models are being prepared and maintained
• How data governance is getting more and more focus due to privacy laws such as GDPR, CCPA, etc. are growing.
• How business data glossaries are required and how these contribute to ERP configurations/development
• How data analytics is driving data quality initiatives

Our next Office Hours will be Thursday at 5:00 PM EDT / 21:00 UTC

There are simple construction projects, coloring sheets, books to read.  Check it out if you are looking for some fun space activities.

And for you data professionals, there’s always data.nasa.gov to enjoy.  NASA’s Open Data makes for great learning and demo data. Check it out.

If you were a member of our dm-discuss over on Yahoo Groups, you’ll need to re-subscribe to the new discussion group over on Groups.io. We won’t be migrating your personal data to the new platform.

We are moving because Yahoo Groups is being deprecated and Groups.io has a bunch of great features to make this community more engaging. To subscribe, go to https://groups.io/g/DataManagement. You’ll need to verify your e-mail address, so don’t forget that step.

You have options for how much information you’d like to share on your profile and in the member directory. I recommend that you update your profile to include a photo or avatar, plus that you provide your professional name, since this is a professional group.

Everyone will have their first three posts moderated.

After a long hiatus, I’m bringing office hours back on a permanent basis.  I did this for years, then things happened and I stopped.  Now it’s time, for many reasons, to bring it back.

Office Hours is a virtual meeting to discuss data and data-related topics. They scheduled for one hour a week but you can participate as often as you want to.  Drop in for 15 minutes or stay for the full hour.

Registration

You must register the first time to attend. This will generate a repeating meeting on your calendar with the meeting information.

We are currently meeting every Thursday at 5:00PM EDT / 21:00 UTC

Register via the link above to receive the meeting invite with the meeting details.