Why can't I relate the fkattributeid to any attributes in the system?
I've looked at all the table and I can't find any other tables with that attributeid.
How I am suppose to know which attribute it is?
Joel, not sure why you need to know the database structure...are you experiencing an issue with missing data in cases? Are you using AF 1.3?
In AF 2.0 I have a quick look at the fkattributeid appears to be linked to the attributeid column of table AFElementAttribute - note, the attributeid in AFElementAttribute is consistent across versions of an attribute, where as the primary key of AFElementAttribute is just an increment. Maybe OSI could clarify this one..?
Rhys has a very good point. Although you can see the database schema (we haven't locked that down, which was nice of us), you really should not be messing around with it. We provide the AF-SDK as a means of querying and manipulating the data store. This layer of abstraction ensures that any action performed agains the database is valid, safe and 'legal'. When you start messing around with the database yourself, you start entering dangerous waters and is something we cannot support.
If, for some reason, the AF-SDK does not quite achieve what you wish, you should post here, contact tech support or speak to the product managers. They will be able to initiate possible changes to the SDK, if the business case presents itself. We will always clearly document the changes to the SDK to ensure forwards and backwards compatibility. I can quite safely say that any DB schema changes are going to be internally documented only and totally transparent to the AF User/Developer.
Now that rant is over, Rhys, what you found sounds about right. I'd still need to clarify it with the dev's tho (or look into the source code, if I can get my laptop working again...). I'll try and send some AF-Guru's this way, see what they have to say (I hear they respond well to beer....)
Ok OK. Lesson learned. Now that my hand has been slapped (was close to the face isn't?), I just want to tell that the fkAttributeid guid found in AFCaseResultValue doesn't match any attributeid in any tables I have looked at. But never mind, I will follow your advise and stop misbehaving.
I am doing those Reporting Services (not embedded in an application or a web page with the reportviewer control) and the fastest way for me to get the information out was to use a stored procedure to get my report dataset directly from database. As far as I know, I can't get a dataset any other way than from a SP.
Just found out that maybe I can build a data processing extension for Reporting Services and get my populated dataset with the AFSDK from there.
The PISysOLEDB provider will be available Q42009, per the Engineering Plan.
Direct table access is problematic, since we have significant schema changes planned and direct access means that your code will have to handle data access security, element historization, element templatization, composition, high availability, and my personal favorite, the sandbox.
The PISysOLEDB provider handles all of these issues for you and supports joining of AF meta-data with PI Point data.
Any suggestion on how to access AF Data from Reporting Services.
I could use Data Processing Extension as I said but what others do?
Thanks for your ideas.
You "could" synchronise your AF data with a PI Module Database and use PI-OLEDB until Q4 2009 when System OLEDB provider is released, would depend on size/complexity of your AF database(s). As I type this I am going off the idea but I will click Post now before I change my mind...
You could indeed develop a custom Data Processing Extension (DPE) and register it on the report server. That would pretty much consist of developing your own .NET Data Provider for AF (using AF SDK) - most likely a subset of what is currently known as the to-be-released PI System OLEDB Provider.
That represents quite a bit of work on your end.
Another solution, if you really cannot wait until the PI System OLEDB Provider comes out, is that you develop a small "replication engine" with AF SDK, which would periodically replicate AF data into SQL Server tables. Once that AF data has become "native SQL data", you can use it in Reporting Services.
Retrieving data ...