cancel
Showing results for 
Search instead for 
Did you mean: 

BPC and Drill to SQL Server Table

Eyal_Feiler
Participant
0 Kudos

Hi Experts,

I am trying to drill through to a SQL server table from BPC.

Does anyone know the address to insert in the URL field when adding the drill through?
As per the help, I created a query and then a DQY file (source.qty), located in the correct BPC server location.  Tried inserting the file path name as below as an example

SERVER NAME /PC_MS/DATA/Webfolders/DEV__2015/CONSOLIDATION/QUERYFILES/source.qty

In testing, the result was a failed attempt to open the location with a prefix of http://.

http:\\ SERVER NAME /PC_MS/DATA/Webfolders/DEV__2015/CONSOLIDATION/QUERYFILES/source.qty

What's the correct syntax to access the query?

Any ideas?

Has anyone used this feature regularly to link to other data?
Trying to create a simple query and then add the parameters e.g. time entity account etc.

Thanks

Eyal

Accepted Solutions (1)

Accepted Solutions (1)

Eyal_Feiler
Participant
0 Kudos

Ok Roberto - progress....

I refreshed the report, selected a  balance, and then drill through.

Received the message below.  Trying to identify the issue:  It seems that it can't find the database.  I double checked every value.

Any idea?

Eyal

,SystemName,JobName,UserID,Status,DateWritten,Message

3044744,SystemConfigComponent,DrillThrough::ReturnHTML,MERTREE\eyal_fe,0,2015-10-13 16:18:17.367,"

==============[System Error Tracing]==============

[System  Name] : SystemConfigComponent

[Message Type] : ErrorMessage

[Job Name]     : DrillThrough::ReturnHTML

[DateTime]     : 13/10/2015 16:18:17

[UserId]       : MERTREE\eyal_fe

[Exception]

    DetailMsg  : {System.NullReferenceException: Object variable or With block variable not set.

   at Microsoft.VisualBasic.CompilerServices.Symbols.Container..ctor(Object Instance)

   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)

   at OSoft.Services.Application.SystemConfig.DrillThrough.ReturnHTML()}

==============[System Error Tracing  End ]==============

"

3044743,SystemConfigComponent,DrillThrough::GetResultSet ,MERTREE\eyal_fe,0,2015-10-13 16:18:17.287,"

==============[System Error Tracing]==============

[System  Name] : SystemConfigComponent

[Message Type] : ErrorMessage

[Job Name]     : DrillThrough::GetResultSet

[DateTime]     : 13/10/2015 16:18:17

[UserId]       : MERTREE\eyal_fe

[Exception]

    DetailMsg  : {System.NullReferenceException: Object variable or With block variable not set.

   at Microsoft.VisualBasic.CompilerServices.Symbols.Container..ctor(Object Instance)

   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)

   at OSoft.Services.Application.SystemConfig.DrillThrough.GetResultSet(String sQueryString)}

==============[System Error Tracing  End ]==============

"

3044742,SystemConfigComponent,DrillThrough::ConnectDatabase ,MERTREE\eyal_fe,0,2015-10-13 16:18:17.070,"

==============[System Error Tracing]==============

[System  Name] : SystemConfigComponent

[Message Type] : ErrorMessage

[Job Name]     : DrillThrough::ConnectDatabase

[DateTime]     : 13/10/2015 16:18:16

[UserId]       : MERTREE\eyal_fe

[Exception]

    DetailMsg  : {System.Runtime.InteropServices.COMException (0x80004005): [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

   at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)

   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)

   at OSoft.Services.Application.SystemConfig.DrillThrough.ConnectDatabase(String sConnectString)}

==============[System Error Tracing  End ]==============

"

3044741,DM,EvServerDataMgr.cEvServerDataMgr.GetInfo,MERTREE\eyal_fe,14,2015-10-13 16:00:03.033,

==============[System Exception Tracing]==============

[System  Name] : DM

[Message Type] : 14

[Job Name]     : EvServerDataMgr.cEvServerDataMgr.GetInfo

[DateTime]     : 13/10/2015 16:00:03

[UserId]       : MERTREE\eyal_fe

[Exception]

    DetailMsg  : {}

==============[System Exception Tracing  End ]==============

3044740,OSoftDatabaseSYSADMINDM,DataSwitch::Connect,....,2,2015-10-13 16:00:02.963,"

==============[System Error Tracing]==============

[System  Name] : OSoftDatabaseSYSADMINDM

[Message Type] : Warning

[Job Name]     : DataSwitch::Connect

[DateTime]     : 13/10/2015 16:00:02

[UserId]       : ....

