Category: Uncategorized

I left TechEd this morning, actually too early, catching a 7:12am flight from New Orleans to Denver. Which meant a 5am alarm for me, not the most fun after being out with some fellow MVPs last night at the New Orleans Aquarium.

This was a short TechEd for me, combined with SQL Saturday #22 in Pensacola. I was gone 5 days from home, across a weekend, and that was a long trip. So even though I had a great time and enjoyed seeing friends and learning a few things, I was ready to come home. I missed a few sessions that I wanted to see today and tomorrow, but I’ll try to catch recordings online.

TechEd is a crazy conference, a zoo as I noted on Tuesday, with 10,000+ people gathered in one conference center. It’s a big Microsoft conference, covering all sorts of products and technologies. It’s always seemed to me that most of the focus is non-SQL products, with lots of Windows, Exchange, and infrastructure technologies. There are some developer sessions, and definitely some SQL ones, that’s the minority. Even with the Business Intelligence conference co-located with TechEd this year, it seemed that this wasn’t a big SQL conference.

That’s OK, and I had some good sessions on SQL that I saw. I spent one day going to a few regular SQL sessions, and then Tuesday in the BI area, checking out some speakers. The lessons learned from the Canadian Royal Mint was an excellent session and I’m trying to see if PASS can get that session at the Summit this year.

A few of my SQL friends go to TechEd to work on other technologies. Buck Woody (blog | Twitter) said he tends to avoid SQL sessions and go to lots of Windows ones on things like clustering, AD, areas that he does some work in. I had other people hit sessions on hacking, on Integrated Communications, and a few people check out Windows 7 Phone sessions. I think the latter went just to try and decide if they wanted to buy one later this year.

I don’t know if this is a great networking conference for people, unless you already know lots of people going. I did meet a few people, and met a few new Microsoft people, but for the most part I reconnected with people I already knew. Since I only see many of these people 2-3 times a year, it’s a good chance for me to say hi, and perhaps convince a few of them to write me an article or two.

I wasn’t thrilled with the New Orleans convention center, since it felt really long and narrow, requiring a lot of time to get to sessions, but the town was nice. It’s neat walking around downtown, people were friendly, and there’s a lot of history there. I barely scratched the surface, but I’m looking forward to going back in August for SQL Saturday #28 in Baton Rouge and spending a couple days in New Orleans.

Next year TechEd North America is in Atlanta, a city I’ve never spent time in and I hope I get to go again.

It’s the million dollar question. Literally.

I stopped by the SQL Server area at TechEd in the expo to talk to Val Fontana, a Microsoft’ie I’ve known for a few years to talk about the largest, and yet-to-be-released version of SQL Server 2008 R2 known as Parallel Data Warehouse. It’s actually above DataCenter edition, and depending on the configuration, can be up to around $2mm for the appliance, with roughly 90+TB of data.

Literally a million dollar question if you have a large data warehouse.

I went to a deep dive on this product as well, from Matt Peebles, formerly of DATAllegro, which was purchased by Microsoft. Supposedly all of the Linux/IngreSQL/other technology is completely ported to Windows now, and they are almost ready for a V1 release of this version.

A few people had MPP/Shared nothing servers in their environments, but not many. A few definitions to start.

A core concept is shared nothing. Resource and data independence are maintained in each DBMS instance. Google does this, breaking each part of the problem into smaller parts, each part handled on a separate node.

In this instance, each instance has certain shared resources (CPU, memory, disk) that is for only it’s use. Then you can scale out continually to add new resources with another box/instance.

MPP is separate: massively parallel processing. Multiple concurrent resources are used to solve SQL queries. Each instance works in parallel on its own distribution of the data. PDW supports up to 10 parallel instances per data rack (HP appliance, 8 with Dell), limited to 4 racks (40 instances). The reason is that they have only enough to test 40 nodes, but they will go larger. No architectural limitation for 40 nodes.

Comparing this with SMP, theres this slide:

 

A couple interesting points. SMP needs HA architected, but in MPP in PDW, this is built in. This should also scale out to > 1PB+.

