locked
Connecting Sql with Dashboard RRS feed

  • Question

  • Hello

    I am trying to connect dashboard with sql database for the same I need help from all of you , if some body know how to create the connection string or any other method.

    regards

    Puneet Sud
    Friday, July 31, 2009 7:20 AM

Answers

  • Hi,

      Make sure that your PPS application pools are runnning under the domain account. Also make sure that the database you are connecting to has necessary permissions. Also in web.config file of PPS monitoring server make sure that the BPM.ServerConnectionPerUser Property is set to true under webservice. For more detailed explanation please follow this link

    http://nickbarclay.blogspot.com/2007/11/pps-data-source-connection-problems.html


    HTH,
    Ram

    Friday, July 31, 2009 11:32 AM
  • Please let me know have you configured your datasource properly

    1) In the data source connection under standard connection you have to specify the server name.
    2)In the database option specify the database from the drop down.
    3) in the tables option what happens when you click on the drop down. Are you able to see the table names in the drop down.If not make sure that appropriate permissions are given to the database as well as to the tables.

    Please refer this link for more info

    http://office.microsoft.com/en-us/help/HA102408461033.aspx


    Regards,
    ram
    Monday, August 3, 2009 2:00 PM

All replies

  • hi,

     Correct me if i am wrong , you need to connect to SQL Database. Please follow these steps

    1)Right click on data sources and select new data source. Here you select Tabular list on the left pane and select SQL Server table.

    2)Name the data source and a data source will be created.

    3)Then in the editor pane enter the server name.
     4) In the database option select the database.
    5) In the table option select the table.

    HTH,
    Ram
    Friday, July 31, 2009 8:58 AM
  • Thanks for the reply but I am getting the following error.

    " The performancepoint server could not connect to the specified data source. Verfiy that either the current user or application pool user has Read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct"


    In the above error I have the editor rights and administrator rights on the server as well as in PPS. So I request you to pls reply as soon as possible.


    Regards

    Puneet Sud
    Friday, July 31, 2009 11:23 AM
  • Hi,

      Make sure that your PPS application pools are runnning under the domain account. Also make sure that the database you are connecting to has necessary permissions. Also in web.config file of PPS monitoring server make sure that the BPM.ServerConnectionPerUser Property is set to true under webservice. For more detailed explanation please follow this link

    http://nickbarclay.blogspot.com/2007/11/pps-data-source-connection-problems.html


    HTH,
    Ram

    Friday, July 31, 2009 11:32 AM
  • Yeah, depending on how you have the security setup default or PerUser, you need to make sure that either the Identity running the application pool has the proper access or that you have the access if you are using the PerUser connection.  Kind of depends on where the database is located also.  Review the posting that Ram posted for further troubleshooting.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Saturday, August 1, 2009 2:34 AM
  • Thanks for the help. I have connected the database in pps now I want to use this database, kindly guide me.

    Monday, August 3, 2009 10:21 AM
  • Hi,

     I am not sure whether you can create Analytic reports by connecting to the database. You can create filters and scorecards by using the data source. Might be some microsoft people will give you more light on it.


    Regards,
    Ram
    Monday, August 3, 2009 11:24 AM
  • Thanks for the reply, Actually I am new in PPS and doesn't know how to work on it. I have created dashboard, KPI, Scoreboard and reports and now I have contacted the Datasource. Now Pls guide me what else I can do it on Dashboard .

    I have contacted the dashboard with the database but not able to preview the data. I am getting the following error

     " Error in Connecting to Data Source. Please verify the connection details." kindly help.


    Regards

    Puneet Sud
    Monday, August 3, 2009 11:59 AM
  • Please could you give us more details like what are you trying to do by conecting to the database. Have you created filters,Scorecards etc with this data source. Please test the data source connetions once.
     Have you connected to the appropriate tables.


    Regards,
    Ram
    Monday, August 3, 2009 12:39 PM
  • No I have not created the filters or scoreboard from this data base. my connection is standard  connection in editor mode, I am not able to see the tables in this database. In case you want to talk to me regarding the same for any doubt pls call me 09811220404.
    Monday, August 3, 2009 1:13 PM
  • Please let me know have you configured your datasource properly

    1) In the data source connection under standard connection you have to specify the server name.
    2)In the database option specify the database from the drop down.
    3) in the tables option what happens when you click on the drop down. Are you able to see the table names in the drop down.If not make sure that appropriate permissions are given to the database as well as to the tables.

    Please refer this link for more info

    http://office.microsoft.com/en-us/help/HA102408461033.aspx


    Regards,
    ram
    Monday, August 3, 2009 2:00 PM
  • Hi All,

    I know this is pretty old post , but it would be great if someone can reply me. I created Scorecard using sql server datasource. Connection is created successfully but the KPI is returning only one row of data based on the value mentioned for Aggregation in data source,If i select "None" no data is getting displayed. Table is having 10 rows. I want to show KPI for all the 10 rows.

     


    Ranjani.R
    Monday, April 11, 2011 12:23 PM
  • Not sure if I am fully understanding the issue.  Might need to get a better idea of what the data looks like.  Do you have 10 rows of data that have their own values?  Like say the following for example:

    Country       Sales      SalesBudget

    U.S.A.         10000     15000

    U.K.            15000     20000

    Etc.

    You would simply want to create the data source to the SQL table or view.  Preview the data and make sure that the dimension and fact columns are all configured properly.  Create the KPI based off the fact columns. Setup the scorecard, place the KPI on the rows, and then drag over a dimension member to the right of the name of the KPI in the row.  This will put the list of items below the KPI and you will see a value for each row.


    Dan English's BI Blog
    Tuesday, April 12, 2011 12:00 AM
  • Thanks a lot. You really helped me. I reached half of the solution. My table structure is as follows

     

    Business Unit      ID     Actualprocessed RowsCount   Error   filename

    UK                     1           5                   5                0          abc

    UK                     2           6                   5                1          abx

    UK                     3           5                   4                0         xyz

    Uk                     4            4                   4                0         xyz

    Igloo                  5           5                     5               0        xya

     

    Actually in the above table based on the ID column i got all these rows in scorecard as follows

                  UK             UK

    IDKpi      Actual         Target

    1             5                5 ^ 0

    2             6                 5 ^ 1

    3

    4

    5

     

    But client is expecting to show all the columns in the kpi just like table . Is it feasible??

    In short, Entire SQL table is expected to be displayed along with the KPI's . Is it feasible

    Please help
    Ranjani.R

    Tuesday, April 12, 2011 6:04 AM
  • Apart from the Error column you should be able to use all columns. But for that you would need to treat every column as a Fact value.

    KPI Metrics are supposed to be Factual value (numeric).

    I suppose a well styled SSRS report would suffice to your requirements.


    http://dailyitsolutions.blogspot.com/
    Wednesday, April 13, 2011 8:41 AM
  •  

     

    Hi umair,

    Thanks for your reply. How to acheive it. Can you please explain me further. If i change all the columns as fact. the kpi is getting displayed as blank. More over i want the entire sql table to be displayed in scorecard.

     


    Ranjani.R
    Wednesday, April 13, 2011 9:29 AM
  • You really need to break out of this original thread and create your own.  This is a completely new issue that you are trying to resolve and should be treated as such so that you will be able to mark and answer and get this resolved.
    Dan English's BI Blog
    Wednesday, April 13, 2011 10:52 AM
  • Hi,

     

    I have posted in this url pls help

    http://social.technet.microsoft.com/Forums/en/sharepoint2010general/thread/1f6c80fb-9902-4a1f-a7b0-6084bbf86822


    Ranjani.R
    Wednesday, April 13, 2011 12:17 PM
  • Hi,

     

    Can Someone help me out. I am still facing this issue


    Ranjani.R
    Tuesday, April 19, 2011 10:54 AM