Voice of the DBA

Category: Uncategorized

  • TechEd 2010 – BI Keynote – Part 1

    The second day’s keynote is by Ted Kummert and focuses on BI.

    This keynote is in one of the smaller auditoriums, which makes sense as only a portion of the attendees are interested in BI. However there are a fair number of BI people in the conference that have a regular pass and aren’t allowed in. Apparently there’s an overflow room, but I found a number of people waiting in line for 5-10 minutes to get in, only to be turned away at the door because they didn’t have the BI, yellow bordered badge.

    Ted runs the business platform division, which includes SQL Server, and the application server technologies.

    Every day businesses are asking questions about their business. That’s true, they are always looking for more insight, more data to support or debunk a position. The dream of BI, making business more efficient, able to more forward, allowing people to make better decisions, that’s something we all want. I know that people at Microsoft want to make that owrk better, and they have, but there is also a need for us in IT to learn enough to effectively apply these technologies.

    The keynote looks back at the last BI conference, before all the BI enhancements were added and released in SQL Server 2008 R2. No committments to future released, but a look forward is coming.

    A slide showing that 20% of the end users have BI tools to use, and 80% do not have tools. It’s a similar story, and MS is looking to try and get more end user BI, self-service BI, to that 80%. BI for Everyone, for 100%, is the vision for MS.

    A nice admission that BI is too hard Too much terminology and technology to learn. I agree with that. BI is hard. So that the idea is to make BI more familiar, using familiar tools. That’s PowerPivot, and I agree with that move. I’ve written about it, and if you haven’t seen it, check it out.

    Collaboration is important, and that includes sharing BI reports, documents, etc. This is primarily with Sharepoint integration from MS.

    There is a note that BI for Everyone means that we have to have managed data. Not data in the wild, in Access, Excel, but in environments managed by IT. So it’s not a look to get fewer DBAs, but rather have DBAs become more managers of data, but allowing end users to access this data with new tools, with less IT involvement in the end user consumption.

    Column oriented, in memory store. That’s what PowerPivot is, and it’s one of the few times that I’ve heard someone actually note that.

    Moving the Excel sheets with Powerpivot into Sharepoint means that IT can manage it. That’s a good thing, so you might want to consider adding some Sharepoint skills. This allows the IT group to back up, and secure, the data that end users are actually compiling, and working with.

    Bi for everyone, sold as: Office 2010, Sharepoint 2010, SQL Server 2010. A great way for MS to sell more licenses, multiple products. Good business strategy, and it means that you’ll need to upgrade Office to get end users working. Tell system admins to get prepped for that, skill and budget-wise.

    Michael Tejedor doing a demo. Sharepoint 2010, searching, and then finding reports stored in Excel sheets. Looking at the data sources, SQL Azure is listed along with many that you expect to see. There are also sources for getting data from SSRS Reports, which is often where someone finds some data they want to scrape out.

    There is an extension to the Excel expression language for Powerpivot, DAX. There are also “social” additions to the collaborative items in Sharepoint. It’s a good marketing move, allowing people to do things like “Tag” or “comment” on a document at a high level, as opposed to a particular cell. you can even “rate” documents, which is something that I’d wonder if people used.

    There are more settings, in addition to things like permissions. You can set data refresh intervals, and workflows.  There are compliance features built in as well, allowing companies to better manage data and ensure it is not being released in violation of regulartory requirements. This is a good check outside of security

    There are good manageability features as well for IT, allowing you to see aggregate activity for the system as well as for individual reports, see the usage of queries, documents, etc. With the farms being built, this should allow you to capacity plan better than in the past.

    You can also see from where data is coming, which can allow you to find out if the sources of data are being overloaded, and perhaps allow you to denormalize, or partition out data sets that are important to end users.

    A customer story from CareGroup Healthcare. It’s a guy that was featured a bit last year at the BI conference at the PASS Summit. I had lunch with him, and he does like Microsoft technology. However that might be because he’s a featured customer, and perhaps he gets some benefits. He’s showing off some Powerpivot reports that I think he showed last year, which allow his end users to build reports that they need without coming to IT.

  • The BI Conference at TechEd

    It’s a bad idea. Co-locating the BI conference at TechEd has created a lot of confusion and annoyance. If people aren’t allowed to flow between the conferences, why have them together?

    Brad McGehee ran into this yesterday when he went to attend a session and was turned away because he didn’t have a BI badge. That’s OK if it’s publicized, but I wasn’t aware that was an issue. Apparently quite a few people weren’t sure of this.

    Today I was waiting for the BI keynote to open, annoying in itself. They kept the doors closed until way too close to the keynote, IMHO, and people were piling up by the doors. Then when a few people at the front tried to get in, they were turned away because they had regular TechEd badges. I was held up since they initially had press people “not on the list”, but after a few minutes, I was allowed in.

    There’s some confusion as I saw a couple regular people in the auditorium that had come in the other door and hadn’t been stopped. Applying rules differently is a hassle for attendees and it goes a long way towards ruining the conference experience.

    It would be easy to just put a small note in the schedules that say “BI only” so that all the people that registered for TechEd itself wouldn’t walk to a session only to be turned away.

    The wifi is working in the auditorium, which is a relief. It was very spotty yesterday, and even died for a couple hours. Today there are less people in the keynote and hopefully it will hold.

  • T-SQL Tuesday #7 – StreamInsight

    TSQL2sDay150x150

    The 7th T-SQL Tuesday is upon us and you can read about it on SQL Chicken’s blog. He’s hosting this month, with a few rule changes (like the picture above) and his call is for posts on new features in SQL Server 2008 or SQL Server 2008 R2.

    T-SQL Tuesday is a blog party where we encourage a large number of people to write on a single theme or topic. It’s fun, and I encourage all of you to participate.

    StreamInsight

    A new feature introduced in SQL Server 2008 R2 is StreamInsight. It’s a complex event processing technology that I think is one of the more amazing things to be added to SQL Server in quite some time. Of course I thought the Service Broker was cool in SQL Server 2005, so that shows what I know.

    This is based on the work at Streambase, and the work of Michael Stonebreaker. It involves building a query engine, processing that can handle large volumes of data, things that come in streams: price quotes, sensor data, auditing data, any number of continuous data elements.

    We commissioned a Stairway Series on this topic, since I think it’s a game changer. The introductory article, What is StreamInsight and why does it matter?, does a great job showing what the technology is and what it can do. It’s a way to filter, process and gather data, find patterns, etc. from a large volume of data, and then get it into another database.

    Why does that matter to us? I think this image presents a great view of why:

    5116[1]

    As much as I have said BI isn’t the next big thing because it’s too complex, I think there’s great value in BI systems that can distill data down to a point where we can work with it. I think StreamInsight can help with this, especially as we get larger and larger volumes of data, more than our SQL Server instances can handle.

    I believe the ability to gather information from a variety of streams, in a more timely manner than you might get with something like Integration Services (SSIS), will greatly aid businesses in analyzing the information that can help them.

    This is a technology that I can see lots of uses for. Real-time auditing data, going through a stream of information about activity on a server or application and flagging patterns. Learning what is normal, and then raising flags when there’s a deviation from that.

    It could be great for monitoring a large volume of data from some source like Twitter, searching for references to your company or product, categorizing them, counting retweets, analyzing those that never get a response, or might need a response. The same thing could be done with a stream of blog entries from a search engine like Google or Bing.

    I think the possibilities could be very cool. I look forward to seeing some innovative uses of this new feature.

    StreamInsight is available in SQL Server 2008 R2, in two editions. There’s a Standard edition that works with Standard, Enterprise, and Web editions. There’s also a  Premium Edition that is available for Enterprise, Developer, and Evaluation editions. The difference is in the rate of processing that occurs.

    If you exceed these levels of stream data, you need the Premium edition:

    • More than 5000 events a second
    • You need to process them inside of 5 seconds (low latency)

    These are the basic differences in the editions, and if you can tolerate a delay of more than five seconds (most companies/applications) and you don’t have an extremely high rate of data (5000 events/sec), you can use this technology in Standard Edition.

  • StreamInsight

    This is one of my favorite technologies for SQL Server 2008 R2. This session was from Torsten Grabs, the program manager.

    It starts looking at the value  of timely analysis. Being able to view, and use data in a timely manner. A trend line of analysis with time. Data that is months and years old is for historical analysis. Days and hours is primarily used in forecasting. The data that is minutes and seconds old, can be very valuable. This is stuff from web analytics, etc. More and more verticals are seeing value in real time information, and they want to query it. Health case, financial services, utilities, etc.

    Many companies have built home grown solutions for this, but they are expensive and require lots of development resources as the number of events grows.

    MS decided to build this into SQL Server since it was a missing part of their stack. Where does this fit into SQL Server?

    Operational Intelligence as a platform comes from multiple sources of data being cached, processed, distributed, and then visualized in multiple ways.

    From the slide, you want data from the left to move to the right as quickly as possible for the top, push items. The pull items, reports, mining, etc, you also want to be able to refresh each other quickly.

    How do event driven applications differ from standard database applications? There is a good set of contrasts. The query paradigm is from adhoc queries. For event driven apps, there are continuous standing queries.

    For latency, a standard db looks for days, minutes, seconds. The event driven app wants ms or less.

    For the data rate, standard db wants hundreds of events per sec. For event driven, there are tens of thousands per second.

    For the query semantics, both use relational queries, but event driven items need temporal queries as well.

    The target applications are for operational analytics apps, web analytics, manufacturing apps.

    For an overview, there are development with VS 2010 using .NET, C#, LINQ.

    The application at runtime takes input adapters, perhaps some static reference data from SQL Server, and it sends the data through output adapters to consumers. These can be devices, or they can be event or data stores.

    Inside, StreamInsight takes your LINQ queries and compiles them to be used against the streams of data. They become triggers against events.

    Out of the box, this is an in memory query processing engine. You do not need to persist any data to work with it in StreamInsight. Persisting data is expensive, and introduces latency. You have the choice of where, and if, you want to deal with this latency.

    StreamInsight query is very composable, so you can take out smaller portions of the queries and reuse them, or build on them for other queries.

    This is needed because data acquisition costs are falling, and with low storage requirements of StreamInsight, you can now process data while it is in flight and then avoid loading huge amounts of data.

    Utility Scenario: households can be instrumented with smart grid power meter. This provides continuous insight into usage, and/or demand. StreamInsight scales to millions of meters, with 100,000s meter readings per second. You can then write validation, editing, estimation rules in LINQ, that might allow them to see a constant view of their grid, and perhaps help prevent power outages.

    Financial Services scenario: You can get continuous low latency market waching, and managing risk better. You can actually detect and notify users in real time of actual risk. You can also take the same models or LINQ queries and run them against historical data.

    Web Analytics: StreamInsight allows continuous analysis of online behavior. This can identify relevant content before the next click, and perhaps define that new content based on the click behavior. It also allows analysis of web logs online, in near real time instead of offline in a batch.

    Event Types – basic structure of what you are writing queries against. Structure, using the .NET type “system” and have a series of fields. The SI engine provides timestamp fields to capture all temporal event characteristics. All calculations are based on business time.

    Event streams can be continuous, or  bursty. They can go on indefinitely, and potentially arrive out of order.

    There are a number of operators that provide all sorts of things you might expect. JOINs, existence, filtering, grouping, APPLY operations, sum, count, ranking, and more. There are a number of temporal operations. Four are provided out of the box: hopping window, sliding window, tumbling window, and a count window.

    The demo shows some real time data management. In this case, it is a financial application, examining MSFT and IBM as stock tickets. Incoming data is partitioned with a temporal function by stock, and data is pushed to some applications, and the stored in SQL Server as well. We saw the stream start coming into an application, which could then display the data as a stream.

    By enabling various output adapters, we could see graphs updating in real time, some data being stored in SQL Server, and even Excel pulling refreshed data for someone. It’s very cool.

    There is a Linqpad like application that can connect to a stream and allow you to write some LNQ queries and see results from the stream.

    We also saw the debugger. You can see what is happening in your application, and even record a stream. If you pick an output row, you can do a root cause analysis, and see at each stop of the query processing, what rows contributed to that output row. That would be amazing for T-SQL as well. If you could see a complex query and find out which rows “contributed” to that final result.

    This is an extensible framework, allowing you to build your own operators or extensions into the StreamInsight applications. These can be temporal or non-temporal operators.

    There are different deployment scenarios. You can process close to the data source, or you can deploy further downstream and capture events from multiple types of sources.

    StreamInsight is being deployed as an embedded engine, a few DLLs inside your application. This gives a low footprint and very little overhead. It can run as a Windows Service as well.

    The packaging is something I’ve noted for tomorrow’s blog, and the latency and event size varies according to the edition.

    There are a number of partners that are working with MS on solutions. Check the StreamInsight site for more information. Lab 49, OSISoft, Matrikon, and more can help you.

    This stuff is cool. Mostly high end, but I can see lots of smaller sites thinking of using this for more real time web analytic analysis.