Funny to see them listing this as relatively low cost. I guess at $1M+, relatively is the key word.

There also is a note of limited SQL Server functionality. I had heard this earlier, with no stored procedures, no text/image/binary data, no spatial, no XML, just text/numbers/queries, but those are the critical types. Partitioning is supported.

This is also somewhat based on the Fast Track program, where systems are basically configured for you to drop an application on. The workload matters more than the capacity, and it is hard for people to design systems. So the Fast Track and PDW systems are almost turnkey. If you need more SQL Server capabilities, they recommend Fast Track. They can also publish data for smaller marts to Fast Track architectures if you need that. I guess if you have a seven figure budget, what’s a few more DW servers?

Ultra Shared Nothing – Never heard of that, but this is an extension of shared nothing. What’s different?

• IO and CPU affinity within SMP nodes.
• Multiple physical instances of a table
• Distribution of large tables
• Smaller tables replicated
• Rows are redistributed on the fly when needed.

This means the system is balanced, with the right amount of drives and CPU for max throughput. So each node is silo’ed, having each node dedicated to chunks of data. So you max out each node with the best CPU/RAM/DISK possible.

This gives more predictable results on the system. As you add mode nodes, it scales fairly linearly.

This technology is converted from DATAllergro to the Microsoft platform. This locked down on W2K8 SP2, SQL Server 2008 SP1 cu5. This is a software + hardware solution, pre-tuned and optimized, with users seeing 1 server. Specific architecture appliances from HP, Dell, IBM, EMC, and Bull.

This is designed for DW workloads, sequential in nature, read only access. It does do loads, and they have a fast loader, but the workloads should be DW in nature. They support normalized and star schemas. It’s built for large loads, 10TB –> 500TB

It’s an R2 release since they integrate with the R2 products, PowerPivot, SSAS/SSRS/SSIS versions of SQL Server 2008 R2.

They are working with  many 3rd party BI solutions (Informatica, Microstrategy, etc)

The connection from client is using the Data Direct Drivers, not native SQL drivers. This is likely what will ship. They will ship ADO.NET, OLE-DB, and ODBC. Data Direct will provide JDBC drivers.

These drivers talk to the control node. That stores authorization info metadata, workspace for final aggregations, etc. This has the main PDW engine.

The control node talks to the underlying compute nodes. This is over a dual  2GB/s Infiniband bus, which is very fast. The compute nodes also keep a service called DMS, which redistributes data among nodes if needed.

There are also storage nodes, bascially SAN storage, but dedicated to each computer node. Should be connected over dual 4GB/s network, private for the storage.

There is a passive, spare compute node that is there for failover. So there is an N+1 failover. The control node also has a spare node with a Windows cluster.

There are a pair of management nodes that reads AD/DNS info and manages that. It also deals with setup/patching, and can leverage the HPC technologies from Windows for provisioning.

There is a landing zone node, an ETL interface for loading data. Is has an SSIS instance, a loader tool, and a file staging environment with a lot of disk. You can load from external SSIS directly if needed.

Backup node is there to do backups. Some test showing 5TB/hour. It does full and diff backups.

This system is delivered to you in multiple racks. All the control nodes (control, management, backup, ETL) are in a rack and then you have data racks with the compute and storage nodes. To expand, beyond the 8 or 10 compute nodes, you add in more data racks.

This is a major system, and not for most people, but for those that need it, it sounds very cool.

Initial reference architectures from HP are DL360 G6s, 8 hyperthreaded cores, 72GB RAM, 6 300GB disks. Or you can go Dell R610, 8 hyperthreaded cores, 96GB ram, 4 300GB disks. Those are some hefty nodes, and you get 8 or 10 of them!

Depending on storage, you can get up to 100TB in a rack, so potentially a 400TB capacity with 4 racks. You can choose performance (smaller, faster disks), or capacity (larger disks). These are basically going with 450GB or 1TB disks.

