Database Views - Using PowerPivot with Problem Databases Part 2

In the previous post I mentioned the use of views to simplify querying your database in general and PowerPivot in particular.

What are Database Views?

Views are virtual tables, which you can create with a SQL statement. Some of the advantages of using views are:

1. Simplified Data Access – The view hides the data structure from the user making reporting less complex. Also, calculations can be performed in the view for them making their lives easier as well.
2. Security – Through the use of views you can easily limit your users to certain types of data in your database. You can restrict them from looking at specific tables, or even certain fields or rows in a database.
3. Standardization/Maintainability – The table logic for views is stored in the view itself. Therefore, if a change in that logic is required or the database structure changes, which happens occasionally when you upgrade M2M, the view need only be updated once. Otherwise every report, spreadsheet, etc. based on the changed areas must be edited.

View Security

As I’ve mentioned in the past, you should use a report login for your reporting with read only access. The simplest way to do so is to add a SQL Login and assign it the db_datareader role for that database. There are clear directions with screenshots covering this here. I create report logins which I creatively call ReportsXX, where XX represents which Made2Manage database that user will primarily report from. I use separate accounts per M2M company so different companies cannot see others data.

When creating these views, you should always create them with the same owner or in the same schema. In SQL 2000 objects were owned by a database user. When you look at the M2M database you’ll notice that every object is owned by dbo (database owner). Objects created in SQL 2000 tend to default to the owner who created them and if an account is designated as a database owner the default is dbo. In my case, all objects owned by Reports01 would be Reports01.ObjectName and would need to be referred to with their fully qualified name by any user other than Reports01.

The concept of database schemas was introduced in SQL 2005. Schemas serve as “containers” for objects and are useful for enforcing security as well as organization in the database. In this case you would create a schema called Reports01 (or Reports or whatever you like), and since your report login has the db_datareader role, it can also read from your views.

How do I Create a View?

As always, please read my standard disclaimer before implementing any code from my site. Creating a view is very simple. Let’s take the following SQL statement.

SELECT ARM.fcinvoice AS InvoiceNo,
       ARM.fcustno AS CustNo,
       ARM.fbcompany AS CompanyName,
       ARM.fdgldate AS PostDate,
       ARM.fsalespn AS SalesPerson,
       Rtrim (ARI.fpartno) AS PartNo,
       ARI.ftotprice AS NetInvoiced,
       ARI.fprodcl AS ProdClass,
       INM.fgroup AS GroupCode
  FROM dbo.armast ARM
       INNER JOIN dbo.aritem ARI
          ON ARM.fcinvoice = ARI.fcinvoice
       INNER JOIN dbo.inmast INM
          ON INM.fpartno = ARI.fpartno AND INM.frev = ARI.frev
 WHERE ARM.fcstatus <> 'V'

Please keep in mind that this is a simplified invoicing script for demonstration purposes. The templates I use are much more comprehensive and bring in many other related tables such as FastForms extension tables, the Sales Person table which contains their full name, portions of the Item Master table for product details, etc.

Further, it’s important to note that if you run that script on your database it will retrieve every applicable record since you started using M2M. With my current company that’s more than a decade of records. With input from the business leaders, we agreed to limit these views to the prior two fiscal years. While you could hard code these dates in with a statement like this, you shouldn’t do so.

 AND ARM.fdgldate >= '1/1/2008'

Instead, construct your view so that it automatically selects the previous two years.

 AND ARM.fdgldate >=  DATEADD(yy, DATEDIFF(yy,0,getdate())-2, 0)  -- In this case 1/1/2008

This lessens the performance impact on your server. So, how do you create a view from this? You adapt the code as follows:

USE M2MDataXX -- Replace XX with your M2M Company 
Go
 
