Search results matching tag 'Data Warehouse'

Data Warehouse Workshop

manowar — Sun, 25 Nov 2012 14:52:02 GMT

I’m really really pleased to announce that it’s possible to register to the Data Warehouse Workshop that I and Thomas Kejser developed togheter. Several months ago we decided to join forces in order to create a workshop that would contain not only the theoretical stuff, but also the experience we both have and all the best practices and lesson learned that can make the difference between a success and a failure when building a Data Warehouse.

The first sheduled date is 7 February in Kista (Sweden):

http://www.eventzilla.net/web/event?eventid=2138965081

and until 30th November there is the Super Early Bird to save more the 100€ (150$).

The workshop will be very similar to the one I delivered at PASS Summit summit, with some extra technical stuff since it’s one hour longer. In addition to that for this first version both me and Thomas will be present, so it’s a great change to make sure you super-charge your DW/BI project with insights that aren’t available anywhere else!

If you’re into the BI field and you live in Europe, don’t miss this opportunity!

Agile Data Warehousing with SQL Server 2012 Q&A

manowar — Thu, 30 Aug 2012 13:38:33 GMT

Yesterday has been published my Q&A interview on my Pre-Conference Workshop at SQL Pass 2012. It’s a good way to understand what will be discussed in the workshop, so if you’re interested or you’re into the Data Warehouse / Business Intelligence field and want to understand how the Agile approach can help you, you can read it here:

http://bit.ly/PASS2012DMInterview

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date.

I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal.

