Voice of the DBA

Tag: data warehousing

  • The New Data Warehouse Choice

    I was listening to the SQL Data Partners podcast the other day with BI expert, Tim Mitchell, and the opening question was “Is the on-premises Data Warehouse dead?” Tim is a friend, so I tuned in knowing he has some good thoughts on the topic. It’s an interesting listen, and one you might enjoy if you’re at all interested in data warehousing and related topics. Spoiler alert, Tim says no, on-premise isn’t dead, but he does point out some interesting things about the Azure SQL Data Warehouse (ASDW) and similar offerings.

    One of the more interesting comments Tim made was about a health care company he worked for. They had an end of month process that heavily taxed their systems. If they didn’t need that peak level of processing, Tim noted that the cost of their large data warehouse architecture would be that halved. That need to scale up dramatically can be a big savings in moving to a cloud based system, where you can pay for a much lower level of performance most of the time and increase your scale at particular times.

    I know this is feasible as I worked with a similar situation. My employer purchased a very large system for our end of month and end of quarter closing load. Fortunately, we had an AIX machine that contained its own hypervisor. At the time (2001), we purchased a 32 processor server, with the idea that only 18 CPUs (and a slice of RAM) were running our financial systems most of the month. We had QA, development, and other guests on the same hardware. During the month closing, we would shut down some VMs and dedicate most of the processors to the finance system for a few days to handle the load. What’s more, the IBM machine actually contained additional CPUs that we could “rent” from IBM for a few hours if we really needed them.

    That’s what data warehouses in the cloud can do for you. Certainly the decision to move to a cloud architecture is more complex than just having the scale up power of ASDW or Amazon’s Redshift. The ability to load into the system, the development challenges, the tax implications, and more will impact the decision. I think the workload characteristics are also important. If you don’t have a highly variable, or large peak, workload, then the cloud might make less sense. If you don’t have any sort of data center, then maybe the cloud makes more sense.

    I do think, however, that the decision to implement a new data warehouse isn’t a simple one, and the cloud is a viable choice. The platforms are becoming more capable all the time, with more tools and scale options, as well as better performance guarantees. Many of the tools used to analyze data in a warehouse are more important than the underlying platform, with Excel, Tableau, Power BI, and more easily connecting to any data warehouse platform, in the cloud or on-premise.

    This means that we will end up managing more disparate systems over time, especially in larger organizations where some groups will adopt cloud systems while others stick with on-premise installations. Certainly if you are a person that works with a data warehouse, you might want to build a small POC on Azure SQL Data Warehouse and see what you think about its capabilities. At least then you’ll be able to add some educated and intelligent thoughts to the discussion when the question comes up inside your organization.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.3MB) podcast or subscribe to the feed at iTunes and Mevio .

  • Data Decisions or Instinct?

    Most of us that are data professionals think the best way to make decisions is to use data to justify some course of action. We look for patterns in data, some guidance that the information we have will lead us to make the best choice for our organization. Google has talked about making data driven decisions as a part of their success and they think more organizations should do this. Any number of other companies also use data to power their BI systems and dashboards that help their employees make better choices.

    That seems in contrast to this piece from the Harvard Business Review that says that great decisions don’t start with data. It talks about using stories and emotions, with a few key facts sprinkled in, to help sell ideas and get decisions made. On one hand I agree that stories help to sell decisions, but I often have found that successful salespeople use this technique to deceive and convince by plucking emotional heartstrings, and using relatively little data.

    In my mind, the best way to make decisions is to go with your instincts, but while examining and understanding the data. You can’t discard data, especially when it presents strong patterns. However data can be deceiving when we don’t carefully examine the ways in which it’s put together. An average doesn’t always reflect the actual value of a set of numbers, especially when we don’t also understand the range, standard deviation, and count of values.

    We also have to realize where we do and don’t have experience and expertise in some subject. We should certainly look to data to guide us and perhaps even justify our decisions, but we can’t forget that the human brain is still an important part of any computational exercise. We need employees that you use their judgement, in collaboration with data, to make the best decision for our organizations.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • A Data Hub

    Hub and spokes
    The idea of a data hub seems very interesting to me as a DBA.

    One great promise of data warehousing over the years has been a “single view of the truth” for companies. By moving, cleaning, transforming, and standardizing data from other systems, we can put together a single location for the most accurate data a company can have. I suspect there are some organizations that have had success here, but many are struggling. The idea behind some of the newer SSIS tasks, Master Data Services (MDS), and Data Quality Services (DQS) in SQL Server is that we have some functions in the SQL Server platform to make this easier to achieve. Or perhaps to ensure we do so at a high level of success.

    However just moving data to a central location isn’t necessarily the only way to deal with the challenges of data. Perhaps there’s a better way, a more distributed way that provides a framework for centralization, but distributes the ownership and knowledge of the data to others. Buck Woody recently wrote about data hubs as a project and idea that Microsoft is making available. It’s a place to publish data for your organization, but groups inside your company, but available for others to use.

    Many of us have experienced the issues of each developer or each department attempting to manage their own sources and lookup data. Even well known data such as postal codes can change and easily become stale quickly. How many developers are willing to write import routines to update this data for something such as postal codes, let alone internal data such as customer names.

    Ideally I think we should pull lots of our initial data from corporate sources, and establish central data hubs for all new types of information we gather and support. From there, a variety of import and update routines could be written and shared by all applications people use. It wouldn’t provide perfection in terms of data quality and freshness, but it would be better than allowing each individual developer to make their own decisions.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.