There are DMVs provided, and mostly the same as SQL Server, but they add on more information as needed. There will be a SCOM pack released after v1 comes out. There are web based admin tools as well. Customer monitoring tools can pull information from DMVs.

All components are redundant (disk, network, power, stored processors) and Windows Failover Clustering is used. Each rack is a separate cluster, and the management nodes use AD failover technology.

Under the Hood

Under the hood, you decide how to lay the data out. There are two classes of tables: big and small. Smaller tables are replicated on every compute node. For large tables, it gets partitioned out across the appliance. You choose the column to evenly lay out the data across all the nodes. The architecture is only as fast as the slowest node. The column you pick gets hashed and moves data across all nodes.

For each node, the data is actually broken up inside each node. There is some affinity that they get with Soft NUMA and Resource Governer to balance nodes. They actually have separate ports for each core in the processor. In terms of the 8 cores, they separate the data into 8 physical tables on the node, each one aligned with a core.

The data is in a R1 pair, with 8 LUNs corresponding to the cores. This is different than the Fast Track config where this separation is managed by the DBA. It’s done automatically here.  They also ensure that replicated are striped here. tempdb is on dedicated LUNS, t-logs are on separate LUNs as well.

The query processing flow was shown next. As you might expect, it works like this:

• Connect with SQL auth. No Windows auth supported right now.
• The query is parsed.
• Validate the user has rights
• Build an MPP plan next. This is the big deal. They have to build a plan that optimally reads sequentially from the disks with minimal data movement.
• The plan is executed
• data returned (either to control node or not), and streamed to client.

There were some interesting questions, and lots of places where Matt mentioned they would do something in v2 to fix, extend, or optimize things. That makes me nervous, but I had heard great things about DAT Allegro before.

This is a good session to examine if you want to see how things work in detail. Some food notes on the flow that hopefully make it to the recording.

Loads also take place in parallel. might be moved twice, but each compute node can process, convert, hash, and then shoot across Infiniband to the correct node.

What are technology preview customers saying? For one that does credit card processing, they are seeing better than linear performance under concurrency, with virtually no degradation for concurrent loads and queries as they are added together.

There is a DW comparison with a 24 core, SMP server. The PDW is 18x faster on average, with a range of 2x-166x. Multi-hour queries ran in seconds. Note that these queries were run with 1 clustered index only on the tables. No NCIs.

Very cool technology. Can you use it? That’s literally the million dollar question.

The Royal Canadian Mint, in Ottawa, is located at a large castle. How cool is that? Would you want to work there?

They are a $2B corporation, but about $700mm without gold trading/processing, with about 950 employees.

Not the Canadian government, they’re really a mid level manufacturing company. They have 4 different business lines, but are dependent on the economy in Canada.

Has to meet market demand for general coins, they don’t have a choice. For collector items, it’s the reverse. They don’t want to meet demand.  They have foreign business, and compete with other mints. That is an engineered, manufactured to order business.

They have a bullion market, trying to meet demand with extreme fluctuations of demand. A very time sensitive business.

With all this business, they need to make relatively real time decisions to maximize shareholder value.

They need to determine what is needed, and what is important, and how to tell. They also had to make sure these are tools that people will use, so it’s important that things match the people.

A demo of a live connection to their server in Canada. Limited showing of some Performance Point stuff due to business sensitvitiy. Early adopter, going from bare metal to production in 90 days. Technology is the easy part.

People, culture, clean data, those are the hard parts.

One initiative is green, reducing paper usage. So they showed a KPI they built on some measurements of usage of paper. Pages/employee over time. The idea is making this more personal to employees and get them to reduce printing, which uses less power, money, etc.

Another demo on exchange rates. They are a worldwide business, dealing with many countries. So they show daily exchange rates, putting them in the warehouse for use by various reports.

Since 2000, they have worked to make a profit and since 2003, they have turned a profit every month. Every month.

That is impressive.

This tool has been used in meetings, instead of whiteboards, PPTs, etc. This was the way to actually look at metrics, values, and data in real time. Microsoft notes this is a way to stop the static view of the world. The CIO of the mint noted someone came to a meeting with

