金沙bb电子糖果

書名： Getting Started with SQL Server 2012 Cube Development
作者名： Simon Lidberg
本章字數： 804字
更新時間： 2021-08-06 16:48:55

Adding objects to the data source view

Now we will start adding more logic to the data source view. We will add new tables and add calculations as well as changing some of the tables to bind to queries.

In order to add new objects to the view, you can click on the Add/Remove Objects button in the left-hand upper corner in the design area, another option is to right-click in any of the empty space in the diagram window and choose Add/Remove Tables... to the data source view.

In the same menu, you also have the option of finding objects within the view. This is a feature that can become handy when you are working with large schemas that have many tables. Another good feature to navigate around a project is the ability to group the objects into different diagrams; so that you will have one view for internet sales and one for reseller sales.

Sometimes the tables do not contain the relevant information in one column or you have to create some other calculation. You can easily create additional columns in a table by right-clicking on the table and choosing to add a New Named Calculation. You write the calculation in normal T-SQL.

In some cases you need to create a table representation that does not exist in your data source. An example of this would be joining several tables down in the database so that the result set could be used in your multidimensional cube. To do this you can create what is called a named query. You can see that you have this option when you either right-click on a table or when you right-click in the design window.

There is one important consideration that you need to have in your mind when you use both the capability of adding columns to tables as well as binding to queries and that is the fact that when Analysis Services processes the cube, every query sent down to the relational database will be in the format of a subquery. This means that the query that you write will be wrapped in an outer query automatically by Analysis Services. Due to this, there are some limitations to what your query can contain; as an example, you cannot use a group by with a having clause in your statement.

Tip

For more information about what the limitations are with subqueries in SQL Server, please refer to the following link:

http://msdn.microsoft.com/en-us/library/ms189543(v=sql.105).aspx

Extending the data source view

Now it is time to start extending the data source view to contain more tables as well as some additional logic. First thing that we will start with is adding a new calculated column to the DimCustomer table:

Right-click on the DimCustomer table and choose New Named Calculation.
In the Column name: box, type FullName.
In the Expression: box, type [FirstName] + ' ' + [LastName].
Click on OK to save the calculated column.

Now you have a new column in your DimCustomer table that concatenates the first name with the last name of the customers to create their full name. In order to test your new column, right-click on the DimCustomer table and choose Explore Data.

As you can see, you now get an Explorer that shows a sample of the rows in the DimCustomer table. The Explorer view by default shows you the top 5000 rows in a table but you can also view a sample based on a random sample by clicking on the Properties button in the top right-hand corner.

Now it is time to change the DimDate table to instead use a query as the source. To do this, perform the following steps:

Right-click on the DimDate table and choose Replace Table | With New Named Query.

In the Create Named Query window, type in the following query:

-- Listing 3.1
SELECT DateKey, FullDateAlternateKey, CONVERT(varchar, FullDateAlternateKey, 101) AS DateName, DayNumberOfWeek, EnglishDayNameOfWeek, 
  SpanishDayNameOfWeek, FrenchDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear, WeekNumberOfYear, EnglishMonthName, 
  SpanishMonthName, FrenchMonthName, MonthNumberOfYear, CalendarQuarter, 'Q' + CAST(CalendarQuarter AS varchar) 
  + '  ' + CAST(CalendarYear AS varchar) AS FullCalendarQuarter, CalendarYear, CalendarSemester, FiscalQuarter, FiscalYear, FiscalSemester
  FROM DimDate

Click on OK to save the query, then right-click on the table to explore the data to make sure that your query works.

Third task that we will perform is to add an additional table to the data source view:

In the Data Source View Design window, click on the Add/Remove Objects button in the upper-left corner.
In the Filter box, type DimPr and click on the Apply filter on available objects button
Mark the DimProductCategory table and click on the button marked with > and then click on OK to add the table.

Now you have added all the relevant objects to the data source view and we will now continue with the multidimensional modeling steps needed to build a good model.

官术网_书友最值得收藏!

Getting Started with SQL Server 2012 Cube Development

Adding objects to the data source view

Tip

Extending the data source view