Business Intelligence: plan your first Microsoft Bi Solution

ArticleDirectory

• What is bi?
• What is the problem with querying your company database?
• Support for decision making
• Running bi
• Microsoft Bi Stack
• Solution Development Method
• Getting started
• Subsequent steps

Business Intelligence: Plan your first Microsoft Bi Solution

In this article, I have defined Bi and briefly described the advanced architecture of BI solutions. I also explained how to build a bi solution.

Stacia Misner

Most database administrators (DBAs) encounter some form of business intelligence (BI) when managing data in their organizations and implementing Microsoft SQL Server ). Many others do not have
IT professionals with DBA duties may have heard of Bi, but have never experienced Bi, or even have no idea What Bi is. In this article, you will learn what Bi is. Once you understand bi
The true purpose of the technology and how to construct a bi solution on the Microsoft Bi platform, you will know why Bi is not only applicable to DBA. If you understand bi, you can
When a bi plan appears (these plans will appear), you can better support these plans and learn how to use bi
To track and analyze data related to your role, so that your work is easier and your understanding of the data is enhanced.

This is the first article in a series of articles about Microsoft Bi stacks. In this article, I have defined Bi and briefly described the advanced architecture of BI solutions. I also explained how to build
The process of BI solutions. To learn more about the SQL Server Bi technology mentioned in this article, read Derek comingore
The accompanying article (see "For bi
Solutions build data basics ") and additional articles written by SCOT reagin and me in the next issue.

What is bi?

Several years ago, I joined hands with others.Business Intelligence: Making better decisions
Faster(Business Intelligence: Making better decisions faster, Microsoft Press, 2002) which of my peers should be classified into bi
I am very surprised to have a very different opinion on this issue. What tools are provided by Bi professionals and what are not bi tools?
I was inspired by the different opinions on the tool. There are still such conceptual differences in the IT industry, and many people are still arguing about the definition of Bi. In my opinion, Bi
It is related to both business processes and technologies, so I will start to define Bi from this perspective.

As a business process, Bi
It is a series of activities you perform to collect and analyze data. You can share the analysis results with others to make better decisions and enhance your business. Whether you need information to decide how to make your daily work more efficient or you need to provide support for long-term planning (budget for the following year), the steps you take to search, transfer, format, and study data are
A part of Bi. In addition, Bi includes a process for making the results available for future reference, so that you and others can estimate the impact of your decision after studying the data. In general, Bi
Is an iteration process. You can analyze the data to understand what happened, and take measures to ensure that good things always happen and avoid bad things, then, you can analyze the data to determine whether your measures are to make things better or worse, and whether external factors help or hinder your work.

Because Bi has such a broad definition, you can even use it every day when you write down a small amount of information or create a list to help you make decisions.
Bi. These processes can be more efficient by introducing technologies into some or all business processes that you use to collect, analyze, and share information. When using bi
In technology, organizations that want to start from scratch usually use the technology to solve specific problems first. Over time, the Organization will shift its focus to publish information throughout the Organization, making bi
The application scope of the technology is gradually expanded. As its bi strategy matures, organizations usually obtain more complex tools to better interact with data and study data in depth.

What is the problem with querying your company database?

In any definition of Bi, data is always the focus. You may want to know why you have to bother creating bi when you only need to query one or more databases of an organization to obtain the required data.
Solution? In the following cases, you may not need formal bi
Solution: you are the only user of the data you are collecting. You have the tools, skills, and security permissions to access the company database. All the data you need is in these sources. In contrast, if you need to regularly share information with others with different technical skills and security permissions, you need to create an easy-to-use and maintenance
BI solutions. You also need to consider that when your colleagues learn about this smart Bi solution, they will also want to use it. To meet this expectation, your solution should be scalable at the beginning.

On the surface, it may seem a good idea to allow everyone inside the company to  from the enterprise database through running reports, but this method will not be performed in DBA
Popular in China. They will tell you that directly querying enterprise databases may cause some problems. Below are several more common problems:

• If the Organization's data is stored on different platforms in different formats, it may be difficult to integrate the data into a common format that is useful for analysis. Data cannot be directly copied from the source table to the common target table, but data must be operated in some way before data is stored.




• Data definitions may be inconsistent between multiple databases, and it is difficult to coordinate similar data. For example, the revenue in the sales database can be calculated according to a set of business rules, but it is subject to a set of completely different business rules in the General Ledger database.




• The structure may have been created and optimized for each database for data insertion or query. Even if you only need to query a database, you usually need to summarize a large amount of data for running analysis queries. This operation consumes a large amount of database resources. Therefore, the query may take a long time and may be executed with other applications that execute insert, update, or query operations.ProgramCompetition for resources.




• Historical data is usually archived in enterprise databases rather than maintained indefinitely. If you need to view the trend over time (a common bi
  Activity), your solution may have to become a repository of data that is not stored in the enterprise database.




• Enterprise databases may not have the data required for analysis. The data may be in the format of a flat file, spreadsheet, or unstructured data (such as Microsoft Word
  Document. Obtaining information that a user stores on his or her local computer or brain is tricky.




• Even if the data is available, quality problems sometimes mean that you cannot use it directly from the source. You may need to download and clean the data before analyzing the data. Unless you can clean up data in the source, you need to manually clean it whenever you access the data.
  -Make sure that the same rule is applied each time. In addition, you cannot ensure that others who use the same data will apply the same rules to clean up the data.

To solve these problems related to data access
A solution usually contains a database specially created to save the data for analysis. Having such a database means that you can avoid any resource contention issues between the data generation activity and the data usage activity. Further, you can reconstruct the data to make the query of summarized data run faster. When you need to merge data from different data sources, You Can centrally manage the data and apply the business logic to set the data to a common format with consistent meanings. You can also
XML
Files Or workbooks) are merged to this central location. Another benefit of creating a separate database is that you can clear historical data from the source database and retain historical data as needed. Finally, you can automatically clean up and enhance the data for analysis, so that the same rules are applied each time you access the data.

Support for decision making

BI solutions should not only help you better access data, but also support your decision-making work. In general, Bi
The solution should help you evaluate and respond to business conditions, whether you need comprehensive data for the entire organization or local data from a department, Working Group or single-person team. In fact, the ability to quickly move from a summary view of data to a detailed view of data is
An important feature in Bi.

Bi
The solution is designed to allow you to spend time analyzing data and asking for answers to questions, rather than tracking, merging, reformatting, and coordinating the data itself. When you have plenty of time to analyze data, you can often identify problems as early as possible and take measures to curb negative trends. You can also use
Bi
To discover the associations between seemingly unrelated data points, and then change the strategy to analyze the saved funds or benefits in detail. Every decision you make at work every day (whether you want to solve the problem or plan for the future) will be directly or indirectly converted into the company's cost or benefit.

Running bi

Theoretically, you have a good understanding of Bi, and viewing the running Bi can really help you understand its benefits. To inform you of Bi
The other three articles in this series describe the process of developing BI solutions for a fictitious company named adventure works. At the end of this article, I will explain how to obtain
Adventure works Sample Database, adventure works
Is a fictitious bicycle manufacturer whose products are sold all over the world. Sales staff of the company are responsible for wholesale their products to dealers, such as small bicycle shops or large warehouse stores. Adventure works
It also sells products to individual customers over the Internet. Adventure works data provides a large number of analysis opportunities suitable for BI solutions.

Remember that the Bi solution is designed to support decision making. After clarifying this, let's take a look at the several questions adventure works needs to answer before making a key decision:

• Which sales channel creates more profits? Adventure works must decide whether to hire more sales personnel to establish relationships with more dealers or expand their Internet
  Sales business. To help guide this decision, analysts must be able to compare dealers and the Internet within a period of time.
  Sales performance of the site. The sales performance data points that analysts need to compare include sales, order quantity, and profit margin. A positive trend in profit margins is the most important measure, because if these sales result in a net loss, the company will not make a profit even if the sales volume (in dollar or sold units) is high.




• Does the demand for specific products increase or decrease? Adventure works must make the production level consistent with the sales demand. Adventure
  Works must adjust its manufacturing process to ensure that more such products are available for sale to improve sales performance. If the demand for other products is reduced, adventure works
  It is necessary to reduce production or stop production, so as not to sell at a loss in case of oversupply.

Even if you do not need to analyze sales data for your work, we encourage you
This is also the case when you build a solution. You can apply the same design and development principles described in this series to your own data.

Microsoft Bi Stack

Now, let's take a closer look at the technical architecture of Bi. Microsoft Bi stack provides all the tools you need to build, manage, and use BI solutions. SQL
Servers 2008
Is the basis of the stack of the data platform that carries the data market or data warehouse. The data market must be subject-specific data storage. A data warehouse is an enterprise-wide  containing multiple topics. The boundaries between the data market and the data warehouse are vague, but you do not have to worry about the inability to differentiate them. In this series of articles, I used the term "data market ". (Although this series of articles involves
SQL Server 2008, you can also use SQL Server 2005 to build similar BI solutions, its bi
The component is slightly modified or not modified in comparison to the provided description .)

SQL Server 2008 includes three bi components: integration services (SSIS) and analysis services
(SSAs) and reporting services (SSRS ). These components use the data integration function, multi-dimensional database support, and data presentation layer to expand the data platform.Figure
1Describes the relationship between components in the Bi solution.

Figure 1SQL Server 2008 component in Bi Solution

After you design the physical structure of the data market, you can use SSIS to enter the data extracted from other data sources. SSIS
Provides tools necessary to automate the following processes: Clean up data, merge data from multiple sources, and convert data into a structure suitable for analysis. You can use SQL Server
Proxy to schedule regular execution of these processes. Derek comingore explains how to develop, extract, convert, and load BI solutions on page 1 of his SSIS article.
(ETL) process.

By adding an SSAs database to your bi solution, you can support more complex and high-performance interactive queries. You can use SSAs
Copy the relational data to the multidimensional database structure named Multidimensional Dataset. A well-designed multi-dimensional dataset can be used to optimize temporary query data by adding indexes and abstract tables (called "aggregation") to return query results, the speed of this query method is faster than that of comparable queries executed on relational databases. You can also embed complex computing logic into a multi-dimensional dataset to simplify queries. This query originally required hundreds of rows to be copied when using a relational data source.
Transact-SQLCode. With Multiple front-end tools (called the multi-dimensional dataset browser), you can query a multi-dimensional dataset without writing any code. In the next issue, Scot reagin
This section describes how to develop multidimensional datasets as part of a bi solution.

Whether you want to store data in the SQL server data marketplace or SSAs multi-dimensional data set, you can
Add to solution architecture to make data available to users. SSRs
Is a reporting platform that includes tools. These tools can be used for development reports. They protect and manage published reports and support user access reports through a centralized management infrastructure. You can use an SSRs web application or
Microsoft Office Sharepoint Server 2007 (MOSS)
You can use the subscription function to receive reports by email or call the SSRs web service in your application to display reports. The default view of the report is in HTML
Format display, but you can also export the report to another file type, such as PDF or Excel. Next month, I will explain in detail how to use SSRs in the data presentation layer of your bi solution.

Microsoft Bi stacks also contain several Microsoft Office technologies that increase the number of options for the data presentation layer. For bi
Excel 2007 is a common choice for data analysis in the solution. You can access the SQL server data market or browse SSAs directly from Excel
Multi-dimensional dataset (for exampleFigure 2And you can view the data more freely than when viewing the SSRs report.

Figure 2Use Excel 2007 to browse Multi-Dimensional Datasets

In addition to using Excel
In addition to link data and multi-dimensional data, you can also use its Data MiningAlgorithmFind the hidden information mode from your data or detect exceptions in the data (this means you can correct the problem before filling the data market ). A good way to start data mining is
Microsoft downloads a free Excel add-on and uses it to analyze data imported from any source to excel or view data in SSAs
The output of the data mining model created and stored on the server. The add-on of Visio 2007 can also be used to share the annotation Data Mining Model. You can
2007 data mining add-ons learn more about downloading and using these add-ons.

An increasingly popular choice for shared data is Excel services provided in Moss. By using Excel 2007, you can create a workbook (including
SSAs multi-dimensional dataset is used as the pivot table of its data source), and then the data connection and workbook are published to excel
Services. Although you can publish workbooks that contain data from other data sources
There are many benefits, including the use of most of the same Multidimensional Dataset browsing functions in a pure HTML interface that retains the familiar style of Excel. Another benefit is that excel can be implemented.
Centralized management and access of workbooks. For more information about Excel services, see "Excel services
Technical overview ".

Another benefit of using Excel services for multi-dimensional dataset browsing is that you can use the Excel Web access web part to embed a pivot table and a pivot table.
Moss dashboard page. A dashboard is a special SharePoint content type that allows you to use a variety of Web
The widget displays data from multiple sources on a page. You can even add a filter to the dashboard page and connect the filter to some or all web
Widget to dynamically change the content on the page based on the selected filter.Figure 3A demo Moss dashboard is displayed. Moss also includes
The Web part reported by SSRs on the moss server (an optional SSRs configuration called the integration mode), or you can use the web
Component to display the reports stored on the Report Server (called the default configuration in local mode ).

Figure 3Display workbooks and reports on the moss dashboard page

For dashboards with more functions, you can use Microsoft Office performancepoint Server 2007 (PPS ). Available
PPS to develop score cards and dashboards that can be deployed to Moss. A scorecard is a report that compares the actual performance with the defined goals and displays the results with a color-coded icon. You can use a specific type of Web
The widget displays the PPS scorecard in the Standard moss dashboard or as part of the PPS dashboard. In the dashboard, you can set the PPS
The report is added to the dashboard area to display different views of the same data (for exampleFigure 4) Or displays data from different sources.

Figure 4Use the PPS dashboard to display data

As you can see, although the Microsoft Bi stack provides a large number of options for developing, managing, and implementing BI solutions, it will never prevent you from using other options. Microsoft bi
A stack is a scalable architecture from top to bottom. When you need to support specific requirements, you can insert custom applications or third-party applications in the stack.

Solution Development Method

The best way to get started with Microsoft Bi Stacks is to build a simple solution. The accompanying articles in this series will show you how to use SSIs, SSAs, and SSRs
Build a solution. After reading this series of articles, you will get a general idea of how all these components work. However, you do not have to use every component in the stack for each constructed Bi solution.

When you sort out BI solutions, you may plan to spend most of your time executing data preparation tasks (such as refactoring and cleanup. In bi terminology, this group of tasks is called extract, transform, and load (ETL)
Process.

Before you begin ETL development, you must carefully plan the Bi solution design. Based on my experience, if you have a specific business problem to solve, and first consider the way users interact with information during design, then
Bi
The development of the solution will be smoother. By using a user-centric approach, you can reverse-Execute applicable business processes to design solutions that can correctly retrieve and construct data to meet business needs. This suggestion may seem to be an obvious method, but I have found that many people are trying to transfer solution development from available data to users and eventually adopt a solution that has never been used.

After you propose an initial design, you can start development. If the Bi Solution uses SSIs, SSAs, and SSRs, you can use SSIS to create and fill bi
The data structure of the solution. Once the data is ready, you can build a cube to continue the next step. After development, you can process the cube to load data. Then, you can use
SSRs
To develop a report for querying multi-dimensional datasets and display the query results in the report. In the last three articles in this series, we will show you how to perform this process step by step. For your own project, you should complete this process step by step to ensure that the results of each step can meet the requirements of subsequent steps.

Getting started

To complete the complete Bi solution described in the remaining articles in this series, you must install SQL Server 2008, including SSIs, SSAs, and
SSRs. If you can access SQL Server, SSAs server, and Report Server on the network, you only need to install development tools on your computer. For product installation instructions, see "How to: Install
SQL Server 2008 (installer )". You can
Codeplex download the sample database used to build a solution for adventure works. The first database is
Adventureworks2008, which is an online transaction processing (OLTP)
Database representative, used to capture transactions generated by business operations in the sales, production, and HR departments. You use this database as the source of the data market built using SSIs. The second database is
Adventureworksdw2008 is a data example that represents the best practices in data warehouse design. If you decide not to understand SSIS and directly jump to cube development, you can use this database
The source of the SSAS multi-dimensional dataset.

Subsequent steps

When you are familiar with SQL Server bi
After components, you should find a simple project to consolidate your knowledge. At the beginning, you should try to use the principles described in the next article in this series to construct data without mature data markets. Once you start using
Bi, you may never use the same method to view data.

Stacia Misner Is a bi consultant, instructor, author, and data Inspirations
The Creator and owner of the project. She has been in the IT industry for 25 years, nine of which are studying Microsoft Bi stacks. Stacia has written multiple bi and SQL statements
Server. Her latest book Microsoft SQL Server 2008 reporting services step
STEP (Microsoft SQL Server 2008 reporting services step by step, Microsoft
Press, 2009) was published at the beginning of this year. You can contact her via smisner@datainspirations.com.