a PPT and the CEO told him to leave. The value of the dynamic nature of data was very apparent.

Why measure?

Measurement is neutral. It’s just a way of capturing data. But measurement can drive behavior. Knowing the value of something changes the way we do things.

Reasons to measure: compliance, planning, justification, improvement, reward/recognize. These are the types of things you gain from measurement, but think about these when deciding what to measure.

You want to measure what matters. You have the ability to measure almost many things, but most things are useless in your environment. This is a challenge to determine those things that do add value.

Value is a highly subjective term, so you’ll need to work on this.

Not everything is equal, meaning that not each measurement is as valuable as others, and some measures only apply to some parts of your business.

They do look at continuous improvement (from Toyota, kaizen), and defining value from the customer’s perspective. Get lean, and getting more horizontal. Start with the customers and then work backwards.

When you have 300 things that matter, nothing matters. You have to determine what is important, and use those things, Too much information, too many KPIs, it’s a problem. Expect to evolve, and change your metrics quickly as you learn what is important and what matters.

If you want to define what matters, you had better really understand your customers. You had better know what is important to them and how they see your business. At the turn of the 20th century (early 1900s) people delivered ice to customers. If you asked them what they did, they’d say they delivered ice.

Soon after that point, the refridgerator was invented. And none of those delivery guys transitioned to refridgeration. Customers didn’t care about ice, they cared about cold beer, and unspoiled meat.

Seven Deadly Sins of Performance Management

The mint used to be in silos, committing provincialism. Narcissism, measuring what matters to the business, not the customer. A good concept to understand.

Don’t forget the people

Context is our perception of measurement. The same measurement can be positive or negative, depending on the context. The context is the most important part of the system. The tools are important, but the people are critical.

How do you know when the context is positive? When the people start to manage themselves, when they start to use the measurements to change behavior, or improve the way they do things.

You do have to beware of tight linkages between rewards and recognition from measurements. You want to target improvements.

If you want people to share their red lights, you can’t shoot them when the red light goes off. You want a no-fault environment.

Mistakes happen. Don’t celebrate them, but don’t punish. Learn from them. Share them, and make things better.

There is an Enterprise Performance group, on par with other IT groups reporting to the CIO, that has a BI group, a collaboration group, and a workflow group. They are almost halfway between the business and IT groups. They use VS, run Sharepoint, SSAS, etc.,, but the people have to be strong with interpersonal skills to work with others.

If you can’t bet the company on it, don’t publish the number. You have to be sure of the data.

Some key strategies to drive BI forward.

– Need strong and vocal champions, usually at the executive level.

– Every number has a name and a goal on it.

– CFO and COO are primary users.

– Balance out measures, sometimes with conflicting views.

– They went to a broad deployment, using lower cost tools from MS to make sure very desktop can get to some level of BI.

– 100 days of amnesty – don’t overreact to bad numbers. Watch them, investigate, ensure there is an issue and fix it. Don’t shoot someone.

– Scorecards are presented to BoD and executive meetings.

– IT doesn’t “do” BI. The build tools, train users, steward (or act as Custodians) for data, and encourages self-service. IT is more of a mentor.

– Don’t wait for perfection.

– Be willing to revisit solutions

It’s an interesting set of lessons, and a good speaker from the Canadian Mint. If you can watch this session, it might help you get BI off the ground, or decide if it’s something you want to get into.

In the customer demo, a few reports that were built with PowerPivot and Report Builder 3.0, which are helpful to business people in their hospital. The mapping control in RB3 is nice, and the customer said they built the report in minutes.

There are tremendous features and capabilities in Reporting Services for SQL, and you can have end users handle a lot of their needs. However you will need to provide them guidance so that they aren’t just playing with reports. I can see data pros learning to teach people how to use reports, helping them schedule data refrehses, and find data, as important skills.

The Alpha Geek Challenge, with Donald Farmer. Always a highlight of keynotes for me to see Donald. He ran this challenge, and they collected cool Powerpivot workbooks from various geeks.

