Using PowerBi to retrieve data

Hi

I am planning to use PowerBi to read out data from SO. But using my useraccount in SO, it fails with errorcode as shown in attached picture-

Do you need to allow for using API in any way?

My user account in SO is: jle@visinnovasjon.no

RE: Using PowerBi to retrieve data

Hi Jo!

In the onsite environment no, there is no restriction. You can use it directly without addtional permissions. 

In the online environment, yes. Any application attemtping to connect to tenant web service API's is considered an external application and must be registered to do so. 

The Create Apps section contains all of the information you will need to know about the process. The best place to start is register as an online developer, then you will received an email with links to register your application.

Hope this helps get you started.

By: Tony Yates 27 Oct 2020

RE: Using PowerBi to retrieve data

Onsite can talk to PowerBI directly.

Point it at https://crm.yourserver.com/SuperOffice/api/v1/archive

Choose BASIC authentication and enter your superoffice login and password.

PowerBI will download the archives list and column metadata and eventually give you a preview

Choose a suitable archive and enjoy.

Not all the archives work directly, but most should be workable.

By: Christian Mogensen 27 Oct 2020

RE: Using PowerBi to retrieve data

Hi

Thanks for your answer. I think this explain why i`m not able to connect with PoweBI by default on our online enviroment.

However, is there any problem (in generall) to register powerBI as a app ? and/or, has not anyone done that before?

 

By: Jo E.L. Lerheim 27 Oct 2020

RE: Using PowerBi to retrieve data

Hi Jo,

I did plenty with MS PowerBI and SuperOffice on premise. Some options to utilize Power BI:

>> (Quick, easy and)* Inexpensive workaround is using MS PowerBI and DATABASE MIRRORING SERVICE  **

>> You can als Use DATABRIDGE to export periodically data to supported formats.

>> Further you can export data from Selections to Excel and then link to Power BI.

On Premise: To handle access, I prepared a set of views to manage access to data, without exposing tables directly. I started preparing a short guide, but need more time to complete it.

If you need help/advise ... will be happy to assist.

Boyan
p.s. * (Quick, easy and) ... will require some codding, as proposed by Tony.

** Check with your SuperOffice account manager what is not mirrored: Tables containing sensitive data are not mirrored.

By: Boyan Yordanov 27 Oct 2020

RE: Using PowerBi to retrieve data

Hi,

@Boyan

Would it be possible for you to share the views you are talking about? Or when and where will you publish your write up?

To handle access, I prepared a set of views to manage access to data, without exposing tables directly. I started preparing a short guide ... but need more time to complete it.

By: Anders Larsson 13 Nov 2020

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

By: Boyan Yordanov 10 Dec 2020