Delivering Business Intelligence with Microsoft® SQL Server® 2012, Third Edition

"All of the samples in this book are based on business scenarios for a fictional company called Maximum Miniatures, Inc. You can download the data, image files, and other supporting materials from the book’s webpage on the McGraw-Hill Professional website. This download also includes the complete source code for all of the Learn By Doing activities and the applications demonstrated in the book.

The download is found on this book’s webpage at Search for the book’s webpage using the ISBN, which is XXXXXXXXXX. Use the “Sample Code” link to download the Zip file containing the book’s material. Follow the instructions in the individual Zip files to install or prepare each item as needed." ("The Maximum Miniatures Databases and Other Supporting Materials" section)

I downloaded the supporting materials to build and execute samples to learn and practice what the book explains.

Created: 2019-01-28 20:15:39 Last updated: 2019-01-28 20:15:39

"“Would you tell me please, which way I ought to go from here?” asked Alice. “That depends a good deal on where you want to get to,” said the Cat. “I don’t much care where,” said Alice. “Then, it doesn’t matter which way you go,” said the Cat.

Alice’s Adventures in Wonderland

—Lewis Carroll" (Chapter 1)

A co-worker recently asked me what my goals were for 2019 and I mentioned that I had books to read but other than that, I could not mention anything else. I realized that I needed to define my goals. One goal that I already have and it is in the same direction of what I answered to my co-worker is what I described at I have created a second goal. It is not necessarily something I expect to achieve in 2019 but it is a realistic goal for me as a programmer: Reach the highest level of privileges at Stack Overflow. For example, according to, the highest level currently requires 25,000 points and reaching that level allows to have access to internal and Google site analytics.

Created: 2019-01-24 03:17:01 Last updated: 2019-01-24 03:17:01

"To function as part of effective decision making, a goal must

  • Contain a specific target
  • Provide a means to measure whether we are progressing toward that target"
(Chapter 1)

Instead of buying Kindle books on Amazon the way I used to do it, since December 11, 2017, I subscribed to Safari Books Online. My goal is to see how many books I have read from my Safari subscription and compare how much I would have paid if I had bought those books on Amazon and make my Safari subscription cost-effective. If the result is that the money paid for my subscription is the same that I would have paid to buy the books on Amazon, that would not be interpreted as cost-effective because on Amazon I keep the Kindle books that I buy and on Safari Books Online I lose the books as soon as I cancel my subscription. This means that in order to make my subcription cost-effective my expectation is that the price of the books that I read from cover to cover on Safari must be at least twice the price I would have had to pay if I had bought thouse same books on Amazon. As time goes by, when calculating price comparison explained before, the result must consistently show Safari Books Online as the clear winner in terms of cost-effectiveness. In other words, Safari Books Online must be consistently the winner and not only once or a few times and then have Amazon as the winner again, which could be the case if I achieve the goal but then stop reading books and continue paying the Safari subscription. My plan is to continue to pay the subcription for the rest of my life. This goal that I have defined contains a specific target and it provides a means to measure whether I am progressing toward the target.

Created: 2019-01-07 01:42:26 Last updated: 2019-01-07 01:42:26

"Solution Explorer Window The Solution Explorer window is visible in the upper-right portion of Figure 5-4. Just as Windows Explorer provides a hierarchical view of the Windows file system, the Solution Explorer window in SQL Server Data Tools provides a hierarchical view of your solution and the projects it contains. Both Windows Explorer and the Solution Explorer window use a tree view to let us navigate the hierarchical structure. We can browse through our solution to find and open any item it contains." (Chapter 5)

I used the Solution Explorer Window to find and open the cube I was studying and its corresponding data source view and data source.

Solution Explorer Window

Created: 2018-11-23 18:38:44 Last updated: 2018-11-23 18:38:44

"Task List Window The Task List window can be opened from the View menu. This window provides a convenient place for us to make note of to-do items for this solution. We can assign priorities that are signified by the exclamation point (high priority), blank (normal priority), and down arrow (low priority). When items are completed, they can be checked off. Completed items are displayed with a strike-through." (Chapter 5)

This SQL Server Data Tools feature made me remember that Gmail also offers the Tasks feature to create and keep track of to-do items. I started to use this Tasks Gmail feature for work related tasks (using my account) and for personal or freelancing tasks (using my account).

Created: 2018-12-11 17:05:27 Last updated: 2018-12-11 17:05:27

