SQL Server bi: (2) Analysis Service Application

Part 1ArticleMainly introducesBiIt mainly involves the raw database, data warehouse database, dimension table, fact table, and related import work. After the data is imported, it means analysis, statistics, and mining. This is all the underlying work of Microsoft.Analysis ServiceWe have done a good job for us. We have learned how to use it so that we can "look amazing.

OpenSqlserver data tools, You can createAnalysisProject.1:

Mainly dividedData sources,Data Source views,Cubes,Dimensions,Mining Structures.
Data sourcesIt is mainly the database source, that is, the databases I use as the Warehouse database. My connectionDemodb.

Data Source viewsWhich tables are used from the data source. I have no tables in this database. I want to import all three tables.2:

Cubes: DataCubeBody. What is a data cube? This is mainly understood together with the concept of dimensions. We are actually divided into three dimensions,X, Y, ZSpace Determined by three coordinates. The database may contain many dimensions, but we cannot imagine things beyond 3D in our cognition. This is just a concept. Visible,CubeIt depends on dimensions. So when we createCubeYou need to understand the followingDimensionWhat is it.

Dimensions:CubeDimension, eachCubeYou must create dimensions here for statistics. Although dimensions are automatically created when a cube is created, sometimes they cannot achieve our goal. Therefore, we should first createDimension, And then createCube.

Mining Structures: Something used for data mining. I won't talk about it here, because I haven't used it yet. I just read it.WebcastVideo, which introduces the integration of a large number of existing miningAlgorithm, You can easily make the corresponding analysis trend. It seems fierce.

 

CreateDimension

Create a time dimension for ourUseractioncubePrepare.

Right-clickDimensions-> new dimension-> use an existing table-> main table (dimdate)-> available attributes (Select all because the year, month, and day are used as statistical conditions.). This dimension is initially created.

Now you can seeDimdateEditing interface,

Attribute on the left, hierarchy in the middle, and table on the right.

Because our statistical requirements may make statistics on the month and year, if we count a month, we need to make statistics on the facts of all the days of the month.ProgramStatistics are very slow and complex, but dimensions have a hierarchical concept. Therefore, we can establish a hierarchical relationship in the middle column, so that the system can help us do a good job of hierarchical statistics.

Build Level result

 

However, there is an exclamation point on it, and the mouse moves up to find that there is no such relationship between attributes. So we need to modify attribute.OfRelationships(On the tab page in the Red Circle of the figure above ).

Currently, the default relationship is level-level. They have no level relationship, so we need to establish a good relationship by year, month, and day.

 

The yellow exclamation point does not exist.IDAttributeDay,DayBelongMonth,MonthBelongYear. Operation Method: delete an existing line, right-click an item, and create a new link.

 

Now you canProcessing dimdate(Process dimension), and then clickBrowserLook at the final data, my example:

 

Now that the dimension has been created, I will createCube.

You only need to select a fact table. The dimension is blue. During the creation process, we can select the createdDimdateDimension can also be automatically created by the SystemDimactionDimension, because this dimension does not require our expertise.

 

InProcessing factuseraction cubeBefore that, don't forget to go firstProcessing dimactionIf you have one. Then you canBrowserThisCube. An exception occurs on my side, so I don't need to map it. I need to paste it to another machine.CubeLet's take a look.

 

On the left side of the page, you can see that the field in the fact table is directly dragged to statistics, and the field in the dimension table is attribute. Generating a report is not bad. However, this drag-and-drop operation can only be viewed by ourselves. To enable O & M and product personnel to view the drag-and-drop operation, we need to develop a report Display System to display the cube.Data in. Let's talk about this next section.

 

【PS] If you have any questions or questions, you can leave a comment or addQqGroup, in the upper left corner of the page. We will discuss it together. Thank you.