CREATE VIEW ReportsXX.vw_Invoices
AS
   SELECT ARM.fcinvoice AS InvoiceNo,
          ARM.fcustno AS CustNo,
          ARM.fbcompany AS CompanyName,
          ARM.fdgldate AS PostDate,
          YEAR (ARM.fdgldate) AS PostYear,
          MONTH (ARM.fdgldate) AS PostMonth,
          CONVERT (VARCHAR (3), ARM.fdgldate, 100) AS OrderMonthName,
          ARM.fsalespn AS SalesPerson,
          Rtrim (ARI.fpartno) AS PartNo,
          ARI.ftotprice AS NetInvoiced,
          ARI.fprodcl AS ProdClass,
          INM.fgroup AS GroupCode
     FROM dbo.armast ARM
          INNER JOIN dbo.aritem ARI
             ON ARM.fcinvoice = ARI.fcinvoice
          INNER JOIN dbo.inmast INM
             ON INM.fpartno = ARI.fpartno AND INM.frev = ARI.frev
    WHERE ARM.fcstatus <> 'V'
 AND ARM.fdgldate >= DATEADD(yy, DATEDIFF(yy,0,getdate())-2, 0) 
Go

The view can be queried as if it were a SQL table, like so:

select CompanyName, InvoiceNo, NetInvoiced, PartNo FROM ReportsXX.vw_Invoices
where NetInvoiced > 1000 -- 1000 dollars

When run against the educational database the data returns like so:

You’ll notice that I’ve aliased the field names and replaced them with English. In programs such as SQL Reporting Services the alias names will be automatically formatted for you so NetInvoiced becomes Net Invoiced. Another advantage is that the view hides the table joins from the user as well and it looks like one object.

Now, keep in mind that what I’ve just shown you most likely violates your Consona User Agreement. You’re not really allowed to make changes to their database. The reason I say “most likely” is because I cannot get a definitive answer on this point from Consona.

Why do I need to bother with all that owner/schema stuff?

As I said before you can use them for security purposes and restrict user access only to the views you create. However, the most important reason is to make migration easier. When you migrate your M2M version, one of the first steps is a check of database objects and the installer may error out and quit if it finds objects that aren’t supposed to be there. Therefore, I document every view I create, run a script to drop all of the objects in the specific owner/schema, and migrate. After migration, I script all of my views back in, and begin testing them.

I wrote this simple script to drop all views belonging to a user in SQL 2000. Make sure to replace “ReportsXX” with the name of your report login and “M2MDataXX” with your database name.

--  SQL 2000
USE M2MDataXX
Go
 
DECLARE @UserName   VARCHAR (25)
DECLARE @ViewName   NVARCHAR (25)
DECLARE @SQL   VARCHAR (1000)
SET @UserName = 'ReportsXX'
-------------------------- 
SELECT [name] ViewName
  INTO #temp
  FROM sysobjects
 WHERE type = 'V' AND user_name (uid) = @UserName
--------------------------
DECLARE
   DropViews CURSOR FOR
      SELECT ViewName FROM #temp
 
 
OPEN DropViews
 
FETCH NEXT FROM DropViews INTO @ViewName
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'Drop View ' + @UserName + '.' + @ViewName
   PRINT @sql
   --EXEC( @SQL)
   FETCH NEXT FROM DropViews INTO @ViewName
END
 
CLOSE DropViews
DEALLOCATE DropViews
 
DROP TABLE #temp

For SQL 2005 and after, simply replace the code between the horizontal lines with:

SELECT [name] ViewName
  INTO #temp
  FROM sys.objects
 WHERE type = 'V' AND schema_name (schema_id) = @UserName

Stay Tuned

In the near future, I will be posting more information about M-Data Analytics which has an entirely separate database, easily understood field names, and will have a multitude of views built in, etc. That doesn’t help you now, but I assure you, it is coming.

In the meantime you can simplify your life by using Database Views. In the next article in this series I’ll put it all together and show you the steps you’ll need to follow to successfully use technology like PowerPivot with problem databases.

Also, make sure to come back Monday when I share my entry in the Twelve Days of SQL Series. Not only will I share my choice for one of the best blog posts (and bloggers) this year, but I will reveal a picture of the tackiest Christmas Ornament you have ever seen. And before I show it, let me assure you that I really do own it, it really is on my Christmas Tree, and you’ll understand why my girlfriend suggested I need psychiatric help simply for owning it.

More to come….