The most interesting analysis was by Dan Comingore, which used AdventureWorks for an employee moral.

Dan English won the most interesting data set, with the flight information analyzed from attendees of the last BI conference.

The third winner for Brian Fosse, for most interesting visualization, showing a circular graph visualization, the radar chart.

The winners show some very cool capabilities of Excel, but I wouldn’t show them to end users as they might spend too much time actually reformatting their data, much like some people do with Word.

The overall winner was Brian Fosse, who is actually a business person. That’s the point of these technologies according to Donald and Ted.

Looking to the Future

No commitments here, but these are ideas on their mind. Real or vaporware to gauge response? Who knows.

The cloud is something they’re thinking about, and a good analogy to what is being done here. End users are using data and building applications, and someone else manages the data. That’s what happens in cloud computing. Not a bad analogy, but is it something that we want? I wonder.

The idea is to provide all capabilities of SQL Server in SQL Azure, including reporting and analytics capabilities. Makes sense, and no timeline, but I suspect we’ll see something over the next year here.

The consumization of IT. Things that used to be only for the public, search, social media features, etc. are making their way into IT. At least in MS products. I wonder if these are good or bad for business as some of these can end up being their own time sinks.

Compliance is a big deal. In terms of BI, this means (to some extent) that data quality needs to be high. But what is the correct data? At least for reference data, MDM is designed to fill this need. It’s not a bad solution, and I saw an interesting session on that yesterday.

Do BI people think of lineage? I haven’t, but it can be something that’s important.

Dependencies are also an issue, especially as we start to share reports, and build on other reports. I can see this being very important, and potentially a problem in Sharepoint 2010.

Data volumes are growing, both in size and in variety. More sources, more types, more data in absolute terms. Parallel Data Warehouse will come out this year, to allow 100TB+ for warehouses. CTP2 complete in that area, so they are working to get that finished as a product, and with reference configurations from vendors.

Project Dallas, a data marketplace, looks cool. You can get public and commercial data sets from here. That’s worth checking out, and using where you can.

“Stuff in code”, “hot off the developer machine”, glimpses of what’s coming. Amir Netz showing a few things, and he’s also one of the better speakers I’ve enjoyed over the years.

Amir shows an application authored by a VP at MS, looking at accounts and sales. It has a waterfall chart for examining how the various units, managers, salespeople are doing. Names and $$ changed, but not bad. However the VP wanted something else, so the BI people went to redesign it.

They changed the report to add the account person’s image, change the color of the background to imply the account status. Interesting idea, and then it’s extended to show everyone’s image. It uses a query, which is something that I think data pros will be writing.

It’s a fun way to examine data, but the analyst seems to still require the person using the data to play with various drill in/out of data. It would be nice to quickly, and easily do some comparisons, like the developers can do as they check in/check out code. Compare two versions of the code. It would be great to be able to compare to reports, easily, especially at two levels.

This technology, with the tile maps, should be available later this month, or next month.

Powerpivot shipped without KPI features, but since it’s built on Analysis Services, they are there. So they have bene working on adding these, and they will be available soon, without requiring MDX skills.

Amir also showed off a record view for a complex, wide table. Instead of seeing a long row, or a partial row, you see more of a report view with a single row moved into a multi-row record that fits on one screen, with labels for values. I can see this as being useful in some ways.

There are some new capabilities to actually edit and program Powerpivot sheets in BIDS. There are some more developer oriented extensions, perhaps making it easier to build complex calculations or reports for end users.

100 million rows aren’t enough sometimes. So in BIDS, Amir shows us a connection to a real SSAS instance. We see 2 billion rows of data being manipulated in Powerpivot (in BIDS). The sorting seems to work just as fast, same for filtering. Amir said that this “is beyond wicked fast. It’s the engine of the devil”

A larger data set, showing refreshes, and an extrapolated scan rate of 2 trillion rows/sec.

That is pretty amazing, and I’m sure it is great for most companies, many of whom have much smaller data sets.