Microsoft Bi solution reference

Question:

Why Bi, statistics and analysis of massive data through BI solutions, compared with pure tsql statistics, can improve query and analysis performance, in addition, multidimensional analysis can help customers better understand data.

Recently, I have been asked a few similar questions, so I have summarized some of them and gradually gained an unformed accumulation, which is roughly recorded as follows.

Microsoft may be subject to some restrictions, so the support of many products in China is not very powerful, and there are fewer people engaged in related development. Based on my experience over the years, this article provides some reference projects for your evaluation.

According to different situations, the product name mentioned in this article is not labeled with a version, but generally it refers to the latest version. Later versions may change slightly, but based on my experience, it will not appear in the next three years.

 

 

Microsoft biProduct System:

Sqlserver

The core of Bi, which consists of three parts: SSIs, SSAs, and SSRs.

SSIS is responsible for ETL and overall Bi scheduling. The graphical interface is relatively intuitive.

SSAs, Analysis Service, including cube and data mining. It is also an independent database that is the same as what we usually see in tables and databases.

SSRs, reports, including subscription and publishing. The latest version integrates Dundas, which is better than before.

The above three modules are developed through Visual Studio shell.

 

 

Subsidiary Product System

Office

It is reflected in Excel, and Visio also has some, but has never seen applications.

Moss

The paid version of Sharepoint is Microsoft's portal solution.

PPS is integrated into the new version, that is, the previous puke.

 

 

Solutions based on Microsoft's product architecture:

Windows Server

IIS

SQL Server> SSIS-SSAS-SSRA

Moss-> PPS

Office

Advantages: a full set of Microsoft Solutions, seamless integration of all parts. Front-end customers have low training costs and are familiar with office tools.

Disadvantages: it relies entirely on Microsoft's system solutions. For example, to use a PPS function, you are forced to deploy moss and purchase the entire Moss authorization. Maintenance of moss is a certain cost.

We recommend that you read this article unless you have decided to purchase Microsoft products.

 

 

Common solutions:

Windows Server

IIS

Sqlserver-> SSAs

ETL layer custom framework

Developed by the front end using third-party components

Advantages: ETL and UI developed by yourself can solve complicated requirements. Relatively speaking, the UI Layer is quite different. For example, it is said that many internal departments of Microsoft use Excel to connect data.

Disadvantages: high development and maintenance costs.

 

It is worth mentioning that the ETL of my recent project is a framework encapsulated by the Team and does not need SSIS at all. Some experts at Microsoft's U.S. headquarters also mentioned this solution. My former team members would rather implement ETL on their own unless the data volume is less than 10 million.

As to why SSIS was abandoned, previous team members once reflected the details that Oracle data is often left dead and cannot be found. Later, I consulted some DBAs about the problems caused by the Oracle driver version. I believe that such a tough detail is enough for many brothers to abandon the SSIS platform.

 

 

My recommended solution:

Windows Server

IIS

Sqlserver-> SSAs

ASP. NET-> WebServices

Silverlight

GIS

This is the Bi + Ria + GIS solution I have always recommended. That is, the use of business intelligence, rich client strong presentation capabilities, and through map auxiliary groups to better display data for customers.

Required knowledge system:

Operating system, basic operations, maintenance, and installation.

The most basic skills of databases and Bi.

The IIS and BS solutions have now become the first choice. In some cases, SSAs also depends on IIS

SQL is mainly used in the ETL layer, and the workload is more than half of the total bi.

MDX, which is used to query cubes. If you can mine data, you also need DMX.

Powershell, which saves a lot of trouble.

. Net, powershell depends on this, and the following depends on this.

C #, encapsulation service, and Silverlight development.

Silverlight is faster to use than Flash. NET developers.

The project may need support from some third-party commercial components as needed, such as the Silverlight chart component. It is more cost-effective to buy a set of ready-made components than to invest a few people in the Development for a month.

 

 

Domestic biStatus quo:

First, data quality. This exists in many industries, and the quality of data is quite gloomy. For example, the numeric field of the foreign key can have full-angle numeric fields. One reason is that the implementation of IT intensity is not enough. Just like teaching the wife to keep an account, the wife won't or is unwilling to do it. Even if it does, the data obtained is meaningless.

Second, demand. There are many reasons for confusion in requirements, such as lack of understanding of Bi and recognition of your own business. In fact, many industries and projects are not "Confidant ".

In short, there are many projects with few successes. Most of them are bi for reports and face-to-face problems.

 

 

Is Microsoft's solution suitable for you?

Compared with other solutions, it can be said that Microsoft products do have some shortcomings. However, with the evolution of the sqlserver version, the gap between the current version should be very small, of course there are no other products to solve, and Microsoft products cannot solve the problem.

If the problem cannot be solved, I think we should first look at bi modeling and then look at the requirements, because most of the things you are struggling to solve, in fact, it was not followed by users. The latter is purely a face project.

 

How long does it take to implement my functions?

This is a frequently asked question. To solve this problem, we need to analyze the data, such as the data volume and data quality. In addition, there are also the complexity of the requirements and the complexity of the business, all determine the length of time. Of course, the project can continue to make the plan.

Other projects are basically developed in the form of a spiral increase. It is seldom said that the first version is successful. This process requires constant exploration and accumulation.

 

Good recruitment and teamBuild

There are very few people who directly work on Bi, but there are basically two cases: one is to switch from DBA, the other is to switch from development. I think I am the latter. The former focuses on the bottom layer, while the latter focuses on the surface layer. During recruitment, you can choose based on the characteristics and project situations.

About team building, I have seen a lot of separation between the back and the front. That is to say, the Bi development at the database layer is a person who manages the "back" and implements. NET development.
The sender is the "frontend. I have no objection to this division, but according to my experience, we must pay attention to the frontend and backend connections. Although the two parts are two teams, we must have a cognition that everyone is a team.
Together, they jointly undertake the success or failure of the project. Here, we also need a balance to make some decisions. Some things can be solved by the back end, which can save a lot of people in the front, while some things can be processed by the front end if they can
There are a lot of troubles.

 

BiIs there a suitable reference resource?

I suggest you read the books on line of sqlserver. Try to read the English version as much as possible. The translation of some details of the Chinese version is not flattering. In addition, Microsoft's webcast is also worthy of reference. Although each lesson is very long and boring and abstract, it is recommended that you check it out.

 

 

Summary:

Slave
From the perspective of a product, Microsoft may not be doing well in this small aspect, but there is no big gap with other solutions as a whole, there are still some gaps, but some of his
These advantages are enough to make up. From the perspective of an employee's experience, this article tries its best not to take any emotional color. Some of them may contain my own short views in some fields, or there may be errors that lead to mistakes.
Experts pointed out. Bi is a potential field and a valuable field. It is also a new field in China. The circle is very small. I hope some people will give some advice.

 

---------------------------------------------------------------

Aspnetx Bi notes series index:

Use visifire in Silverlight to display data in a cube

Use visifire in Silverlight to display data in a cube (2)

Bi notes-incremental processing of Multi-Dimensional Datasets

Bi notes-General Bi Process

Bi notes --- several methods for deploying SSAs

Bi notes --- several solutions of SSAs database Process

Bi notes-a solution for cube incremental Processing

Bi notes --- solutions for different aggregation methods in SSAs for a certain amount of records based on different dimensions