Importing Tabular model to SSDT

Jun 29, 2012 at 7:50 PM

I have a question regarding bringing the model into SSDT.

In the read me it says: "...models created using the AMO2Tabular V2 library cannot be used in Microsoft SQL Server Data Tools (formerly known as BIDS)."

I would like to know if after the model is created on the server would I be able to create a new project and choose "Import from Server(Tabular)" to bring it into SSDT for additional modification?

 

 

 

Jul 10, 2012 at 1:08 AM

Knightt,

I tried to be as clear as possible on that subject in the readme and setup guides; my fault. So, I would say... let me try again.

A model created with SQL Server Data Tools -SSDT- includes a significant amount of detail about formatting and reporting in annotation fields, for columns and other objects. On the other hand, the AMO2Tabular library on purpose ignores those annotations, as internally the server does the same with annotations. The consequence of ignoring those annotations, however, will be that SSDT will crash on you, after the model is imported, when it tries to access those missing annotations.

Bottom line, any model created through AMO2Tabular cannot be opened with SSDT. You can use and query the model, manage it and many other operations; but all of them away from SSDT, you can use SQL Server Management Studio with no problems.

Jul 19, 2012 at 2:26 PM

I only got as far as the readme.txt when I noticed this and didn't get to the more detailed explanation in the setup and execute guide.

After further reading it's clear this is not the first time this concern has come up.

Thanks for explaining it again.

Aug 5, 2013 at 1:09 PM
How hard would it be to add those annotations? Is there anyting else missing to open in SSDT? I have added some attributes to the DSV that made the "Table Properties" dialog in SSDT work. But when SSDT is trying to show column properties it crashes (as mentioned above). It would be very nice to be able to open a created model in SSDT... Any hints about where in the object tree of a tabular model I can find those attributes?

Thank you "jpjofre" for some very nice example code!
Best Regards
Ulf
Aug 8, 2013 at 12:21 PM
I modified the code to add some missing annotations - still didn't work. But after removing the GUID in the RowNumber attribute ID it worked! "Import from Server(Tabular)" in SSDT works perfect for me now!
Sep 13, 2013 at 10:44 AM
Edited Sep 13, 2013 at 10:45 AM
Ulfen,
I also modified the code to remove GUID in the Row number attribute id. Basically modified the line below but still I am unable to import the model in SSDT. Any idea. Can you share the updated code.
string rowNumberColumnName = string.Format(CultureInfo.InvariantCulture, "RowNumber_{0}", Guid.NewGuid()); //  Making sure the RowNumber calculatedColumn has a unique name"
This is error i am getting
An error occurred while opening the model on the workspace database. Reason: The given key was not present in the dictionary.
Sep 13, 2013 at 11:00 AM
In table functions file I made the following changes (don't know if all are needed):

After MDX script is created:
cube.Annotations.Add("DefaultMeasure", "__No measures defined");
The RowNumber issue:
string rowNumberColumnName = "RowNumber";
and a few lines below I added:
tableDimension.Annotations.Add("IsQueryEditorUsed", "False");
tableDimension.Annotations.Add("QueryEditorSerialization", null);
tableDimension.Annotations.Add("TableWidgetSerialization", null);
And later on:
tableMeasureGroup.ErrorConfiguration = new AMO.ErrorConfiguration();
tableMeasureGroup.ErrorConfiguration.KeyNotFound = AMO.ErrorOption.IgnoreError;
tableMeasureGroup.ErrorConfiguration.KeyDuplicate = AMO.ErrorOption.ReportAndStop;
tableMeasureGroup.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
And:
defaultMeasureSource.DataSize = 8;
Basically I created a project in BIDS and compared the XML and tried to add everything missing when model was created using AMO2Tabular. There are changes in some of the other files as well similar to the ones listed above.
Sep 13, 2013 at 11:12 AM
Thanks for quick reply. Do you the updated library you can send across. I will compare the changes and accordingly modify it if required.

I changed the row number column name but it did not worked. So something else is also required.

Thank again.

Sent from my Windows Phone

Sep 13, 2013 at 12:06 PM
youranupama, I don't have your email address... Is it possible to attach files? Anyway, I checked my files and the only one besides TableFunctions.cs I have modified is GetDatabaseSchema.SQLOLEDB.cs where I added some extended properties to the tables in the DSV:
                foreach (DataRow row in tables.Rows)
                {
                    string tableName = row["TABLE_NAME"].ToString();
                    using (DataTable table = new DataTable(tableName))
                    {
                        //  Obtaining calculatedColumn information for current table
                        DataRow[] columnsInfo = columns.Select(string.Format(CultureInfo.InvariantCulture, "TABLE_NAME = '{0}'", table.TableName), "ORDINAL_POSITION ASC");
                        table.ExtendedProperties.Add("Locale", "");
                        table.ExtendedProperties.Add("IsLogical", "True");
                        table.ExtendedProperties.Add("FriendlyName", tableName);
                        table.ExtendedProperties.Add("DbSchemaName", "cube"); // this is specific to my database where my Views is in the cube schema
                        table.ExtendedProperties.Add("DbTableName", tableName);
                        table.ExtendedProperties.Add("TableType", "View");  // Specific to my database, I only use Views
                        table.ExtendedProperties.Add("Description", tableName);
                        table.ExtendedProperties.Add("QueryDefinition", "SELECT [cube].[" + tableName + "].* FROM [cube].[" + tableName + "]"); // Specific to my database

                        for (int i = 0; i < columnsInfo.Length; i++)
                        {
                            string columnName = columnsInfo[i]["COLUMN_NAME"].ToString();
                            using (DataColumn column = new DataColumn(columnName))
                            {
                                column.DataType = MapMsSqlType(columnsInfo[i]["DATA_TYPE"].ToString());
                                column.ExtendedProperties.Add("FriendlyName", columnName);
                                column.ExtendedProperties.Add("DbColumnName", columnName);
                                //  Obtaining Max string length... passing it as out parameter
                                int maxLength;
                                if ((column.DataType == typeof(string)) && int.TryParse(columnsInfo[i]["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
                                    column.MaxLength = maxLength;

                                column.AllowDBNull = (0 == string.Compare(columnsInfo[i]["IS_NULLABLE"].ToString(), "YES", StringComparison.OrdinalIgnoreCase)) ? true : false;
                                table.Columns.Add(column);
                            }
                        }
                        dsv.Tables.Add(table);
                    }
                }
Sep 13, 2013 at 1:08 PM
My email id is [email removed]

If you could send the updated project/changed file would be great help.

Thanks once again.