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:
- 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.
- 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.
- 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….
David,
Thank you for a very informative article. The one time I created a view, a few weeks later at month end on a Saturday, I got a call at home. Accounting was either trying to make a backup of our M2M database, or they were trying to restore the backup over our sand box company, and they received an error. I don’t remember what the error was now, this was a few years ago, but it turned out that it had to do with the view I created. I deleted the view and then everything was fine. Any idea why this type of thing might happen (unless I didn’t have the same view defined in our sand box company)?
Thanks,
Fred
The reason that Views would cause problems when trying to upgrade or restore; Createdbc tried to verify that everything in Data00 matched everything in DataXX (structure wise) and would throw errors when it didn’t. Therefore, you’d end up with headaches. 🙂 Not sure if views will still create problems today, but it used to create major problems. And, I’m pretty certain that it will still cause some headaches if they’re not dealt with properly. Just make certain that you fully understand what problems could present themselves if you’re working on the same SQL server for both testing and live environment. I’d suggest using a virtual machine for your testing! Makes life much easier and creates less headaches, as well.