How-to start Business Intelligence in Microsoft SQL Server

Business Intelligent is a concept. The users will not find a single product called Business Intelligent in Microsoft products’ stacks instead of Business Intelligent in Microsoft is formed by using various sets of Microsoft’s services and products.

Business Intelligent can be viewed as a 3-tiers application by its nature. It needs the backend, the middle-tiered and with the User Interface (UI). Microsoft SQL Server, since 2005 has been come up with the complete tiers for Business Intelligent. Database Engine is the backend of the tier, Analysis Services will fall under the middle-tier and lastly the Reporting Services can be served as the UI in order to deliver information to the appropriate business users.

Reporting Services is not the only UI tool that can deliver information to the users. There is another famous product of Microsoft Office’s product can be used to retrieve the information from the middle-tier of Business Intelligent, Excel. Excel is presents in the form of pivot table, and it can be used for Business Intelligent UI and reporting. Users can basically form their reports by drag and drop the items (dimension and measure) to the pivot table.

ASP.NET is uncommon method to build up the UI for Business Intelligent. In order to customize the UI in ASP.NET, a developer is definitely needed. The developers not only need the skill of ASP.NET but as well as ADOMD.NET (ADO Multidemension) with Microsoft.AnalysisServices.AdomdClient namespace.

Some of the advanced users will expect “here come the AdventureDW”. Yes, you are right. AdventureDW has the most samples built for Visual Studio 2005 Business Intelligence Project. Unfortunately, in term of ADOMD or ADOMD.NET, I found many samples using Foodmart 2000 that came with MS SQL 2000. Even worse that I could only get the Foodmart database for Analysis Services (cube or abf) but I cannot the source database (mdf).

MS SQL 2008 is rather new so I will prefer to use MS SQL 2005 (Developer Edition) to get it started. Business Intelligence’s samples will not be installed by default even you choose Advance Options during MS SQL 2005 installation. To install the Business Intelligence’s sample after the complete installation of MS SQL 2005, you can do the followings:

1.  Click the Start -> All Programs -> Microsoft SQL Server 2005 -> Documentation and Tutorials -> Samples -> Microsoft SQL Server 2005 Samples (English)

To move your first step:

1.  Click the Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio

2. File -> Open -> Project/Solution…

3. Open Analysis Services Tutorial.sln from C:Program FilesMicrosoft SQL Server90SamplesAnalysis ServicesTutorialsLesson4

4. Right click the solution Analysis Services Tutorial -> Deploy

5. Open Analysis Services Tutorial.cube click Browser tag.

You should be able to drag the dimension to the viewer now.


Microsoft Business Intelligence Overview


Analysis Services Developer InfoCenter


About chanmingman

Since March 2011 Microsoft Live Spaces migrated to Wordpress ( till now, I have is over 1 million viewers. This blog is about more than 50% telling you how to resolve error messages, especial for Microsoft products. The blog also has a lot of guidance teaching you how to get stated certain Microsoft technologies. The blog also uses as a help to keep my memory. The blog is never meant to give people consulting services or silver bullet solutions. It is a contribution to the community. Thanks for your support over the years. Ming Man is Microsoft MVP since year 2006. He is a software development manager for a multinational company. With 25 years of experience in the IT field, he has developed system using Clipper, COBOL, VB5, VB6, VB.NET, Java and C #. He has been using Visual Studio (.NET) since the Beta back in year 2000. He and the team have developed many projects using .NET platform such as SCM, and HR based applications. He is familiar with the N-Tier design of business application and is also an expert with database experience in MS SQL, Oracle and AS 400.
This entry was posted in .Net. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.