"The SQL Server Management Studio is found on the Start menu under Microsoft SQL Server 2012, as shown in Figure 5-22.
SQL Server Management Studio on the Start Menu
Figure 5-22 SQL Server Management Studio on the Start menu" (Chapter 5)

I found the SQL Server Management Studio on my Windows 7 Ultimate environment. I completed successfully the installation of Microsoft SQL Server 2012 and I am ready to use it!

SQL Server Management Studio on the Start Menu

Created: 2019-01-22 13:57:25 Last updated: 2019-01-22 13:57:25

"Business intelligence design should never be done in a vacuum. As we discussed in Chapter 1, the goal of business intelligence is to provide the tools for effective decision making. Therefore, any business intelligence design must start with the decision makers themselves. What foundation and feedback information do they need? How do they need that information sliced and diced for proper analysis? To create truly effective business intelligence, these questions need to be answered by the decision makers themselves.

There are two important reasons to have the decision makers involved in this design process. First, the decision makers are the ones in the trenches. They know the choices that are made each day in the organization’s operation. They also have a pretty good idea of what information can aid them in making those choices.

Second, the decision makers are the ones who ultimately determine the success or failure of a project. They do this through their willingness or unwillingness to use the resulting business intelligence tools. Your tool may produce dynamite information, but, ultimately, it is a failure if no one uses that information to produce more effective decisions.

Involving decision makers in the design of the data mart structures distributes the perceived ownership of the project. Most people who get their brainstorming ideas on the whiteboard during a design meeting, or who are allowed to submit a design suggestion via e-mail, feel some sense of having contributed to the project. They feel a small piece of ownership in the project. Just as the owner of a single share in a billion-dollar corporation cares whether the company’s stock goes up or down, the person who feels ownership in a project, no matter how small their actual contribution might be, cares about the success or failure of that project. A decision maker who has taken a small piece of ownership in our business intelligence project is far more likely to use the resulting tool and, if appropriate, to push for others to make use of it as well." (Chapter 6)

I am building an OLAP cube for a client and I am creating measures and dimensions with him so that he can participate in the design process. He is also suggesting measures and dimensions that he wants. We are designing the OLAP cube together with my client and this is important to make him participate rather than simply creating everything by myself. He is providing good feedback and ideas and this is positive for a design that adapts to his needs. After all, the OLAP cube is for him to use it, not for myself. For that reason, he needs to decide what he wants the cube to have or not to have.

Created: 2018-12-25 11:42:07 Last updated: 2018-12-25 11:42:07

"Dimensions and Hierarchies

As we learned in Chapter 3, dimensions are used to spread a measure into its constituent parts. Hierarchies are used to organize dimensions into various levels." (Chapter 6)

After reading requirements from the business owner about how he wanted to spread measures into their constituent parts for a data cube, I could easily identify the dimensions that I needed to build and realized that those dimensions formed hierarchies.

Created: 2019-01-05 02:45:23 Last updated: 2019-01-05 02:45:23

"In the snowflake schema, each level in the dimensional hierarchy has its own table. The dimension tables are linked together with foreign key relationships to form the hierarchy." (Chapter 6)

The snowflake schema is the design that I am using for the OLAP data cube that I am building. For example, for the location dimension, I am using a hierarchy where municipality, department and country are stored in three different tables linked together with foreign key relationships in such a way that the municipalities table has a foreign key that references a primary key value in the departments table. In the same way, the departments table has a foreign key that references a primary key value in the countries table.

Created: 2018-12-28 16:44:29 Last updated: 2018-12-28 16:44:29

"1. Prepare a list of the measures requested by the VP of production. This is shown in the Measure column of Figure 6-9.

2. Determine which fields in the OLTP data source supply the data for these measures. This is shown in the OLTP Fields column of Figure 6-9. The Reject Flag field tells us whether a product has been accepted or rejected. This can be used to determine the number of accepted and number of rejected products. The manufacturing system does not track the time spent in each individual production step. It only tracks the date and time at the start of manufacture and the date and time at the end of manufacture. Therefore, we need to put NOT AVAILABLE for these two items.
Requested measures
Figure 6-9 Requested measures for the Manufacturing data mart" (Chapter 6)

