The first step in any business intelligence project is to get a clear understanding of the client’s business. In other words, you need a good grasp of what the client hopes to achieve.

You’ll do this by having general discussions with the client about his or her needs.

For example, if you’re building an eCommerce dashboard for a client, you might ask questions like:

To understand this step, let’s use a simple example.

Suppose you have gotten a project in the field of online shops in which the customer tells you he wants to have a dashboard that displays specific information, such as:

after selecting a particular:

Here you can see that we were able to extract one of our client’s needs by asking questions and listening carefully.

Sometimes the answer given by the customer may not be clear, but it is important to ask more questions in order to fully understand their request.

So, We extract all the needs of the customer by talking to him.

Due to its high importance, this step must be done with great care and obsession. Try to devote more time to the project to this part.

You will understand my point better when you get involved in big projects. If you do this step correctly, you will better take the following steps and minimize setbacks.

As an SSAS Specialist, you should analyze his request in my head quickly.

This request does not require you to do anything special; just check two items.

You should determine:

So, in this section, based on customer requests and specifying their type, we can quickly identify in which category each one falls.

This is a trick I have learned based on experience, so make sure to pay close attention.

Let’s look at the same example again.

The customer asked us to see the final output (Total Amount of Sales) in the dashboard based on three specific conditions:

With this information, we can deduce that these three items become dimensions of our business.

Now we are looking for Facts in the customer’s conversations.

For this, we need to understand the type of computing needs he has.

In this example, the customer asks us to calculate the total amount of sales after applying these conditions (dimensions) and display it to him as a result.

Great! So this request tells us that this type of client’s needs is related to computing. So we will have a field named “Sales Amount” in the fact table.

We extract other customer needs of this kind similarly and consider them as a calculation field in the Fact table.

Each of these calculation fields in the Fact table is also called Measure.

The sum of these measures is also called the Measure Group.

For this reason, Measure Group and Fact table are equivalent. In the specialized tools of this field (including SSAS), these words are sometimes used interchangeably.

After all the dimensions and facts of the project have been determined, we now have to start implementing them practically and using specialized tools.

Since Nill2Bill focuses on SSAS, I will explain the implementation process based on this tool.

In the SSAS environment, the tool required to create dimensions is Dimension’s Design Environment.

Using this tool, you can quickly implement all the detected dimensions.

If you have done the previous two steps correctly, your work here will be straightforward.

You can quickly implement all dimensions by right-clicking on this tool, selecting “Create New Dimension,” and following a few steps.

You may have an important question. What data is going to be placed in these dimensions?

There are three situations here:

• That business already has the required data.

For example, consider the same Date table. After determining that we need a “Date” dimension, we examine the existing business data.

We notice that there is a table for the existing date.

Ok, the data from this table will be used to build the “Date” dimension.

• Another situation may occur where there is no existing table for the specific dimension we have identified.
• The third case is that we have the table with the data, but it is not fully responsive to the detected dimension.

For example, consider the same dimension “Date.”

The customer wants to decide on the sale of a particular product up to the hour level. That is, he wants to know how much the total sales of a specific product were in a specific hour. Now suppose that the fields in the “Date” table have stored information up to the daily level.

In these cases, it is sometimes necessary to create tables that meet the needs of the dimensions from scratch.

Sometimes the required information is not in one table, but it can be extracted from other tables in a scattered manner. In this case, it is necessary to quickly obtain this information by creating a Named Query and joining the existing tables.

You can now create your own dimension naming convention.

I use “Dim” before the name of the dimensions to name all the dimensions.

You can also use the same method to separate Dimensions from Facts.

So now, let’s make a summary.

We have three dimensions with the following names for the same example we had earlier, which we easily create in the SSAS environment based on the three challenges mentioned.

We can consider another dimension for the customer if he wants to engage in decision-making.

Also, if you are a beginner without knowledge about SSAS, read the SSAS Tutorial For Beginners article.

And if you have mastered all the basic concepts and want to start working with the environment practically, the SSAS Tutorial For Intermediate article is one of the golden opportunities.

Do not miss reading this valuable article.

In the SSAS environment, the tool required to create dimensions is Cube’s Design Environment.

Using this tool, you can quickly implement all the detected Facts.

The process is like this, while creating the cube, we also define the measures.

All the steps to create the practical parts of SSAS (creating dimension, fact, cube, named query, deploy, etc.) are available in the “SSAS Tutorial For Intermediate” article.

According to the previous example, one of the measures (one of the fields of the Fact table) that we recognized was SalesAmount.

So here we have a Fact table (named FactSales) where one of the fields of this table is SalesAmount.

The other fields in this table are each a Measure and perform a specific calculation. We talked about one of them in this example.

We will explain some of these fields that have a key next to them. Also, know that these fields are not “Measure.”

Specify the Hierarchy of Dimensions

So far, we have both the necessary dimensions and the facts.

The two dimensions of Date and Product will be the options he selects.

FactInternetSales calculates the value of the computational field inside it based on the selections in these two dimensions and presents the output to the manager.

Now suppose the nature of this need is that the customer should not have to select the Date generally.

What does this mean?

It means that the customer wants to be able to divide the Date into periods such as year, season, month, week, and day.

It means he wants to make complex decisions and have access to this capability to drill down.

And to be able to top-down and make decisions at the monthly level, for example, as well.

He wants to know how many sales they have had per month.

Or move to the lower layer and see weekly sales and so on.

This is called a hierarchy and needs to be implemented in many analytic projects.

If we notice such a need in the project or the customer asks for it, we figure that we should make some changes to that specific dimension and then prepare it for these operations.

As we said, this is called hierarchy in the Analysis Services world, and if we make the changes manually, it will be called “user-defined hierarchy.”

So I think with the explanations, you figured out the fundamental concept of hierarchy and the fact that hierarchy is a group of features connected together logically

Specify The Primary and Foreign Keys

First, you need to know that the Primary Keys are in the dimension tables.

Foreign keys are also in the Fact tables.

Now, using these keys, the connection between them is made.

Just as easily.

The Primary Keys are:

The same fields (Primary keys) are repeated as Foreign Keys in the Fact table.

These will be the part where the customer will pick their values.

Let’s not forget that dimensions were the same as tables to which we merely assigned concepts here in the Analysis Services world.

So we should say that we need three tables;

for example, the date table which includes all the dates.

For instance, the exact data must be precise, and all the times to the point of hours and minutes must be specified too.

We will even make decisions up to the layer of minutes if the project needs us to.

We will learn this later, which is called the hierarchy, and it should be implemented on that specific dimension if need be.

Communicating Between Dimensions and Fact

We need to make a logical connection between the dimensions and the fact.

We implement this connection based on a logical concept called the schema and the identified primary and foreign keys.

Provide Solutions for Data Deficiencies

Sometimes, we lack a specific table we need for our work.

This is where we need to make it ourselves.

For example, the date table is not as detailed as the customer needs it to be based on the data he has given us.

The good news is that these types of tables are common among most projects, and you can find and download them on the internet and customize them based on your project.

For instance, another table contains all the business products and has these data stored in the product table.

And another table will include the list of stores; suppose they are in the store table.

Sometimes we have to join data from other tables together.

Put the results in a new table (tailored to the project needs).

We do this using a concept called Named Query.