[Exception]

    DetailMsg  : {System.InvalidOperationException: Invalid attempt to read when no data is present.

   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.GetConnectionString(String strAppSetName)

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.Connect(String strAppSet, Boolean bExtraDB)

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.Connect(String strAppSet)}

==============[System Error Tracing  End ]==============

"

3044739,OSoftDatabaseSYSADMINDM,DataSwitch::Connect,....,2,2015-10-13 16:00:02.883,"

==============[System Error Tracing]==============

[System  Name] : OSoftDatabaseSYSADMINDM

[Message Type] : Warning

[Job Name]     : DataSwitch::Connect

[DateTime]     : 13/10/2015 16:00:02

[UserId]       : ....

[Exception]

    DetailMsg  : {System.InvalidOperationException: Invalid attempt to read when no data is present.

   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.GetConnectionString(String strAppSetName)

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.Connect(String strAppSet, Boolean bExtraDB)}

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Eyal

For troubleshooting, can you remove BPC out of the picture?

Double-click the dqy file directly from your local machine and what happens ? What error do you get?

try: Provider=SQLNCLI11;Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;

see https://www.connectionstrings.com/sql-server-2012/

Thanks,

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Try this syntax as well listed here: 1624320 - BPC MS: Drill Through Cannot connect Database or open result https://service.sap.com/sap/support/notes/1624320

Eyal_Feiler
Participant
0 Kudos

Hi John,
Thank you for the links and advice that helps a lot.

By clicking just on the file - I receive a blocked connection from Excel (which I enabled) and then a prompt for a data source. 

I'll work with a colleague to create the required ODBC setup - apparently this is missing on the client machine, and will reference the docs you sent.  Setting up for now on SQL 2008 R2 and then will need to setup on another location for SQL 2012.

In the meantime another 2 question:

I'd like to filter by 3 dimensions ACCOUNT TIME and ENTITY and will add these dimensions to the selection query as the help and Roberto guided.

This will work for presumably for matches TIME: 2015.NOV -> will match to 2015.NOV in the source table.

1) How do I account for differences between the BPC data and source? e.g. 2015.11 source vs. BPC 2015.NOV etc.?

I saw for the URL query the user is prompted to (define and then ) select the ID or property containing the source mapping.  But how does that work here - where this feature is not used?

If I create and display a property value of TIME in my report 2015.11 - will the drill through be able to match?

Thanks again.

Eyal

Answers (2)

Answers (2)

Eyal_Feiler
Participant
0 Kudos

Ok guys Thank you very much for your help!

It succeeded!!

I created a new dqy file which defined the parameters.

I added the the user id and password in the drill through table and now it works.

Also added filters by the dimensions so that drill through only brings the desired results.

So if the data differs between source and BPC we would need to create a source table that matches the BPC data e.g. TIME in source would have to be 2015.NOV in the local table so that BPC can ID?

Unless there's a way through the SQL to select property, I don't see any other way...

Thanks again.

Eyal

former_member186498
Active Contributor
0 Kudos

Hi Eyal,

good that now the query works!


Unless there's a way through the SQL to select property, I don't see any other way...

you can join the mbrtable of the related dimension to use the property.

Please don' forget to close the thread after solved.

Regards

     Roberto

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Eyal,

You can create a lookup table in the source database as well and then match the two values with an SQL JOIN.

HTH

John

former_member186498
Active Contributor
0 Kudos

Hi Eyal,

as per help if you have built a query you don't need to insert the url in the admin console just insert the query in the web admin -> editdrill through table giving a valid DB server userID.

Regards

     Roberto

Eyal_Feiler
Participant
0 Kudos

Hi Roberto,

Thank you.

I have a drill through defined on a dimension ACCOUNT, with a qty file saved in the location specified in help  pointing to the server location etc.

I built the example in BPC MS 7.5.  Created 4 accounts with the drillkey ACCT_SBO.

Now when sitting on the account dimension, or value  or another dimension I run the Drill-Through and it does not work.  I receive a message - there is no drill through information.

2) To confirm, you're saying that the drill-through customization at the application level is not relevant here, just the drill-through table?

3) If I understood help, the one drill is only available on the dimension.

The business case I am trying to show is that I can filter an SQL Table on 3 dimensions by ACCOUNT by TIME and by ENTITY.  The result will return based on these 3 selections.

Is this possible?

Thanks again.

Eyal

former_member186498
Active Contributor
0 Kudos

Hi Eyal,

if the query isin the Application->queryfiles folder and you have tested the select in SSMS then please check the connection parameters probably some parameter is wrong or incomplete,e.g. for db server you have to put "full domain\instance".

