This example assumes you have a working tabular mode server and SQL Server Data Tools installed.

Let's review what was done in the previous steps for this first recipe. In steps 1 and 2 we created a new Tabular Model project and selected the option to import data. Then in steps 3 and 4 we selected the source data type of Excel and chose the file to import that included a list of the States in the USA. During step 5 we selected how the data source can be accessed via user security. In step 6, we reviewed the import process and started loading the data. In step 7 we were able to see that the data was successfully imported. Then you repeated the process to import the Famous Landmarks file. You now have two data tables loaded into your model that Tabular mode can use.

Tables contain the columns and rows of data that you are using to populate your Tabular data model. Data can be added from a variety of source systems. Examples include relational database structures such as tables or views, Analysis Services cubes, or text files. The tabular mode engine does not require you to transform data into special schema structures such as Star or Snowflake schemas. By leveraging the tabular model engine you can connect directly to data and transform it in the model designer if needed. This enables quicker model design and iterations without the need to invest in design and building data transformations and load processes. You can share the model with users to ensure the business need is being met. In addition to performing time calculations you will have to create and configure a table known to be a Date Table.

Using the model designer, you can view tables as either a diagram or a grid design view. The grid designer view shows the data in the table similar to viewing the data as an Excel file. This view is where you will create new DAX calculations and review the data.

When using the grid designer view, you see a data model of the table that displays the table and column names. Using this mode enables you to create hierarchies on a table and relationships between tables.

Every table will contain columns that store the data that make up your model. When you import data into your table, the designer inspects each column to automatically determine the type of data in the column and assign it a data type. In SQL Server 2016, the following data types are allowed:

In order to perform calculations in Tabular mode you must create measures using Data Analysis Expressions, also known as DAX. Adding measures to your data improves the usefulness of the information to your business users. For instance, adding a calculation to perform period over period growth to a tabular model would allow all users to leverage the same calculation and result. Otherwise, you may have users creating calculations outside of the model that use different logic.

As you build more complex models that contain many tables, relationships are the method to determine how data in one table relates to data in another table by linking columns. When adding a relationship to a Tabular Model, the column data must be the same. For example, if you create a relationship between an address table and a state table containing the master data of all 50 United States plus the District of Columbia, the columns used to link would have to match the data.

Once you know what columns and tables you want to link together you then must determine the type of relationship to establish. In the Tabular Model designer, you can create two types of relationships:

Continuing on our previous example will demonstrate the differences in the types of relationships. Every address can have one state and is a one-to-one relationship. However, every state can have multiple addresses and is an example of a one-to-many relationship.

When building relationships there are rules that are enforced in the model designer; first, each column can only be used in a single relationship. You cannot reuse a column that is already established in a relationship. The second rule is there can be only one active relationship between tables.

Much like how relationships define how tables are joined together and related, hierarchies define how data between columns is related. You add hierarchies to your model to make it easier for your business users to leverage the data. The classic example of a well formed hierarchy is a Calendar hierarchy built on a date table. The top of the calendar is the highest unit of measure and the bottom of the hierarchy is the lowest unit of measure. Therefore, you could have a Calendar hierarchy that is defined as Year | Quarter | Month | Day. Given this hierarchy users could navigate the model starting at Year and then drill down into the next lower level (Quarter), and then ultimately down to the day to get more detail based on their needs.