:{>

PASS Summit Preconference and Sessions

manowar — Fri, 22 Jun 2012 08:41:10 GMT

I’m very pleased to announce that I’ll be delivering a Pre-Conference at PASS Summit 2012. I’ll speak about Business Intelligence again (as I did in 2010) but this time I’ll focus only on Data Warehouse, since it’s big topic even alone. I’ll discuss not only what is a Data Warehouse, how it can be modeled and built, but also how it’s development can be approached using and Agile approach, bringing the experience I gathered in this field.

Building the Agile Data Warehouse with SQL Server 2012
http://www.sqlpass.org/summit/2012/Sessions/SessionDetails.aspx?sid=2821

I’m sure you’ll like it, especially if you’re starting to create a BI Solution and you’re wondering what is a Data Warehouse, if it is still useful nowadays that everyone talks about Self-Service BI and In-Memory databases, and what’s the correct path to follow in order to have a successful project up and running.

Beside this Preconference, I’ll also deliver a regular session, this time related to database administration, monitoring and tuning:

DMVs: Power in Your Hands
http://www.sqlpass.org/summit/2012/Sessions/SessionDetails.aspx?sid=3204

Here we’ll dive into the most useful DMVs, so that you’ll see how that can help in everyday management in order to discover, understand and optimze you SQL Server installation, from the server itself to the single query.

See you there!!!!!

Spotlight session at PASS 2011 - Temporal Snapshot Fact Table

manowar — Sun, 29 May 2011 13:44:59 GMT

I’m very happy to announce that my proposal for the Spotlight session I’ve been invited to deliver at PASS 2011 has been accepted!

Temporal Snapshot Fact Table

You are designing a BI Solution and your customer ask you to keep a snapshot of the status of all their documents (orders, insurances, contracts, bills...whatever the word "document" may mean) for all the days of the year. They have millions of documents and they want to have in their Data Warehouse all the data they have gathered right from the very first operating day.

If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse.

In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware.

The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before.

The topic is very interesting and – I bet – very intriguing for many people working in BI and specially with Analysis Services, since it lacks the support of “time intervals” to define the validity period of a fact row. With my SolidQ Italian collegues we’ve been able to find a way to overcome this limitation, allowing the storage of daily snapshots of data with a very high efficency and performance.

In this session I’m going to share everything we discovered with you. It will be really interesting, I can tell you! Probably one of the most advanced – yes simple - usage of SSAS and Many-To-Many relationship you’re going to see.

Presenting Part 4 of Building a Scalable, Enterprise-Class Data Warehouse for SQL Server 20 Oct 2010

andyleonard — Fri, 15 Oct 2010 11:00:00 GMT

I am honored to present Part 4 of 4 of Building a Scalable, Enterprise-Class Data Warehouse for SQL Server Wednesday, 20 Oct 2010!

This presentation features a demo of expressor 3.0 studio by Steve Frechette, expressor's Vice President of Engineering. It's going to be fun and I hope to see you there!

:{>

Richmond Code Camp X Recap

andyleonard — Sun, 10 Oct 2010 03:29:00 GMT

Introduction

The tenth Richmond Code Camp was held today at the J. Sargeant Reynolds Community College campus on Parham Road.

It. was. awesome!

With 40 sessions and over 400 registered, this #RichCC was arguably the best one yet! There's a lot that goes into planning an event of this magnitude. I commend the Code Camp Leadership Team for their hard work, but the team possesses a quality that will ensure many more Richmond Code Camps to come: absence of ego.

Ch-Ch-Ch-Changes...

There's a bad joke about a widow who visits the funeral home before services begin. She's astonished to find the suit she provided for her late husband on the cadaver in the next coffin. Aghast, she notifies the undertaker who assures her the matter will be addressed. As she steps out of the room she hears the undertaker yell into the back room "Lou! Swap the heads on two and three!"

Over the past five years, we've "swapped heads" a few times on the leadership team. I'm honestly not sure who led the first Richmond Code Camp, my turn came up around #2 or #3. After a few turns, it pased onto another, then another. Areas of responsibility also rotate. I remember Fran La Vigne (Blog | @Tableteer) and I sitting in a Panera Bread in Short Pump Virginia selecting speakers for that first event.

There's no penalty for stepping up or down on our team. Life happens, people move, get more and less busy, change jobs, and just have other plans. Why punish people for that? Especially in a volunteer organization? The on-point person doesn't try to "edge out" previous leaders. That's because we're all pretty secure individuals. It's a great group to be part of, and I love every member of our team.

Presentations

As usual I didn't get to watch an entire presentation (except my own). I caught a lot of Jessica Moss's (Blog | @JessicaMMoss) talk on Data Warehousing. As always when I hear Jessica present (or speak), I learned new stuff. Jessica is a great speaker and Richmond is fortunate to have her as part of our community.

I had to duck out to set up lunch (which was late for the first time ever in Richmond Code Camp history!). While waiting for the Pizza Dude to arrive, I had a great chat with "Robin's Mean Husband" (apologies for the inside joke in a public blog...).

I presented after lunch. No one fell asleep (yay!). I did my talk called Building Your First SSIS Package and, just like in the same presentation for SQL Saturday #46 in Raleigh NC a few weeks earlier, the keyboard stopped working in the middle of my demo. I finished using only the mouse - again - but it's definitely time to place a service call to Dell.

The coolest thing about community events is the cool people. I skipped the next session to talk with Joel Cochran (Blog | @JoelCochran) , Frank La Vigne (Blog | @Tableteer), Kevin Hazzard (Blog | @KevinHazzard), and G. Andrew Duthie (Blog | @DevHammer), TBDDEOTP (The Best Durn Developer Evangelist On The Planet). It was great to catch up with everyone and just geek out a little.

For the last session of the day, I snuck into Joel Cochran's talk on Blend. Joel loves building solutions with Blend and it shows. I was in there for 15 minutes and I picked up a ton of tricks. Joel is also my star SSIS pupil. We spent a Saturday in July working together on database design and SSIS mentoring. He has taken that ball and run with it!

Conclusion

For some reason, I thought it would be fun to drive to and from Boston to deliver From Zero To SSIS in cooperation with Boston SQL Training last week. The drive up was rainy bu ok. The drive back wasn't. It took me three hours to go the first 100 miles. No fun at all. Watching the Arrival Time inch forward on my Garmin all that time was no fun. I left Boston around 3:00 PM and arrived in Richmond about 12 hours later, grabbed four hours of sleep, and then rolled out for Code Camp!

Despite a rough start to the day for me personally, Richmond Code Camp X was a huge success!

:{>

Presenting Why Consider Semantic Integration? 29 Sep 2010!

andyleonard — Mon, 27 Sep 2010 11:00:00 GMT

In the third part of our series on building a scalable, enterprise-class data warehouse for SQL Server, expressor's (blog | @expressor) Mike Ruland (@mruland) and I present Why Consider Semantic Integration Thursday 29 Sep 2010 at 12:00 PM EDT.

Semantic rationalization is a natural progression from metadata management; utilizing metadata to abstract data integration mappings.

Sound interesting? Register here. Hope to see you there!

:{> Andy

Presenting the Importance of Good Metadata Management 8 Sep 2010!

andyleonard — Tue, 07 Sep 2010 16:00:00 GMT

In the second part of our series on building a scalable, enterprise-class data warehouse for SQL Server, expressor's (blog | @expressor) Mike Ruland (@mruland) and I present the importance of good metadata management tomorrow at 12:00 PM EDT.

Metadata is important and hard to manage. Mike and I define metadata and talk about the many places metadata is used in data warehousing.

Sound interesting? Register here. Hope to see you there!

:{> Andy

SQL Server Fast Track Data Warehouse

jchang — Tue, 24 Feb 2009 20:46:00 GMT

This came out on the Microsoft website. I have not had time to look over the hardware configuration carefully. Of course the recommended storage is a SAN, vendors love to sell very high margin products when there are perfectly good merely high margin alternatives. Well atleast this one looked at sequential disk IO bandwidth, unlike past configs that were totally silly. HP config for the DL385G5p does 1.5GB/s, the DL585 3GB/s and the DL785 6GB/s. The Dell configs for PE2950 does 1.6GB/s and the R900 4.8GB/s. Gut feeling is the 2-socket systems are underpowered storage wise, but that what happens when you config expensive storage solutions.

http://www.microsoft.com/sqlserver/2008/en/us/fasttrack.aspx

Ok, I withdraw my initial complaint on the MSA 2000, depending on which model. I love direct-attach storage in non-cluster environments for brute force capability. So I like on the HP side, the MSA60 with LFF drives or the MSA70 with SFF drives (because of the high drive count, 25, on a single x4 SAS, this is really for 8K random IO, not large block or sequential). The MSA 60 base unit is $2,999. The MSA 2012sa single controller base unit is $4,499. The MSA 60 requires a RAID controller in the server. The MSA 2012sa has a built-in RAID controller, requiring an SAS HBA on the server. So the MSA 2000 SAS variant is only slightly more expensive than the pure DA solution. The MSA2000 FC variants are far more expensive and (while suitable for cluster environments which this is not) do not contribute to performance.

Intel vs. AMD

The Dell reference systems are on Intel Core2 architecture processors and the HP systems are on AMD Shanghai. For many people, the choice of Intel vs. AMD is highly emotional. Some feel they are Luke Skywalker battling the evil empire. Others feel compelled to quash rebellion in the ranks. I am of the Han Solo thinking ("I ain't in it for your rebellion honey, I'm in it for the money"?). From the technical perspective, the Core2 has the more powerful processor core for CPU intensive operations. Opteron has the integrated memory controller for faster serialized round-trip memory operations. At the 4-socket level, the AMD system has 8 memory channels versus 4 for systems based on the Intel 7300 chipset. The AMD 4-way systems have more IO bandwidth at the 4-way Intel 7300 chipset. One reason HP may have referenced the AMD Opteron line is to have a uniform line from 2-way to 8-way. Unisys now has an ES7000 for the Core 2 architecture. I would like to see the detailed system architecture for that, as well as run some IO bandwidth calibration tests. The HP Integrity line has outstanding IO capability, but is saddled with Itanium 2 on 90nm (soon to be 65nm?).

SAN versus Direct-Attach

I strongly prefer DA over SAN for DW because DA can achieve very high bandwidth at a low cost. A pair of Dell PowerVault MD1000 can support 1.6GB/s for about $12K. I am inclined to think the EMC Clarion CX4-240 with 2 dual-port 4Gbit/s FC HBAs, and 2 DAE will cost in the range of $30K. Yes, the SAN has lots of features, useful for clustered and transactional environments, but not really essential for DW. However, if one were intent on using SAN for DW, I would agree with this approach of achieving very high bandwidth using multiple entry/mid-level SANs rather a single high-end SAN.

Data Consumption Calculations

The reference configurations seem to be built around the calculated table scan data consumption rate of 200MB/sec per core. It is commendable that configurations be built around calculations. Also, most people cannot handle complexity, so calculations must be kept as simple as possible. However, it is grossly irresponsible to make the statement that table scan consumption rate is linear with the number of cores. It is highly dubious that parallel execution plan consumption rate was tested at all given the cited SELECT * OPTION (MAXDOP 1) method. An all rows returned SELECT * query is very unlikely to generate a parallel execution plan. This has to do with the formulas used by the internal SQL Server cost based optimizer (or execution plan cost formulas), as IO costs are not reduced, only CPU. The extra cost of the “Parallelism (Gather Streams)” operation inhibits the parallel plan.

Also, SELECT * FROM Table is not a proper test of data consumption, it is as much as a test of the ability of the client to receive data.

Anyways, I had previously reported precise measurements of the cost structure of SQL Server table scans. A rough model is that the base table scan (SELECT COUNT(*) or equivalent) depends on the number of pages and rows involved, and whether disk access is required. The cost per page (referenced a 2-way system with the Intel Xeon 5430, Core 2 architecture at 2.66GHz) is around 1 CPU-microsecond for in-memory. The test tables columns were all fixed length not null.

The cost per row per page is roughly 0.05 CPU-microseconds. So the in-memory table scan for a table with 20 rows per page works out to approximately 2 CPU-microseconds. This applies for a unhinted SQL query, for which the default for a table this size should be table lock. The cost of a table scan using row lock is much higher, possibly 1000 cpu-cycles per row (I reported on this long ago, in SQL Server 2000 day on the Pentium III, 4 and Itanium architectures).

Let me emphasize that these are measure values, and are known to be consistent over a wide range of conditions. It does not matter what one imagines the code to execute the above looks like.

For large block disk access, the cost additional per page is approximately 4.3 CPU-micro-seconds. So the base table scan at 20 rows per page will run in the range of 4GB/sec in memory and 1.2GB/sec to disk on a single core. Note that I said for large block disk access. So I think this amortizes the disk IO cost over many pages (32-64?) and of course includes the cost of evicting a page from the buffer cache and entering the new page.

Notice I said base table scan, meaning more or less SELECT COUNT(*) or equivalent so that the execution plan is a table scan or clustered index scan, not a nonclustered index scan, but the formulas are approximately valid. It turns out that the cost of logic, i.e. AVG, SUM, and other calculations can be quite expensive. A single aggregate on an integer column appears to cost about 0.20 CPU-microseconds per row, meaning the 20 rows per page in-memory table scan cost is now 6 CPU-microseconds versus 2 for the base. The TPC-H query 1 has 7 aggregates, 3 multiplies and 3 add (or subtracts), yielding a net data consumption rate around 140MB/sec on a single core.

Storage Configuration

So should each application be tested to the actual data consumption rate for the most important aggregate query? I think this is too complicated and narrow. Even if the main query is complicated, it still helps to have brute force capability in the storage system. This is why my recommendation is to simply fill the available PCI-E slots with controllers, which are relatively inexpensive, and distribute many disks across the controllers, with consideration for the cost of the disk enclosures. This means avoiding the big capacity drives, i.e. skip the 300GB 15K drives. Last year, I recommended 73GB 15 drives. Today, there is little price difference between the 73G and 146G 15K drives, so go for the 146G 15K drives.

Memory

On memory, forget the stupid 4GB per core rule. I see no justification for it. Memory is cheap. Fill the DIMM sockets with the largest capacity memory module where the cost per GB is essentially linear. Two years ago, 2GB ECC DIMMs were around $200, and 4GB ECC were around $1000, so 2GB DIMMs were the recommendation then. I just checked on Crucial, today 4GB ECC is less than $200 each, while 8GB is around $850, so 4GB DIMMs are the recommendation for now.

Processors

Finally, a note on recommended processors: Large data warehouse queries do benefit from high clock rate (only comparable within a micro-architecture, do not equate Opteron to Core 2 frequency). DW queries do not benefit from cache size. So if the same frequency is available with different cache sizes, at different prices, just be aware that processor cache size does not impact DW. Large processor cache does significant benefit high call volume transactional queries, so the higher price is more than justified there.

Reference Configurations with Direct Attach Storage

Below are my reference configurations on the assumption the data warehouse is not clustered. I have included prices obtained from the Dell and HP websites on 26 February 2009. If you can get Dell or HP to quote the price of SAN based storage as in the Microsoft/Dell/HP reference configurations

Dell PowerEdge 2950 III, 2x3.33GHz Xeon X5470, 32GB memory

2 PERC6/E controllers, $850 each

2-4 MD 1000 w/ 15 x 146GB 15K SAS drives, $7K each

Total cost: $22K with 30 disks, $36K with 60 disks

PowerEdge R900 4 x Six Core Intel Xeon X7460 2.67GHz, 16M, 128GB, $21,366

6 PERC6/E controllers, $5,100,

6 MD 1000 w/15x146G 15K, $43K

Total cost: $70K.

While I do agree in principle with the concept of balanced configuration, I ask the question: which is better? Each configuration is approximately $36K.

1 PE 2950 w 2x3.33GHz X5470, 32GB memory, 2 PERC6, 60 x 146G 15K disks

or 1 PE R900, 4x2.67GHz X7460, 128GB memory, 2 PERC6, 30 x 146G 15K disks

HP ProLiant configurations with Opteron (Shanghai, 6M L3) processors.

DL385G5p, 2 x Opteron 2384 2.7GHz, 32GB, 2 x 72GB 15K HDD, $7,282

DL385G5p, 2 x Opteron 2384 2.7GHz, 32GB, 16x72GB 15K HDD $12,500

DL 585G5, 4 x Opteron 8384 2.7GHz, 128GB, 2 x 72GB 15K, $21,500

DL 785G5, 8 x Opteron 8384 2.7GHz, 256GB, 2 x 72GB 15K, $60,600

MSA60, 12 x 146K 15K $7K ($5765 bundle price?)

Smart Array P800 $949

Smart Array P411 $649

The lowest cost DL385G5p DW configuration is to populate all 16 internal SFF drive bays, then attach 2 MSA 60, each with 12 15K disks. The preferred DL585G5 configuration is to populate 6 RAID controller, with 2 MSA 60 (directly connected, not daisy chained) on each controller in the 3 x8 PCI-E slots, and 1 MSA 60 for each controller in the x4 PCI-E slots.

On the DL785G5, place RAID controllers in each of the 3 x16 and 3 x8 PCI-E slots, each connecting 2 MSA 60, and connect a single MSA 60 for RAID controllers in the x4 PCI-E slots. Defer to Gunter Zink’s team for any more specific slot to disk configuration.