In some cases it might be necessary to access SQL Tables that contain data synced by EPM Pulse DataMart.
EPM Pulse DataMart SQL Tables names change after each data synchronization. As you are trying to access SQL Tables via Admin account, all prefix-related schema names are also shown. To avoid any difficulties related to tables names changes you can use the internal DBReader account, which is created by EPM Pulse Datamart during the tenant creation. In this case schema names will not be shown, and you can use general SQL queries - they will be working after each sync.
To find the account name perfom the following:
- Open SQL Management Studio
- Navigate to Databases -> Tenant _tenandID -> Security -> Users
- Find the account that is displayed in the following format – epmpulse_db_reader_tenantUID_UID
To find the password to the epm_db_reader account follow these steps:
- In SQL Management navigate to Databases -> EPM Pulse Config -> Tables
- Right click on dbo.ReportingConnection -> Select Top 1000 Rows
- Password can be found in the DbPassword column
Also, you can get DBReader account password for your EPM Pulse on-premises tenant using this query:
1 USE [EPMPulse_Config]
2 GO
3
4 SELECT
5 [Tenant].[Name] AS 'Tenant Name',
6 [IntegrationPoint].[Name] AS 'Connection Name',
7 ('Tenant_' +
8 REPLACE (([ReportingConnection].[TenantId]),'-','')
9 ) AS 'Tenant Database Name',
10 ('epmpulse_db_reader_' +
11 REPLACE (([ReportingConnection].[TenantId]),'-','') + '_' +
12 REPLACE (([IntegrationPoint].[Id]),'-','')
13 ) AS 'Reporting Account Name',
14 [ReportingConnection].[DbPassword] AS 'Reporting Account Password'
15
16 FROM [ReportingConnection]
17 INNER JOIN [Tenant] ON [ReportingConnection].[TenantId] = [Tenant].[Id]
18 INNER JOIN [IntegrationPoint] ON [ReportingConnection].[TenantId] = [IntegrationPoint].[TenantId]
19
20 WHERE [DbPassword] IS NOT NULL