Skip to content

Using views in the a tabular model

February 27, 2012

It is very handy to views as the table source definition in the the tabular model.

The biggest advantage for using views – the columns can be renamed in the underlying data source without breaking your tabular model. If that’s not enough to convince you use views, here are a number of smaller advantages to using views, especially if you are incrementally loading data using ProcessAdd:

  • It is easy to limit the number of rows imported into the workspace database during development – simply add a where clause to your view and change the view definition later.
  • You can safely redeploy over top of a model in production without worrying about retaining partitions as the partition definition has not changed.
  • Because the partition definition has not changed, the underlying partition will never be marked as unprocessed and will not ever be reprocessed if a Process Default command is issued on the partition.
  • The script out of the partition definition will always accurately reflect the query to be used for processing.

Interested in using a view? Read on for the implementation details.

Creating a table based on a view

To create a table that is based on a view, go to SSDT, open your tabular project, and then launch the Import Wizard. You have the choice of using the table preview mode or entering a SQL query to import a table. Both of these can use views. The SQL query method is self explanatory. Table Preview mode could use a picture.
Views are shown in the list with a special icon that’s rather difficult to describe. It looks like a table with a checkbox with dotted lines on top (not sure how this represents a view). I have circled the view in this picture:

image

Once the view is selected, you can import as usual. If you made a mistake at import time and want to update the source of a table in your model to refer to a view instead of a SQL table, this is easy to correct. Simply select the table in either the grid or diagram view, press F4 to view the property grid, then click to edit the Source Data property. You can change the table definition to refer to the view instead of the table. If you have multiple partitions, you should update the partitions in the Partition Manager as well. Redeploy your model after making this change.

Creating a partition based on a view

You can use the Partition Manager in SSDT or SSMS to create a partition based on a view, even if the other partitions in the table are not based on views. Again, this is done using a SQL query (in both SSMS or SSDT) or in preview mode in SSDT.

Processing data incrementally into a partition based on a view

Processing data incrementally into partitions that are based on views is also pretty straightforward. First, you update the definition of the view in SQL to reduce the number of rows returned to just the rows we want to load, taking care to ensure that there are no duplicate rows introduced when processing. We can then do a ProcessAdd in SSMS, PowerShell, AMO, or SSIS to load the data into the partition.

Using AMO to incrementally load data into partitions based on views

If you are using AMO, the C# program I previously showed to incrementally load data changes a little bit. The only thing that changes in the AMO is that you no longer need to specify a binding when calling ProcessAdd. This means you no longer need to care about this mysterious “Sandbox” data source view, which is nice. Your AMO looks a bit nicer, however you do need some more code overall to deal with updating the SQL view.

Here is the code:

//update the SQL Server view, ensure that the data is being added for the year 1867
OleDbConnection conn = new OleDbConnection("Provider=SQLNCLI11;Data Source=.\\PowerPivot;Initial Catalog=hans_rosling_project;Integrated Security=SSPI;Persist Security Info=false");
String sql = "ALTER VIEW [dbo].[vDemographicFacts] AS SELECT ID, Country, Year, LifeExpectancy, GDPPerCapita, ChildMortalityPerThousand, ChildrenPerWoman, CountryKey, ArmsImport, ArmsExport FROM  dbo.DemographicFacts WHERE (Year = 1867)";
OleDbCommand cmd = new OleDbCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();

//connect to Analysis Services
Server s = new Server();
s.Connect(".\\TABULAR");

//navigate to the partition we want to process
Database db = s.Databases.FindByName("hans_rosling_project");
Cube c = db.Cubes.FindByName("Model");
MeasureGroup mg = c.MeasureGroups.FindByName("Demographic Facts");
Partition p = mg.Partitions.FindByName("DemographicFacts");

//incrementally add data to the pre-existing partition
p.Process(ProcessType.ProcessAdd);
s.Disconnect();

Using SSIS  to incrementally load data into partitions based on views

The Integration Services package that I previously showed for loading data changes as well. Now, you need an Execute SQL task to run before the Analysis Services Processing Task. Also, just as in the AMO example above, you no longer need to configure the Analysis Services Processing Task to use a query binding when doing the Process Add.

The new procedure for adding data incrementally looks like this:

  1. Create a new Integration Services project.
  2. Create two connection managers in the Connection Manager pane – one to the Analysis Services database you want to process, and an OLE DB connection to the data source you want to modify.
  3. Drag an Execute SQL Task from the SSIS toolbox to the Control Flow pane of the SSIS designer.
  4. Configure the Execute SQL Task to use the OLE DB connection defined in step 2, and set the SQL to ALTER VIEW [dbo].[vDemographicFacts] AS SELECT ID, Country, Year, LifeExpectancy, GDPPerCapita, ChildMortalityPerThousand, ChildrenPerWoman, CountryKey, ArmsImport, ArmsExport FROM dbo.DemographicFacts WHERE (Year = 1867).
  5. Drag an Analysis Services Processing Task from the SSIS toolbox to the Control Flow pane of the SSIS designer. Connect the output of the SQL task to the Analysis Services task.
  6. Double-click to configure the Analysis Services Processing Task. Use the Analysis Services connection you defined in step 2, then as described in my previous post add the partition to the list of objects to be projects by drilling down through the cube and the measure group.
  7. Set the Processing Option to Process Add for the partition, then click OK.

You can now run this package and see the view updated and data loaded incrementally.

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: