Voice of the DBA

Tag: Reporting Services

  • The Pyramid of Data

    Data is an important part of our world, and arguably the most important asset in computing. All the rest of the devices, platforms, and technologies we use are designed to work with data, by manipulating, storing, accessing, and presenting data in new ways. We need devices and operating systems to host software, and applications to work with data, but the data is the key to fuel for every engine.

    I see there being a pyramid of data, with various technologies that are used to store and work with data. This is roughly how I see things, with various sources that store data as the foundation, and then systems to summarize and aggregate data, a new layer of analytics with Data Science, and the ever present ways of interacting with the data in order to use it for insights and decisions.

    (SSRS) (Visualizations) (Excel) (Power BI)
    (Data Science) (Artificial Intelligence) (Machine Learning)
    (Data Warehouses) (Data Marts) (ETL) (Data Streams) (Linked Data Sources)
    (SQL Server) (Oracle) (CosmosDB) (ElasticSearch) (Redis) (HDFS) (MongoDB) (PostgreSQL)

    That’s been my traditional view of the data pyramid, but cloud computing, the orchestration of containers, and better ways of analyzing data without moving it lead me to think that this is becoming more of a mesh inside the pyramid that multiplexes connections between layers. While I do think AI and ML systems will become more and more useful to a wider variety of applications and organizations, I do think the adoption will move more slowly than the hype suggests. Likewise, I think containers will grow slowly as there is a need to rearchitect many applications.

    Certainly cloud computing is becoming more and more commonplace. I especially am starting to see more smaller organizations taking advantage of cloud platforms that build SaaS, not for large scales, but for very small scale organizations. The platforms themselves are constantly lowering the cost of engaging with the cloud at small scales, and making it more feasible for application developers to deliver tremendous value and capabilities to very small organizations that aren’t, and don’t want to be, software companies. They just need services and capabilities without a lot of effort. The Power platform from Microsoft is likely to accelerate this with easy development for any semi-skilled software developer.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Data Science, BI, and Reports

    Data science, along with the Artificial Intelligence (AI) and Machine Learning (ML) fields, is often seen as the new direction in which we ought to move our analysis of all the bits and bytes that we collect and store in our databases. There is so much hype now about those technologies, and managers are buying in.

    I’m not sure I agree. I do think that AI and ML will increasingly be used, but they’re just a part of what you use to analyze data. Buck Woody has a good post about the way in which we might examine our technology stacks used for BI work.

    We have a lot of reporting technologies to enable us to make better decisions, and there is a space for all of them. Many people like Excel, some use tools like Power BI and Tableau, still others prefer to get insight boiled down to a single number that influences them to move one way or the other.

    There is a lot being written about AI and ML technologies and certainly many organizations experimenting with them. Data Ccience covers these areas and more, asking our data not just what it says, but potentially what this might mean in the future.

    However, this doesn’t replace traditional BI and reporting. As Buck notes, these are tools and you should use the ones that work for your organization. Learn about them, experiment, understand the impact they have on your audience, and choose the best tools for the job.

    I’m sure this area will continue to evolve, and we’ll get new tools and techniques to help organizations make better decisions. Whether this will actually improve forecasting is likely up to the skills of both the technical and business people.

    Steve Jones

  • Large Chunks of Data

    This editorial was originally published on Sept 17, 2010. It is being re-run as Steve is on vacation.

    I saw a post recently where someone talked about trying to get better performance from a report. They were selecting 5mm rows from a table and wanted to see if there was a more efficient way to chunk out this data so that the instance would not report memory errors.

    My first question is what kind of report has 5mm rows of data? That’s just too much data.

    How long can a report be?

    From your experience, talking with people, looking at what they analyze, how big is a report? How much data can you really display on a report and make it useful for users?

    I’m thinking here in terms of the raw data you show. A pivot table can summarize millions of records, but realistically I thin kit becomes hard to examine more than a few hundred data points on a page. Whether they are raw data or aggregates of other data, it seems there’s some limit to what a report should provide.

    After all, that’s why we have drill-down 🙂

    Let us know this Friday what you think; what you have observed? Maybe we’ll  help others to build better reports that are more practical and useful to end users, as well as easier to develop.

     

  • Reporting on Data

    I would think that most DBAs and SQL developers out there realize they need to understand some basic statistics and mathematics to write meaningful reports for their customers. Since many of us write our reports in T-SQL, if we correctly write the formula for one row, it expands to work the same way for all rows returned from the database. That should hold true in Excel, if you cut and paste the formulas across the all the rows or columns. However since each cell could have a different formula, you can’t always be sure that the calculations for all rows are the same.

    This was brought to light in a blog that looked at the problems with a public policy analysis based on an Excel worksheet. The audit-ability problem of validating results in Excel was one issue, and it comes about because you would have to manually verify the formulas in every cell to be sure they were correct. I’m not sure how many people want to do that, though I like the transparency of providing the formulas with the data. Someone will verify them.

    The other part of the blog talks briefly about other issues. One is that to accurately represent the meaning of data, we need to do more than show simple aggregates like range or average. The reduction of a set of data to a single or small group of representative values may not describe the information in the data. An average without knowing the variance or standard deviation may not be the data you want to base a business decision on.

    As we seek to provide more accurate information to our customers and clients, we need to better understand the data we are querying. At some point it won’t be enough to understand the basic aggregates in order to report on data. Big data can provide many false patterns, but tiny data suffers from a similar problem. We need to learn more about statistics and analysis to ensure that as we work with business people to query data, we understand what information we are extracting out of data sets.

     

    The Voice of the DBA Podcasts

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