none
Published Database fields in the Reporting Database RRS feed

  • Question

  • For the data that is within the Published database, but not the Reporting database, is there a way to have that included within the Reporting database?  We have some sql reports that rely on the data within the Published database, but we aren't supposed to access the Published database.  The requests we get for reports are pretty open ended, so I'm not sure what specific fields we would need.  One, for example, monitors predecessors and succeccors, which only seems to be in the Published db.  There are other things too (like notes).  Thanks!

    Tuesday, August 13, 2013 3:50 PM

All replies

  • Stephen, the only solution i see is to create custom views or SP's to pick data from Published to Reporting database. In my case, i would use the reporting DB. Actually, my queries Always include those databases and i never had a problem with that.
    Tuesday, August 13, 2013 5:32 PM
  • I was hoping we wouldn't have to create custom views so that we could avoid clutter, but it would certainly work.  As for picking data from the Published databases, I was told that we are not even allowed to look into the database, otherwise I would agree and just pull from the Published db.  Thanks.

    Tuesday, August 13, 2013 7:17 PM
  • It's near to impossible to report without accessing other Project Server databases. I've come across many times where I need to access Published or Draft dbs, though they are not recommended to access directly.

    The proper way I see to access data from these dbs is to create Replication tables either in new db or you can create in Reporting DB (which I guess is allowed :)) and access those in your queries, you can ask you DB admin to create replication.

    HTH


    IN
    Thanks, Kashif

    Thursday, August 15, 2013 9:22 AM
  • It's near to impossible to report without accessing other Project Server databases. I've come across many times where I need to access Published or Draft dbs, though they are not recommended to access directly.

    The proper way I see to access data from these dbs is to create Replication tables either in new db or you can create in Reporting DB (which I guess is allowed :)) and access those in your queries, you can ask you DB admin to create replication.

    HTH



    Thanks, Kashif


    Kashif, the Replication Table idea sounds like what we are trying to do.  I'm not familiar with how that would work: could you explain the process please?  We would probably try to have the tables in the Reporting db, but I'm not sure how that will work.  Would we be creating the data in the Reporting db (i.e. sort of saving the data while bypassing the Published db), or would we be copying the data from the Published db into the Reporting db?  I'm guessing the former, because the latter still has the issue of accessing the Published db.  Thanks for all of your help Kashif :)
    Thursday, August 15, 2013 1:58 PM
  • You can google how sql replication works or you can ask you db admin. I haven't used this approach, doing this would be hell of a job for me, as every other reports require access to Published db. I solemnly declare that I do query (SELECT only!) Published db :)

    The latter option would be copying the db (my bad I thought replication can be done partially on select tables but I guess it works on the whole db). You'll be creating new DB and replicating data into it from Published and then querying your new db (sounds really silly to me :S).

    Hope this helps a bit!


    IN
    Thanks, Kashif

    Thursday, August 15, 2013 8:42 PM
  • Kashif,

    Would you care to elaborate on what you need to retrieve from the draft and published databases? I've not seen this same need so I'm curious.

    Thanks!

    --Treb

    Treb Gatte | @tgatte |http://AboutMSProject.com

    Friday, August 16, 2013 6:10 PM
    Moderator
  • Treb,

    There are many, for instance Groups and Users are not in Draft they are in Published.

    Hope this lessen your curiosity :)


    IN
    Thanks, Kashif

    Saturday, August 17, 2013 2:31 PM
  • Kashif,

    I occasionally have run queries over Pub to figure out who is in what group but I don't see that as a common activity.

    The only time I've ever really had to crack it open was for state of the timesheet reporting. So, just curious if you had uncovered other scenarios.

    --Treb

    Treb Gatte | @tgatte |http://AboutMSProject.com

    Thursday, August 22, 2013 1:01 AM
    Moderator
  • I also see no problem in including the published database table. If you do it directly or use a view in the reporting database for that (what is officially allowed) does not matter.

    If you want to make sure that there is no interference of you query you could use the nolock option in you select statement.

    As I understand it the reason for using the Reporting DB only is simply that this database is documented and Microsoft will not make changes to these tables so your reporting will not break at least within one version.

    Regards

    Christoph


    Christoph Muelder | Senior Consultant, MCSE, MCT | SOLVIN information management GmbH, Germany

    Thursday, August 22, 2013 5:26 AM