RE: Using PowerBi to retrieve data
Hi Anders, (I am sorry for late response.)
I was using an SQL technics described here: https://dba.stackexchange.com/ In fact I created Role (i.e. CRMreporting) and revoked access to all database ojects for its members (check this:https://dba.stackexchange.com/). This way each member of this role cannot see anything, unless granted explicitly.
Then I created set of views for Each UserGroup in SuperOffice i.e."Marketing_companies", "Sales_companies" etc.
In order to allow "DOMAIN\user" to access view "Marketing_companies" you execute this:
GRANT SELECT ON [CRM7].[Marketing_companies] to [DOMAIN\user] -- GRANT access of user to view
if needed, you can block it:
DENY SELECT ON [CRM7].[Marketing_companies] to [DOMAIN\user] -- DENY access of user to view
Using DOMAIN\user - simplifies PowerBI connection to data source and further managing permisions.
Below are sample stored procedures adopted to automate above. They can be used to check access, grant(with login creatoin) and revoke access to users to particular views:
CREATE PROCEDURE [dbo].[sp_Reporting_views_access_check] @userName as varchar(50)
AS
BEGIN
-- First parameter is the AD account i.e. DOMAIN\user - include it in apostrophes!
-- exec sp_Reporrting_views_access_check 'DOMAIN\user'
SET NOCOUNT ON
Declare @defaultDataBaseName as varchar(50);
Declare @CheckUserAccessLevelScript as varchar(max);
set @defaultDataBaseName = 'SuperOffice';
set @CheckUserAccessLevelScript ='
USE {dataBaseName}
EXECUTE AS USER = N''{userName}'';
SELECT
s.name as SchemaName,
o.name as ObjectName,
p.[permission_name]
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name) +
N''.'' + QUOTENAME(o.name), N''OBJECT'') AS p
WHERE o.[type] IN (N''U'', N''V'') AND p.subentity_name = N'''';
REVERT;
'
/*Print what access User was granted*/
set @CheckUserAccessLevelScript =Replace(Replace(@CheckUserAccessLevelScript, '{userName}', @userName), '{dataBaseName}', @defaultDataBaseName)
Execute(@CheckUserAccessLevelScript)
END
CREATE PROCEDURE [dbo].[sp_Reporting_views_access_revoke] @userName as varchar(50), @level as varchar(50)
AS
BEGIN
-- First parameter is the AD account i.e. DOMAIN\user - include it in apostrophes!
-- Second paremeter is General User group from SuperOffice i.e. Marketing, Sales etc.
-- exec sp_BI_views_access_revoke 'DOMAIN\user', Marketing -- this will automatically block access to Marketing_companies for DOMAIN\user
SET NOCOUNT ON
Declare @defaultDataBaseName as varchar(50);
Declare @LoginCreationScript as varchar(max);
Declare @UserCreationScript as varchar(max);
Declare @TempUserCreationScript as varchar(max);
Declare @DenyUserAccessToViewsScript as varchar(max);
Declare @CheckUserAccessLevelScript as varchar(max);
set @defaultDataBaseName = 'SuperOffice';
set @LoginCreationScript ='CREATE LOGIN [{userName}]
FROM WINDOWS
WITH DEFAULT_DATABASE ={dataBaseName}'
set @DenyUserAccessToViewsScript ='
USE {dataBaseName}
DENY SELECT ON [CRM7].[{level}_companies] to [{userName}];
'
set @CheckUserAccessLevelScript ='
USE {dataBaseName}
EXECUTE AS USER = N''{userName}'';
SELECT
s.name,
o.name,
p.[permission_name]
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name) +
N''.'' + QUOTENAME(o.name), N''OBJECT'') AS p
WHERE o.[type] IN (N''U'', N''V'') AND p.subentity_name = N'''';
REVERT;
'
/*DENY SELECT to view for User group*/
set @DenyUserAccessToViewsScript =Replace(@DenyUserAccessToViewsScript, '{level}', @level)
set @DenyUserAccessToViewsScript =Replace(@DenyUserAccessToViewsScript, '{userName}', @userName)
set @DenyUserAccessToViewsScript =Replace(@DenyUserAccessToViewsScript, '{dataBaseName}', @defaultDataBaseName)
Execute(@DenyUserAccessToViewsScript)
/*Print what access User was granted*/
set @CheckUserAccessLevelScript =Replace(Replace(@CheckUserAccessLevelScript, '{userName}', @userName), '{dataBaseName}', @defaultDataBaseName)
Execute(@CheckUserAccessLevelScript)
END
Sample View for Marketing:
CREATE VIEW [CRM7].[Marketing_companies]
AS
select DISTINCT
CRM7.USERGROUP.name as UserGroup,
CRM7.ASSOCIATE.name as OurContact,
CRM7.ROLE.name as RoleOurContact,
CRM7.PERSON.firstname+' '+CRM7.PERSON.lastname as OurContactFullName,
CRM7.CONTACT.registered,
REGBY.name as RegisteredBy,
CRM7.CONTACT.updated,
UPDBY.name as UpdatedBy,
CRM7.COUNTRY.name as CompanyCountry,
CRM7.CONTACT.contact_id,
CRM7.CONTACT.name as CompanyName,
CRM7.CONTACT.number1 as CompanyCode,
CRM7.CONTACT.number2 as CompanyNumber,
SUBSTRING(CRM7.CATEGORY.name,5,CHARINDEX(char(59),CRM7.CATEGORY.name)-6) as CompanyCategory,
SUBSTRING(CRM7.BUSINESS.name,5,CHARINDEX(char(59),CRM7.BUSINESS.name)-6) as CompanyBusiness,
SUBSTRING(CRM7.CONTINT.name,5,CHARINDEX(char(59),CRM7.CONTINT.name)-6) as CompanyInterests,
CompanyAddressTyp = CASE WHEN CompanyAddress.atype_idx = 1 THEN 'CompanyStreetAddress' WHEN CompanyAddress.atype_idx = 2 THEN 'CompanyPostalAddress' ELSE 'Other' END,
Replace(CompanyAddress.zipcode+', '+CompanyAddress.county+', '+CompanyAddress.city+', '+CompanyAddress.address1+' '+CompanyAddress.address2+' '+CompanyAddress.address3+', '+CRM7.Country.name,'find','replace') as ContactAddress,
CRM7.EMAIL.email_address as CompanyEmail
from CRM7.CONTACT LEFT JOIN
CRM7.ASSOCIATE ON CRM7.ASSOCIATE.associate_id = CRM7.CONTACT.associate_id LEFT JOIN
CRM7.ASSOCIATE AS REGBY ON REGBY.associate_id = CRM7.CONTACT.registered_associate_id LEFT JOIN
CRM7.ASSOCIATE AS UPDBY ON UPDBY.associate_id = CRM7.CONTACT.updated_associate_id LEFT JOIN
CRM7.USERROLELINK ON CRM7.ASSOCIATE.associate_id = CRM7.USERROLELINK.associate_id INNER JOIN
CRM7.ROLE ON CRM7.USERROLELINK.role_id = CRM7.ROLE.Role_id LEFT JOIN
CRM7.PERSON on CRM7.ASSOCIATE.person_id = CRM7.PERSON.person_id LEFT JOIN
CRM7.COUNTRY ON CRM7.COUNTRY.country_id = CRM7.CONTACT.country_id LEFT JOIN
CRM7.CATEGORY on CRM7.CATEGORY.Category_id = CRM7.CONTACT.category_idx LEFT JOIN
CRM7.BUSINESS on CRM7.BUSINESS.business_id = CRM7.CONTACT.business_idx LEFT JOIN
CRM7.USERGROUPLINK ON CRM7.USERGROUPLINK.assoc_id = CRM7.CONTACT.associate_id LEFT JOIN
CRM7.USERGROUP ON CRM7.USERGROUP.UserGroup_id = CRM7.USERGROUPLINK.UserGroup_id LEFT JOIN
CRM7.CONTACTINTEREST on CRM7.CONTACTINTEREST.contact_id= CRM7.CONTACT.contact_id LEFT JOIN
CRM7.CONTINT on CRM7.CONTACTINTEREST.cinterest_idx = CRM7.CONTINT.ContInt_id LEFT JOIN
CRM7.ADDRESS AS CompanyAddress on CompanyAddress.owner_id = CRM7.CONTACT.contact_id LEFT JOIN
CRM7.EMAIL on CRM7.EMAIL.contact_id = CRM7.CONTACT.contact_id LEFT JOIN
CRM7.UDCONTACTSMALL on CRM7.CONTACT.userdef_id = CRM7.UDCONTACTSMALL.udcontactSmall_id
where CRM7.USERGROUP.name like 'Marketing%'
GO
Above view has companies details, associate created and updated them, company interests, plus address that can be used as Location for Map location of companies.
Further you can develop idea above to link AD user groups to SuperOffice User Groups, or use on reccord access level, or make specific setup depencing on SuperOffice configuratoin.
I hope above helps you or anyone who needs to analyze SuperOffice data via MS Power BI.
One limitation is - you need to have on premise installation, but gives really good analytics capabilities (especially linking this with Sale, Project, Appointments, People).
PowerBI and proper views can be really helpful when dealing with User defined fields.
Please let me know, if you have further questions or need assistance.
Boyan