I created a similar table during the design process of an OLAP cube, where I specified in the "Measure" column not the name of the measure but the tables and columns where I was storing the value containing that measure in this format: [Table].[Column]. I also had a "Dimension" column to specify where in the database I could find data to build dimensions. In addition, I added a column to specify the "Name" for the measures and dimensions, which is what users browsing the cube would see because it is likely that in many cases users would prefer to see a more intuitive name for measures and dimensions and not the same name of the columns in the database that are used to retrieve that data.

Created: 2018-12-29 15:29:27 Last updated: 2019-06-22 04:56:35

"14.  Place each dimension into its own dimension table, as shown in Figure 6-15. Include a table name beginning with “Dim” and a primary key designation in each table.
Fact table
Figure 6-14 The Manufacturing data mart schema with measures in a fact table

Dimension tables
Figure 6-15 The Manufacturing data mart schema with dimension tables added" (Chapter 6)

I added "Fact" as the suffix of fact tables and "Dim" as the prefix of dimension tables. Now it is easy to differentiate fact and dimension tables in my data mart simply by reading the table names.

Created: 2018-12-29 20:01:44 Last updated: 2018-12-29 20:01:44

"Even though we identified several foreign key relationships among our tables, we are not going to create foreign key constraints in our data mart. Foreign key constraints put a strain on the database engine during a large data load. Large data loads are quite common in the data mart environment." (Chapter 6)

For the data mart that I created for an OLAP cube, my database design included several foreign key relationships for table connections and levels of hierarchies in the dimension tables. However, I did not create foreign key constraints to avoid putting a strain on the database engine in case the ETL process requires large data loads.

Created: 2019-01-04 03:42:22 Last updated: 2019-01-04 03:42:22

"Now that the Manufacturing data mart contains data, let’s go ahead and define an online analytical processing (OLAP) cube on top of the data mart relational database. In Chapter 6, we defined one OLAP cube at the same time we created the Sales data mart relational database. This time around, we approach things from a different direction. We already have the Manufacturing data mart relational database. What we need to do is define our OLAP cube on top of that existing database." (Chapter 9)

I defined an online analytical processing (OLAP) cube on top of a data mart relational database that contains 37 tables (10 fact tables and 27 dimension tables). I also built some hierarchies with the dimension tables. This design is likely to evolve with additional tables depending on my client's extra requirements.

Created: 2019-02-11 20:33:10 Last updated: 2019-02-11 20:33:10

"25. Right-click the Cubes folder in the Solution Explorer window, and select New Cube from the context menu. The Cube Wizard appears.

26. Click Next on the Welcome page. The Select Creation Method page appears.

27. Use existing tables should be selected by default. Click Next. The Select Measure Group Tables page appears.

28. The Max Min Manufacturing DM data source view that you just created should be selected. Check ManufacturingFact in the Measure group tables area. We initially leave the measures in the InventoryFact table out of the cube and add them in at a later time.
Figure 9-1 The Data Source View Design tab for the Max Min Manufacturing DM data source view
Figure 9-1 The Data Source View Design tab for the Max Min Manufacturing DM data source view

29. Click Next. The Select Measures page appears.

30. The wizard should have found and checked all of the measure fields in the ManufacturingFact table. The wizard also added a field called Manufacturing Fact Count, which counts the number of records. This does not provide us with any useful information in this data mart, so you should uncheck the Manufacturing Fact Count field.

31. Click Next. The Select New Dimensions page appears.

32. The wizard should have found all of the tables related to the ManufacturingFact table in the data source view. It should also have created hierarchies for the Dim Product and Dim Machine dimensions. Uncheck both ManufacturingFact entries.

33. Click Next. The Completing the Wizard page appears.

34. Enter Max Min Manufacturing DM for Cube Name, if it is not already there. Click Finish. The Cube Design tab appears. SQL Server Data Tools windows should appear similar to Figure 9-2.

35. Click the Save All button on the toolbar.

You will notice the wizard did not create our time dimension for us. We will manually create the time dimension later in this chapter.
Figure 9-2 SQL Server Data Tools window after the Cube Wizard completes
Figure 9-2 SQL Server Data Tools window after the Cube Wizard completes" (Chapter 9)

I used the Cube Wizard to start the process of creating a cube using existing tables from a view as the foundation or skeleton. This view contained all of the fact and dimension tables that I prepared in my design of the data mart that will store data moved from a transactional database to the database of the OLAP cube used for business intelligence purposes.

Created: 2019-02-25 21:06:40 Last updated: 2019-02-25 21:06:40