SQL Server BI
- By Derek Comingore


PowerPivot Logo Take the Alpha Geek Challenge!

The second PowerPivot Alpha Geek Challenge has begun! In challenge #2 (which runs from Feb 4th - Feb 13th), Microsoft's Donald Farmer wants to see your most interesting data set loaded into PowerPivot. Go to the PowerPivot Alpha Geek Challenge website and click on the 'Challenge 2' button to get all of the details directly from Donald himself. *Make sure you watch the accompanying video!

I was fortunate enough to win the first Alpha Geek Challenge. As a result I won a $250 Microsoft Store shopping spree and a chance for a free trip to the 2010 Microsoft BI Conference in New Orleans, LA!

On a personal note, I've seen various comments online regarding the Alpha Geek Challenge stating "its too hard to understand what Donald is asking for". Please don't over think the challenge(s). Just listen to what he is saying in his videos. PowerPivot makes creating BI applications easy!

PowerPivot Alpha Geek Challenge Website: http://exceleratorsquiz.com/challenge.html . Click on Challenge 2...

Happy Power Pivoting!

 


This blog post was well overdue for me personally. I did blog about The Top 5 Reasons Your Company Should Consider SQL Server PowerPivot but that was all I said.  I’ve been using SQL Server PowerPivot off and on for a few months now, been provided some great help by the product teams/fellow MVPs, and have been reading other’s thoughts and technical content on the product even longer. Finally, this blog post is not an official product review, merely my own personal thoughts on SQL Server PowerPivot and its related topics. There is a difference in giving out one’s own thoughts and opinions on a topic and that of an official review.

I have one basic premise for this post: SQL Server PowerPivot is going to be a long-term win for Microsoft Corporation. The product will not be a direct, immediate, quick win. Rather, the product will slowly and yet continuously provide increased levels of business value, grabbing higher levels of customer adoption, and as a result vastly contribute to the Microsoft BI platform’s overall market positioning and share as time passes. “BI for the Masses" continues to advance onwards. And as a result here is a second premise: start learning SQL Server PowerPivot! While I’m at it here is a third: SQL Server PowerPivot will increase the utility and demand for Corporate BI.

Perhaps you are thinking “well gee anyone could say that and be right”? You’d be right (assuming the product does well) but let me explain the reasoning behind my premise. And please feel free to leave your own thoughts and opinions here at my blog.

Self-Service Business Intelligence (SSBI) is still fairly new. Most enterprises have not yet adopted SSBI solutions as they are still working away at getting Corporate BI right. In the next ten years, as an industry, we will see the beginning of main stream adoption of SSBI solutions. Yes, I know, “XYZ Company is doing SSBI today” but look at the other 30 companies who are not doing SSBI today.

Enterprises will continue to prioritize Corporate BI solutions over SSBI ones. SSBI products provide their highest levels of potential value when used in conjunction with an established Corporate BI vision and associated solution(s). SSBI is but one part of the larger puzzle; it is not the entire puzzle. Those are bold statements, feel free to “blast me” in the comments here at my blog.

Finally, I look to the competing SSBI landscape as we know it today and what is Microsoft putting into place (good or bad). I’m not going to tell you SQL Server PowerPivot is the perfect SSBI product. What I will tell you is that for a v1 SSBI product it’s good, real good. One feature of the product that I think Microsoft hit the nail on the head with is that SQL Server PowerPivot is Managed Self-Service and the product encourages managed usage. What the product team does with SQL Server PowerPivot for v2 and beyond is what counts most. At this point I am confident that customers are taking notice and will begin to consider the product’s long-term adoption. There are some “pickle” scenarios still to be figured out, but I have confidence in the product teams, after all just look at SQL Server 2008.

Take a weekend and learn SQL Server PowerPivot, it will be time well spent.

 


Here are the features of the upcoming SQL Server PowerPivot (self-service BI) SSBI product that I see being key in dominating the product’s existing competition:

1. Applications are Managed by IT

2. Excel Integration

3. Imported Data is Read-Only

4. Combined w/x64 CPUs; Extremely Scalable Information Producer Experience

5. Information Producers are Encouraged to Collaborate & Share via SharePoint


