Database Reference
In-Depth Information
Getting ready
We will use a view in the
DWDataMart
database to provide an abstraction layer from the
database model. The view will hold friendly names for the columns and will be limited to
the data of interest to your report consumers.
In this example, we are providing a list of all the incidents with all the commonly used at-
tributes. To create the view, open SQL Server Management Studio and connect to the SQL
Server that hosts the
DWDataMart
database. Create a new view (select
New Query
, type
the code, and click on
Execute
) using the query in the
Accessing data using Microsoft
Excel Query Code
section of
Appendix
,
Useful Websites, Chapter Code, and Community
Resources
.
Next, we need to grant permissions for the
reportuser
database role to the newly cre-
ated view. This can be done by executing the following query:
USE DWDataMart
GO
GRANT SELECT ON v_Custom_r_AllIncidents TO reportuser
GO
Tip
You need to make sure that the users who access
DWDataMart
from Microsoft Excel are
members of the
reportuser
database role.
It is recommended that you create an Active Directory group and add this group as a login
to SQL Server. Add all the users that you want to grant access to the data to this Active
Directory group. Then, add the login as a user to the
DWDataMart
database and assign it
to the
reportuser
database role.
