#RELATIONSHIPS IN EXCEL DATA MODEL HOW TO#
#RELATIONSHIPS IN EXCEL DATA MODEL MANUAL#
#RELATIONSHIPS IN EXCEL DATA MODEL CODE#

If the model has no relationships or we need additional relationships to existing ones, we can create them using the management relationships window available for this purpose in the development environment of Visual Studio 2010 weвЂ™ll call this type manual relationships. In scenarios like the one just described is where we realize the importance that proper establishment of relations between tables in our model, in order to achieve optimal data analysis.ДЄ tabular model gets, if any, the relationships directly from the data source during execution of the data import wizard.The relationships obtained in this way will name automatic relationships.

#RELATIONSHIPS IN EXCEL DATA MODEL HOW TO#

However, the result obtained in the pivot table is very different, because all the cells show the same value: the sum total of SalesAmount field.This is due to the lack of a relationship between fields and DateKey OrderDateKey from FactInternetSales and DimDate tables respectively, which causes tabular model engine donвЂ™t know how to apply the measure В«Sum of SalesAmountВ». Since we have DimDate table as catalog dates, if we combine both tables in a SQL query using the appropriate date fields, weвЂ™ll get the expected numbers. The result we would need to obtain from this pivot table is the sum of SalesAmount field, that belongs to FactInternetSales table, grouped by sale years, that is, for the year in OrderDateKey field.

This action will result in opening a new spreadsheet showing the data model in a pivot table which weвЂ™ll place the measure В«Sum of SalesAmountВ», from FactInternetSales table, into the Values block, and the CalendarYear field from DimDate table in block В«Row LabelsВ». To analyze this model from Excel we will select the menu option SSDT В«Model | Analyze in ExcelВ», or we will click on the button on the toolbar that serves the same purpose. In the data model of this project we will include the tables in the database just created, adding also a measure that sums the values вЂ‹вЂ‹in column SalesAmount. Then weвЂ™ll create a tabular analysis project with SQL Server Data Tools (SSDT), the development environment for SQL Server 2012 based on Visual Studio 2010, known in earlier versions as Business Intelligence Development Studio. DimDateДЇor those readers familiar working with multidimensional analysis models, if we were to build an OLAP cube using this database, the FactInternetSales table represents the fact table because it contains the columns from which we obtain measures or analyze numerical results, while DimDate table represents a dimension table, used to filter the information by the dates in the model. SELECT * INTO DimDate FROM AdventureWorksDW2012. SELECT * INTO FactInternetSales FROM AdventureWorksDW2012.

#RELATIONSHIPS IN EXCEL DATA MODEL CODE#

The following code block shows the SQL statements necessary for its creation. LetвЂ™s say we have a database named AdvWksDW, created from some of the tables belonging to AdventureWorksDW2012 database, but without the corresponding relations between them. An essential feature in BISM (Business Intelligence Semantic Model) tabular data models, the new BI development paradigm built in SQL Server 2012, are the relationships between tables of a particular model, because without them it would be impossible perform a proper analysis of the information contained therein.

Relationships in excel data model