Even over the holidays I occasionally update and read what my colleagues are up to via Twitter (http://twitter.com/dcomingore). Today, (thanks to fellow SQL Server MVP Vidas Matelis for the heads up) the below MSDN blog post has surfaced, confirming the Microsoft BI 2010 conference details.

It appears the conference is to be collocated with Tech Ed North America this year on the week of June 7th, 2010 in New Orleans, LA. Full details can be found at

(http://blogs.msdn.com/usisvde/archive/2009/12/28/mark-your-calendars-for-microsoft-bi-conference.aspx).
I have compiled a list of valuable PowerPivot resources (mostly discussed on Twitter with my peers) to kick-start you in your PowerPivot voyages...

  1. www.powerpivot.com : Official Microsoft Site
  2. http://powerpivotpro.com/ : Rob Collie's site, lead program manager for Gemini, oops I mean PowerPivot
  3. http://powerpivot-info.com/ : Microsoft MVP Vidas Matelis's community site
  4. http://powerpivotgeek.com/ : Dave Wickert's blog
  5. http://powerpivottwins.com/ : Denny Lee's (Microsoft SQLCAT) and Dave Wickert's combined site
  6. http://www.facebook.com/PowerPivot : Official Factbook page for PowerPivot
  7. http://bivoyage.com/presentations.aspx : My company's website that hosts a copy of a PowerPivot presentation we did for a recent SQLSaturday event.
  8. http://www.linkedin.com/groups?gid=2437685&trk=myg_ugrp_ovr : LinkedIn "PowerPivot Professionals" Group
Active PowerPivot Twitter Users

  1. http://twitter.com/VidasM/ssas-powerpivot/members
As our Industry Bytes blog mentioned earlier located here http://www.sqlmag.com/Articles/ArticleID/103179/103179.html?Ad=1, PowerPivot is now available to the public. PowerPivot for Excel 2010 beta, PowerPivot for SharePoint 2010 beta, and SQL Server 2008 R2 Nov CTP are all currently available builds.

Download Locations

  1. www.powerpivot.com : Excel 2010 PowerPivot add-in
  2. http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx: SQL Server 2008 R2 Nov CTP
  3. http://technet.microsoft.com/en-us/evalcenter/ee390818.aspx : Office 2010 Beta
  4. http://technet.microsoft.com/en-us/evalcenter/ee391660.aspx : SharePoint 2010 Beta
Note: @VidasM has been working on some great tutorials regarding single-server install instructions. You can find the results of his current efforts @ http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint .

Briefly, here is a general overview of how the various PowerPivot components are used together to form a PowerPivot Suite (client and server components)

  • SharePoint 2010 provides the portal technology and collaboration foundation for PowerPivot workbooks 
  • SQL Server Analysis Services 2008 R2 gets installed in "sharepoint integrated mode" to provide PowerPivot processing services
  • Excel 2010 hosts the PowerPivot add-in that is used by Information Producers to create ad-hoc, performant, self-service BI models.
PowerPivot has been available for some time now in various priviate builds (now fully public), however we are now approaching a point in the technical community's embrace of the product where I expect to see a continued explosion of outstanding PowerPivot content!


Attunity Inc. (www.attunity.com) has become the premier real-time data integration SSIS 3rd party company for several years now. On October 27th, 2009 Attunity announced a new suite of their award winning Change Data Capture (CDC) technologies that introduces support for new data sources. The new suite of real-time data integration components provides out-of-the-box support for the following enterprise repositories:

  • Oracle
  • IBM z/OS DB2
  • IBM iSeries DB2
  • HP NonStop SQL/MP
In addition, the new suite will provide the ability to target multiple, target receiving repositories. With the new Attunity suite SSIS customers will be provided a solution that enables real-time data replication across the enterprise including for BI applications while gaining more business value through existing SQL Server licenses.

For more information on the new Attunity Suite of Real-Time Data Replication technoloiges please see the following URLs:


I will be speaking at SQL Saturday #23 Louisville on the New Managed Self-Service Capabilities of SQL Server 2008 R2, 'Gemini', as well as Enabling Real/Right Time Data Integration (DI) with the Attunity Suite of DI products. We have some really great speakers lined up for the event including other MVPs. More information on the event can be found below. If you plan to be in the Louisville, KY area on October 24th we would love to have you attend!

What is SQLSaturday?

It's a free one day learning event for SQL Server professionals, loosely patterned on the well known Code Camp model, organized by the Louisville SQL Server Users Group and supported by End to End Training, SQLServercentral.com, ConfIO, PerpTech, Microsoft, and BI Voyage.

When and where will it be held?

SQLSaturday Louisville will be held October 24th, 2009 at New Horizons Computer Learning Center, 10200, Linn Station Road, Ste 110, Louisville, KY 40223 3841.   Event check in will begin at 8 am, sessions begin at 9 am, and we expect to wrap up the event between 4:30 & 5:00 pm. Our thanks to Donna Christie, Account Executive, New Horizons, for her efforts in making the facility available to us for the event.

Who will be speaking?

We have local SQL Server experts Dave Fackler, Sarah Barela, Derek Comingore - in addition to speakers from out of town.

What else can you tell me about the event?

  • This event is free to anyone that wishes to attend
  • Check in for the event opens at 8 am
  • We'll have coffee and doughnuts for breakfast
  • Sessions begin at 9 am - no keynote!
  • We'll be raffling off prizes throughout the day and we'll save a few special ones for the end of the day
  • We'll have a post event party - location to be announced - if you want to continue the talk after the sessions finish and maybe spend more time with our great speakers.

Directions & Hotels

Our primary hotel for the event will the Courtyard Marriot Louisville http://www.marriott.com/hotels/travel/sdfch-courtyard-louisville-east/.


Happy Weekend SQL Pros!

I figured Saturday morning was a good time to sit down and finish out this series of “BI for the DBA” blog posts here on SQL Server Magazine.

I’ll start off by saying please feel free to post your comments/questions here on the blog or feel free to email me at dcomingore@bivoyage.com . I’ve found taking questions is a very good way to improve my own knowledge and skills.

BI Adoption Obstacles

As a new BI entrepreneur, making quite a few blunders (and learning along the way), I try my best to keep my finger on the “BI pulse”.  There are two major reasons why companies that want BI do not further engage:

·         Traditional BI engagements are exactly as Michael Otey described: big, bet-the-business endeavors. ROI cannot be realized quickly as a result.

·         Lack of available BI expertise

I placed quite a bit of focus on the topic of Rapid BI in my prior posts because those solutions help a company overcome the first objection cited above. Regarding the second objection, companies that invest in BI produce more BI experts as a result (chicken and the egg). Support the DBA who wants to do BI in developing a small data mart to “play with”. Engage with a consulting company for a pilot BI project. These are small, incremental steps that can produce initial BI-based value.

In an effort to give-back to the community I value the opportunity to assist those SQL Server DBAs and Developers who wish to transition to BI. I’m not the first and certainly won’t be the last BI community activist who discusses both the business and technical sides of business intelligence. If you have read my prior two posts you should now have a pretty good idea about what BI is. Now it’s time for ”How to implement BI”…

Implementing BI

Let me start by saying this is a very large topic. There is no way I can explain every detailed BI concept in a book, little alone a blog post. One of the challenges associated with building and delivering BI solutions is the sheer volume of available architectures, technologies, and processes. With every BI project I participate in, I learn something new. So my first message is “don’t become so overwhelmed by the forest that you miss the trees”. No one is an expert in every BI technology or platform. However, what you can do is become an expert in selective areas and maintain a good understanding of the others.

A good way to begin learning BI is to comprehend the common architectures (how each tier works with another) and then dive into those areas you wish to master.

BI Architectures

Using the image above as a guide for the remaining discussion you can see that the process starts with the various OLTP databases the business wishes to collect data from. These targeted data sources provide us the “copper” we will transform into “gold”. We use Extract, Transform, and Load (ETL) processes to collect and mold the data into a format that is useful for denormalized database schemas (data marts & warehouses).

There is an optional layer called the Operational Data Store (ODS). The ODS is a normalized solution to enable right-time analytics. If you do employ an ODS your ETLs must then move and transform the data into the downstream Data Mart & Warehouse thereafter.

The data mart (or warehouse) tier is simply denormalized schemas that are useful for reporting & analytical consumption purposes. These relational databases are designed to combine 3rd normal form tables into star and snowflake schemas. Dimension tables contain descriptive attributes and hierarchies while fact tables contain the actual measurements.

The OLAP server contains cubes that are useful for performing Fast Analysis of Shared Multi-Dimensional Information (FASMI). With a cube in place, a business analyst can slice-and-dice analytics to gain a better understanding of how (and why) the business operates in the capacity it does. Technically, there is a second major component of the analytics server called data mining. Data mining is unfortunately a seldom used yet very powerful BI component. For now just understand that data mining is an optional BI component that is employed for predictive analytics.

Finally we have the consumption layer. BI consumption is where the “forest” grows quite large. Collaboration and Analytical servers are usually deployed in-between the core BI infrastructure and the clients in the consumption layer. These servers provide the client applications with additional functionality beyond the pure information provided by the core infrastructure.

There are numerous thick and thin BI client applications on the market today. What is consistent about BI consumption are the logical classifications of functionality they provide:

·         Reporting (Tabular & Aggregative)

·         Analytical Applications that allow one to slice-and-dice cubes and metrics

·         Dashboards & Scorecards used for Performance Management

·         Self-Service applications which are used for on-the-fly reporting & analytical construction

·         Mobile Intelligence applications which facilitate consuming BI on the go

So that is how modern-day BI is implemented from an architectural overview. Hopefully you now understand why companies implement BI and how it is implemented. For those of you new to BI I hope my three posts on “BI for the DBA” have helped you on your personal BI Voyage! Have a great weekend and thanks for your valuable time.


There are a few more Intelligent Enterprise topics I would like to discuss before we dive into the world of DW/BI terminology and how those terms and entities map to an end-to-end elegant Microsoft BI solution.

Organizational Culture

While technology is very useful and powerful it only solves the enablement piece of the Intelligent Enterprise "puzzle". Organizations must commit to change, to grow their business through strategic, value-add corporate IT initiatives. Becoming an Intelligent Enterprise does not end when a data mart and some reports are completed. Becoming an Intelligent Enterprise means committing to a cultural change whereby you empower workers with intelligence. Furthermore, you commit the organization to continuous intelligence improvement by gaining more insight into your business which will result in enriched analytics (and thus business value).

I cannot emphasize the commitment of the business side for BI projects enough. While I all too well understand that BI is implemented in technology, it is 100% business driven! The BI projects I've witnessed having the most success started with someone outside of IT (CXO usually) who became aware of the power and value of adopting BI and drove the "BI stake" into the corporate ground hard. Incidentally, those enterprises who lead BI by the business side are now benefiting heavily from their resulting solutions still to this day as a result. They have a significant competitive advantage as a result.

Rapid BI & Project ‘Gemini’

A friend notified me today that in my recent blog post I completely left out 'Gemini' in regard to Rapid BI. So let's dive into this topic a bit... 'Gemini' will bring about a new era of Rapid BI by taking some (not all, not near all) of the development workload out of IT's hands. So yes, 'Gemini' definitely qualifies as a Rapid BI platform. There are two very different yet strongly correlated worlds of BI: Traditional and Self-Service.

Traditional BI is what I and others like me do for a living. When a company wants "BI" we step in, gather requirements from key stakeholders, and start the design sessions (which translate into many technical artifacts thereafter). We are building the "Corporate Truth" for key business processes the business wants transparency into.

Self-Service BI does not compete but rather complements Traditional BI by allowing Information Workers to build their own analytical models (most likely sourcing some of its data from a data warehouse). How does Self-Service BI complement Traditional BI? Let's say Sue in marketing creates a new self-service analytical model. Sue publishes the new model to a collaboration server. Sue's colleagues really like the analytics she has created. As a result, the business now takes this model to "us" (the BI experts) and asks us to recreate this model in a Traditional BI capacity. Voilà! I could be wrong but from what the experts at Redmond are saying, this synergy of Self-Service & Traditional BI is where more value will come...


I was reading Michael Otey’s 'Getting Started with BI' Article and it compelled me to write two blog posts here on SQL Magazine to serve as a follow-up. In this post I’ll take you on a brief tour of what is Business Intelligence (BI) and why is it so valuable for companies to implement. In part 2, I will take you on a 5000 foot view (mid level) of how traditional Microsoft BI solutions are implemented.

Most current day organizations will have some form of reporting or analytics. A "normal" company will have reports (operational usually) running directly off of various OLTP databases. These reports produce useful business measures for that one operational system. In another area of the company, similar reports (maybe created in a different technology) are built for a different operational system. And there might even be some form of a corporate intranet that hosts one or both sets of these reports for thin-client viewing. Spreadsheets continue to dominate the "normal" company as they are flung in-between meetings with various edits being made to their metrics. The CEO might even receive two different reports for the same metric yet having different results. The situation I am describing is "par for the course" at most companies still to this day...

Now can you imagine working in a company where information is consistent, reliable, and flows throughout? An organization that ensures executives are constantly aware of how their business is performing, where analysts explore free-flowing models to locate hidden trends, and where operations has constant visibility for their daily decisions wrapped with seamless collaboration? What I am describing to you is a 21st century Intelligent Enterprise. Such a company is not reserved only for our generations to come; it is viable today as I write this blog post. It is the idea and current day enablement capabilities of the Intelligent Enterprise that makes BI ever increasingly popular.

So why is it hard to obtain this Intelligent Enterprise model in today's world? First, I could not agree more with Michael's thoughts on this matter. Secondly, I emphasize a lack of general enterprise BI expertise in our environment. So why are there not more BI experts? As Michael said, cost and scope are the big reasons why many BI projects never get going. Most BI industry experts will tell you only ~20% of companies have implemented BI solutions and thus the "normal" scenario I described above still prevails. However, looking forward, more and more rapid BI solutions are becoming available.

Rapid BI is enabled by any combination of tools, technologies, and processes. Agile software development practices are beginning to be leveraged for BI solution development. The Kimball methodology of designing and delivering individual (yet interconnected) data marts enables much quicker ROI for enterprises. The cloud (see SQL Azure) will enable small data marts in the cloud. Microsoft's Fast Track Data Warehouses provide a reusable hardware reference framework to remove the burden of hardware architecture design for enterprise DW/BI solutions. Finally, any premier BI professional services firm should be more than willing to provide a pilot project for your company. BI pilot projects usually translate into the creation of a test data mart (but not always). BI pilot projects should last no longer than six weeks.

 [1]  2  3  4   next page 



SQL Server BI
FEBRUARY 2010
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28       
or
More blogs about technology, databases, and SQL Server.
 

ADS BY GOOGLE