Creating a Tabular Model with AMO - New Version SQL 2012
With the release of SQL Server 2012 we have included a complete set of database samples in the data warehousing field, to help you get started understanding almost all product features. In addition to all sample data, we delivered a developer's sample to programmatically create a Tabular model using the AMO API, in C#. This is a completely new sample; redesigned and written from scratch based on your input on the AMO2Tabular sample that was available during the early previews of the product. The sample is available at http://tabularamo2012.codeplex.com .
This new version of AMO2Tabular sample is a library of functions, with no graphical parts or UI screens. On top of the library, another sample was written to show how to use the library; both samples are available together. Because we were writing a completely new AMO2Tabular sample, not updating it, we made the decision to remove the first version of AMO2Tabular from Codeplex. We understand that this might have caused some problems to you but there were some technical inaccuracies that motivated us to remove the first version. If you have any specific feedback on this decision or immediate needs, please do not hesitate to contact us through this blog.
To give you a flavor of what to expect, from new AMO2Tabular library sample, we will use the library to recreate an existing tabular model: AdventureWorks Tabular Model 2012. Recreating a known model should help developers understand how to map AMO objects to the logical objects in a Tabular model. For example, a logical table in tabular modeling is represented by a dimension, a cube, a cube dimension, a measure group and a partition in the measure group (actually, it can be one or more partitions) in AMO. In this case you can see that a single logical object, a table, is mapped to many AMO objects. When trying and working with the sample, it is of capital importance that you realize that the sample is in the code, not in the execution of the code; executing the code is an aid to understand the process of creating a tabular model.
The library covers the major functionality in the process of creating a complex tabular model; this includes creating all major tabular objects: database, connection, table, relationship, perspective and role. In order to create the tabular objects, above, the sample will need you to create several AMO objects that are hidden to the user in Management Studio or SQL Server Data Tools (BIDS).
When you compile and run the sample that uses the library, it recreates the sample tabular model: AdventureWorks Tabular Model SQL Server 2012 ; the sample, itself, is meant to be run once or twice to understand the logical steps involved in creating a model. Later the focus should turn into understanding the source code and how AMO objects are used to generate the logical tabular objects.
In the mean time, while the new sample is developed and tested, we would like to have your input and opinion on what you value more about this AMO to Tabular sample… therefore, read what others have written and write your comments at the end of this blog.
Prerequisites, to compile and run the sample:
- Visual Studio 2010 with C#
- An instance of SQL Server 2012 Analysis Services deployed in tabular mode.
- An instance of SQL Server 2012 rdbms engine.
- An instance of ADVENTUREWORKSDW2012 DATA FILE deployed in the server
- An instance of ADVENTUREWORKS TABULAR MODEL SQL SERVER 2012 deployed in the server.
Tabular AMO 2012 is available here: http://tabularamo2012.codeplex.com .