2) yes, the url is if you want to start an url page with some parameters, e.g. could be useful connecting it with "live reporting" but this is very basic reporting that's why it's unused.

3) in the where conditions you can put the 3 dimensions and also other dimensions conditions.

Regards

     Roberto

Eyal_Feiler
Participant
0 Kudos

Hi Roberto,

Here is the content of the dqy file  We kept it simple to confirm the connection first.

It is located in on the server where the other dqy files are per the help:

D:\PC_MS\DATA\Webfolders\DEV_MER_2015\CONSOLIDATION\QUERYFILES

Thank you for answering 2 & 3.

XLODBC

1

DRIVER=SQL Server;SERVER=(local);UID=;APP=Microsoft Office 2003;WSID=DEWDFIAS084;DATABASE=SAP_IFRS;Trusted_Connection=Yes

SELECT [SourceData].[ACCOUNT],[SourceData].[DOC_DATE],[SourceData].[DOC_DESC] FROM [dbo].[SourceData]

ACCOUNT DOC_DATE Entry_Description DOC_DESC

former_member186498
Active Contributor
0 Kudos

Hi Eyal,


XLODBC

1

DRIVER=SQL Server;SERVER=(local);UID=;APP=Microsoft Office 2003;WSID=DEWDFIAS084;DATABASE=SAP_IFRS;Trusted_Connection=Yes

SELECT [SourceData].[ACCOUNT],[SourceData].[DOC_DATE],[SourceData].[DOC_DESC] FROM [dbo].[SourceData]

ACCOUNT DOC_DATE Entry_Description DOC_DESC

XLODBC

1

DRIVER=SQL Server;SERVER=<db full domain name server\instance>;UID=<a valid userID for DB server access>;APP=Microsoft Office 2003;WSID=DEWDFIAS084;DATABASE=SAP_IFRS;Trusted_Connection=Yes

SELECT [SourceData].[ACCOUNT],[SourceData].[DOC_DATE],[SourceData].[DOC_DESC] FROM [dbo].[SourceData]

<you can insert here where conditions to filter for time, entity,...>

ACCOUNT DOC_DATE Entry_Description DOC_DESC

verify that you dqy file is in \\<appserver>\Osoft\Webfolders\<appset>\<application>\QUERYFILES

Regards

     Roberto

Eyal_Feiler
Participant
0 Kudos

Hi Roberto,

Again thanks.  Found some items to fix in the file.

Still it's not working. 

Is there a possibility we could have a quick call?

I've spent 2 days on this for a client that will stop a new project unless I can successfully demonstrate this feature.

Here is what I changed.

Also will add a screenshot of the error.

Eyal

Here is the file (replaced password with PWX).

Updated to open the Office 2010. 

WSID - understood that's the server workstation.

updated the database too.

Still I receive the same message when I select from the BPC for Excel

XLODBC

1

DRIVER=SQL Server;SERVER-BPC-DEV;UID=PWX;APP=Microsoft Office 2010;WSID=BPC-DEV;DATABASE=DEV_MER_2015;Trusted_Connection=Yes

SELECT [SourceData].[ACCOUNT],[SourceData].[DOC_DATE],[SourceData].[DOC_DESC] FROM [dbo].[SourceData]

ACCOUNT DOC_DATE  DOC_DESC DOC_dATE

former_member186498
Active Contributor
0 Kudos

Hi Eyal,

XLODBC

1

DRIVER=SQL Server;SERVER-BPC-DEV<you need to insert also "\instance name of your SQL" see previous msg>;UID=<not the psw but a userID that have access to SQL server>;APP=Microsoft Office 2010;WSID=BPC-DEV;DATABASE=<you appset (DB)>

....

try first without ";Trusted_Connection=Yes"

sourceData should be the name of your custom table.

Regards

     Roberto

Eyal_Feiler
Participant
0 Kudos

Hi Roberto,

Created another user to access - test.

I added the domain based on the computer name.

Still no luck.  Result:  "There is no drill through information"

Please see below.

Ideas? 
is there a log I can view to see what's not working.

Again this is for ms 7.5
Eyal

XLODBC

1

DRIVER=SQL Server;SERVER=MERTREE.MER.CO.IL\BPC-DEV;UID=test;APP=Microsoft Office 2010;WSID=BPC-DEV;DATABASE=DEV_MER_2015;Trusted_Connection=Yes

SELECT [SourceData].[ACCOUNT],[SourceData].[DOC_DATE],[SourceData].[DOC_DESC] FROM [dbo].[SourceData]

ACCOUNT DOC_DATE  DOC_DESC DOC_dATE