Saturday, January 20, 2007 4:13 PM
I am just cusrious to know how are you guys generating your application definitions in BDC? by hand or using one of the tools out there to auto generate that scary XML file? I am unable to find a tool which gives me the ability to include stored procedures in my app def instead of pure tables or views?
Any help would be appreciated.
Thanks a lot
Monday, January 22, 2007 11:52 AM
Well the known community tools are getting better all the time but do not support everything yet. So, i've done some implementations now with the BDC and basically I did the first one by hand (beta 2). The second one I used ome of the tools to create the basis xml file and adjusted it by hand.
Right now, I basically copy/paste a lot when creating new xml, untill the tools are good enough to create more complex, non-SQL application definitions. As far as I know, the stored procedures are not yet supported but you should be able to get started using the SDK I guess.
Monday, January 22, 2007 4:09 PM
>>As far as I know, the stored procedures are not yet supported but you should be able to get started using the SDK I guess.
Do you mean that using the SDK , I can call my sps ? How?
Thanks a lot
Tuesday, January 23, 2007 8:47 AM
I haven't found a working example on stored procedures, but I found this:
So basically, you can use stored procedures. Btw, there is a new version of the MOSS SDK, so maybe there is some more info in that one:
Friday, January 26, 2007 4:50 PMWhat tools specifically are out there ? I found a couple of utlities but wondered if there are better ones out there.
Friday, January 26, 2007 6:40 PM
There are a couple of database tools floating around. Check out this one:
Unfortunately I've yet to find one that generates an application definition file when consuming a web service.
Friday, January 26, 2007 7:26 PMThanks for the link Chris. I hadn't used that one yet. I'll try it out. Should save a considerable amount of time in creating the XML :-)
Saturday, January 27, 2007 4:07 PM
we're working really hard at the moment to get BDC Meta Man working for web services. We've got it working but there are a few guidelines here for the first version that we'll be releasing:
It should be out very very soon!!!
Tuesday, February 13, 2007 3:03 PM
Wonder why Microsoft spent so much time creating SPS 2007 with BDC as its main feature failed to provide a tool to help devlopers generate BDC MetaData; that kind of defeat the purpose of trying to get more people to use SPS.
Wonder why BDCMetaMan is charging $1200 per license for the tool that can generate metadata for web service?
Tuesday, February 13, 2007 3:35 PM
My take on this is
1) Microsoft have always stated that there is a significant market for partners to develop value added tools for the MOSS platform. BDCMetaMan is one example. What they have done is provide a very good import routine that will identify exactly where in the XML you have problems.
2) If you create a lot of BDC metadata files, it won't take very long for you to recover the $1200 cost of the license, if you just playing with the toolset then it is probably better that you get into the xml to understand it and what can be done.
Tuesday, February 13, 2007 5:37 PM
I agree with Frank. I would not call a metadata generator for BDC a value-added tool, but an essential part of the framework. It should have been there either as an add-on or most likely an extension to VS 2005. When it comes to complex metadata files, digging into the XML file seems to be impossible for most of the people. I will give you an example (not very relevant, but you can get the point):
You probably know that Reporting services reports are infact rdl files that are basically another format of XML. You can go ahead audit these rdl files by hand and upload them to the Reporting Services engine and get them run without any another tool. For hello world demos or very simple reports that is doable approach, but not definitely for complex scenarios. Well, they did not leave it up to the partners and came up with an extension to VS since the beginning of this great product (Jan2004). I think Microsoft should have done the same thing for BDC.
Tuesday, February 13, 2007 6:07 PM
I agree with you all that there should be a better facility for managing the BDC MetaData.. But we have to understand this is the first release of the BDC in concept and we shouldn't expet it to have all of the bells and whistles that a "mature" product has :-)
This gives the partner market a way to buidl these tools and for Microsoft to take the time to build these features into subsequent versions.
Givent he enormity of this release, I think if given the choice of whether to have BDC but to have to muck with the XML myself or to NOT have BDC and wait until Microsoft Can (maybe) abstract it in such a way to make it easier, I'd choose to muck with the XML anyday ;-)
Tuesday, February 13, 2007 6:38 PM
>>the bells and whistles that a "mature" product has :-)
I am not sure if you are referring to Reporting Services here or not. If yes, then I'd say that those whistles and bells have been there with the product since the beginning. if you did not mean that then let's move on to the discussion itself :-)
I guess the point here is the usability. if I or Steve can muck with the XML today:-) , it does not mean that everyone else can do as well. A quick look at the newsgroup posts reveals that how much people are stuck with mucking with these xml files of BDC. Almost everyone I know who has used BDC ( at a higher level than a simple demo :-)) is nagging about the definition files. It is not easy to work with these xml file especially when entity relationships are a bit more complicated.
Tuesday, February 13, 2007 11:44 PM
I think we all agree that it would be good to have these tools out of the box, and the 'code free' panacea that we would like has been replaced with coding (for want of a better description) xml files. For now we are left with two options, edit the xml or purchase a xml editor. The choice will probably be down to how many systems you need to integrate too and you confidence in hacking the XML.
Wednesday, February 14, 2007 5:00 AM
>>we are left with two options, edit the xml or purchase a xml editor
This time I do agree with you:-).
Point was that Microsoft should not leave these things to the partners.
Wednesday, February 14, 2007 6:26 PM
Reza, My point earlier in reference to whether they should release or not release even if it doesn't have all the bells and whistles was in reference to MOSS & BDC. I think everyone at Microsoft that works on the product knows that there is a better way to manage the BDC configuration and I am sure they more than one approach to tackling this.
The point I was trying to make is that if we had to wait until Microsoft "Got It Right" and relased BDC with a wonderful XML configuration Editor, we wouldn't have gotten the feature in this release at all :-( or the release would have been delayed :-(
By going ahead and releasing it as is, we do have to muck around with confusing XML but at this point, it's better than no BDC at all IMHO :-)
Wednesday, February 14, 2007 6:36 PM
SharePointing, I second that.
Wednesday, February 14, 2007 8:11 PM
>>By going ahead and releasing it as is, we do have to muck around with confusing XML but at this point, it's better than no BDC at all IMHO :-)
I respect your humble opinion , but that may not be the opinion for the poor customer who payes for the whole functionality and only recieves portion of it OR as Frank mentioned has to pay $1200 on the top of MOSS enterprise License for a third party tool to make the functionlity work for his own scenario. I am not sure if you have ever tried to muck up the XML for a real world project (or a web service) and in the meantime you are in a tight budget situation, then you will appreicate the point I am trying to make here. Nobody can expect a perfect BDC at this point,but at least something that can facilitate a customer's process and not something that doubles his hassles. I have personally experienced it and I know how bad that hurts , that's why I think I am wrting these posts with the hope that someone in MS can hear me
Wednesday, February 14, 2007 9:23 PM
there are a few posts that I will try to reply to all in one go...
1, Yes it would be ideal if Microsoft had a tool to create the app def files. However back in May 2006 they said there wouldn't be so we've just tried to fill a gap presented by them.
2, Business Data Catalog is part of the MOSS 2007 Enterprise licensing, so compared to the CAL cost, $1,200 for BDC Meta Man isn't too much
3, We worked out the cost of BDC Meta Man on how long it would take to write an app def file by hand. For the price of BDC Meta Man you'd probably get 1/2 a days consultancy. Considering it could take up to 5 days to write one app def file, I know which I would prefer.
I realise this thread is probably as much against Microsoft as it is that we are charging for our tool (which after all is just filling a gap). 50% of people say we are charging too much, whereas the other 50% think it is very very cheap. From that I think we are about the correct price.
Thursday, February 15, 2007 5:57 PM
Glad to see that someone of from BDC metaman is also here to defend their *great* product. I guess there is a misunderstanding here. First of all, no one is against MS, it was just a feedback and what I thought is right to do. Secondly the discussion was around BDC and not BDCMetaman. I don’t think that BDCmetaman is expensive (good for their customers :-)).
Good luck and thanks everyone for taking part in this discussion.
Thursday, February 15, 2007 7:50 PM
It was my first post and it was nice to see all this discussions going on. MOSS 2007 is a great product and I have been tryinging it for about a month now. I have to admit that BDC metadata generation is the biggest sore point in my experience as I have spent countless hours learning and manually creating the file for SQL Server data source. Now I am not ready to spend more time on it for web service.
I have opened a case with Microsoft 2 days ago and the support center has refered the case to their Office Developer Team. I would like to find out how they will be helping me to create the metadata; what tool would they use? May be they have purchased BDCMetaMan?
As far as BDCMetaman pricing is concerned, I respect that is a business decision and they are filling a gap very nicely at this point because there is nothing(?) like that out there that does the job. But may be as more and more people get into MOSS there may be one brave (XML savvy) soul who would spend a little time to come up with some competition.
As far as Microsoft is concerned, one of their biggest selling points is that MOSS can be setup almost without programming experience. It is therefore surprising for Microsoft to leave such a big gap in one of their most (if not the most) critical features in MOSS 2007. I bet if they put one of their bright interns to the job, they can get something out very quickly (may be a week or less?)
Wednesday, February 21, 2007 3:40 AM
Is BDC is an incomplete feature? Yes. It's great, but it's only part of what I hope we'll eventually have. To demonstrate, take a look look for marketing on MOSS (via the BDC) integration with Lotus Notes. And then try to find a sample or even a theory on how to get it done. If the answer is to tack an ODBC driver onto Notes, that's not an answer. That said, if you can get the ODBC driver installed, then BDCMetaMan will help you build the app def.
If your goal is to build a solution in the most efficient way, that's the answer today. Until Microsoft builds some flesh on top of the skeletons of WSS 3.0 and MOSS 2007, I'm real happy that a third party community exists. Nothing stops anyone from building competing utilities or hand-rolling, but if you have the resources, for Bob's sake, save yourself some time.
Disclaimer: Todd's a friend and I've been using his app since before he combined forces with Nick. Their combined effort is an excellent piece of work.
Friday, March 30, 2007 6:54 AM
WSS 3.0 is a huge platform, and MOSS 2007 is a huge product. There are many gaps in both. For the 2007 release, we prioritized capabilities ahead of tools because it's usually much easier for our ISV partners to extended the OOTB capabilities and to provide the wide variety of tools that customers need. If we had decided to include even just a few tools, it would have taken us more time reach RTM, and we might have priced the licenses a bit higher, neither of which most of our customers would prefer over the current situation, which has already been discussed at length in this thread.
Monday, April 02, 2007 1:59 PM
Thanks Lawrence ! I was trying to tow the line :-0
I have no doubt that there is a team working right now to address these woes :-}
Thursday, July 05, 2007 5:23 AM
BDC is a great product and i appreciate MS for that, but when you really start using it you feel the heat of creating a app def file, which is so cumbersome that we need a third party tool like BDCMetaMan to develop it.
so my question is, why when we are paying so much money for MS product, cant they deploy something along with its product that will really make development more seamless, i mean we are already wasting so much of time creating the app def's, and with no offence to BDCMetaMan, its a great product, but we are not quite sure if we can include it in our estimates for now.
Saturday, July 07, 2007 10:58 PM
I thought that I would add that we also produce a tool for creating MOSS Application Definitions our product supports WebServices, Tables, Views, StoredProcedures and Custom SQL. Most of which is just Drag & Drop. You can create a working definition in a matter of a few minuites.
There is a free 30 day trial of the full version on our web site at www.simego.com look for MOSS BDC Design Studio
Monday, July 09, 2007 7:31 PM
I've been using the tool from Simego. My developer used the tools as well. For myself, it was a much easier tool to use and understand than BDC MetaMan, which I saw the preliminary workings of when Todd presented it in its longer form name at DevConnections in Nov. 06.
This is standard fare for Microsoft and anyone who wants more should not be surprised. Continually through the years Microsoft develops products but leaves a segment for developers to come in and heap on some services/applications. After a few updates, MS releases a tool and everyone is happy. I agree that the documentation on the BDC is a bit lackluster, but I'm not about to jump on anyone for charging what they charge for the tool.
If you seriously need the BDC then you should be able to afford the tool to develop appdefs or a developer who can do it for you.
Saturday, August 25, 2007 5:20 AM
I am so glad that this issue has been addressed in the latest release of MOSS SDK by introducing Microsoft Business Data Catalog Definition Editor. That was a good one
Wednesday, August 29, 2007 2:23 PM
creating a metafile is quite simple I discovered. here are some guidelines that may help you:
1 download the MOSS sdk from the microsoft site
2 read the explanation of the bdc meta xml so you know what each main element means (lobsystem, entity, discriptors, methods, etc...)
3 download and install XML Notepad 2007 and get used to the interface. I really had to get used to the interface but after playing around with the tool I discovered it to be a very useful tool to generate xml documents.
4 copy and paste the example meta file from the sdk and store it like e.g. MyMetaFile.xml
5 open the file with XML Notepad 2007.
6 connect to a schema file of the bdc (located in Program Files\Microsoft Office Servers\12.0\Bin): bdcmetadata.xsd. to connect use the view menu and choose for xml schemas.
7 start editing the sample file and configure it to use your datasource.
8 the schema file will prevent you from using illigal elements and attributes. text values are not captured by the schema file so you need the sdk again to read the valid values.
also be aware of the authentication options:
passthrough: logged on user credentials are passed mits kerberos delegation is turned on. otherwise the request will be anonymous to the database.
revertoself: used the service account to connect to the database
- Proposed As Answer by steentje1986 Wednesday, April 22, 2009 8:49 AM
Friday, September 14, 2007 4:42 PM
Creating a metafile is quite simple
You must know something I don't then.
If I could afford to dedicate a couple of months of my life to doing nothing other than faffing about with BDC, I might stand a chance of one day getting to the position where I could claim that it's "quite simple". But I can't; like most people in IT I have lots of other technologies to look after, and billable projects which are higher priority.
I haven't seen anything as demonstrably NOT simple to pick up, since I was given the entire CA Minder suite to learn "in my own time" and someone else had the manuals. BDC is one component of one product and I've found it a total nightmare to learn, not least because I have to keep leaving it for weeks at a time, then go back to it.
A graduate could knock out a Coldfusion front end for our stock system in about a fortnight and that includes the time it'd take them to learn how to use CF. They could be out on the road inside of a month.
We reckon it'd take that graduate a minimum of a month to figure out how to use BDC, and at least three months of playing around with it after they've reached that point, to be confident with them going out to a customer site and installing it. That's if we made sure they didn't even look at any other technologies, and focused solely on Sharepoint development.
It should NOT need to be as difficult as that, to knock out a web front end for a database.
Creating a metafile should be easy. I work with hardened appliances that use Oracle and MySQL databases, and use XML to configure them, just fine.
Using a SQL query should be a real doddle, especially for a Microsoft product accessing databases on a MSSQL server. But it isn't. One obvious example - if the query source you need to work with doesn't use SQL primary keys at all, then 15 years of Microsoft products knowing how to carry on regardless has gone flying straight out of the window.
Has anyone yet come up with a reason why that's the case? Because it's a STUPID limitation. I can reel off a list of CRM and enterprise database systems that have tables without keys in them but can nonetheless be presented in a data grid on a web page with minimal effort, if you're NOT using Sharepoint.
Getting BDC to parse the XML successfully, then import it successfully and then have the web parts behave the way you want them to - well, that's a bit like having your limbs chopped off and then having to swim through treacle while wearing a belt with lead bricks in it.
Case in point - here's the Sql source for a view called Stocklist which resides in our stock database:
SELECT warehouse, product AS [Product ID], long_description AS Description,
CASE WHEN [free_qty] > 0 THEN 1 ELSE 0 END AS IsFree,
CASE WHEN [allocated_qty] > 0 THEN 1 ELSE 0 END AS IsAllocated,
CASE WHEN [physical_qty] > 0 THEN 1 ELSE 0 END AS IsInStock,
CASE WHEN [back_order_qty] > 0 THEN 1 ELSE 0 END AS IsBackOrdered,
CASE WHEN [on_order_qty] > 0 THEN 1 ELSE 0 END AS IsOrdered,
physical_qty AS [Amount In Stock], allocated_qty AS [Amount Allocated],
free_qty AS [Amount Available], back_order_qty AS [Amount Back Ordered],
on_order_qty AS [Amount On Order], last_supplier AS [Last Supplied By]
This is in my development copy of the database. I had to create this view, because when I tried to get BDC to run the query on its own, it got totally confused by the CASE WHEN options. No idea why.
This is what happened when I got my hands dirty with the XML:
1. Created XML for stocklist view, with no filters, and no parameters. Tested it using a copy of database stored on the Sharepoint server itself (brand new quad-core server, only 2 users). Took 20 seconds to return 15,000 records. Consistently.
I can return the same dataset from outside of Sharepoint, in half the time - from the live server, which is a comparitively ancient PIII box running SQL Server 2000 and Sage; is used by 50 users; and is the other end of a slow 1MB link from here. Make of that what you will.
2. Added a wildcard filter to allow users to filter the Description field. On its own, this worked.
3. Added a filter to check a boolean value in a boolean field - the IsOrdered field, to be exact.
Result... XML failed to import.
4. Repeated 1-3 in BDC Design Studio. Again, it failed. Portions of the XML generated are shown below.
<TypeDescriptor TypeName="System.Boolean" IdentifierName="IsOrdered" AssociatedFilter="Restrict to items on order" IdentifierEntityName="StockList" Name="IsOnOrder">
<DefaultValue MethodInstanceName="StockListFinderInstance" Type="System.Boolean">FALSE</DefaultValue>
--- SNIP ---
<FilterDescriptor Type="Comparison" Name="Restrict to items on order">
<Property Name="UsedForDisambiguation" Type="System.Boolean">TRUE</Property>
<Property Name="CaseSensitive" Type="System.Boolean">false</Property>
<Property Name="IsDefault" Type="System.Boolean">false</Property>
You might ask, what's the point of this fragment? Well, the error message I get when I try to import it is...
Could not parse Property with Name 'UsedForDisambiguation', Type 'System.Boolean', on Metadata Object with Name 'Restrict to items on order'.
Just for fun I changed the names in case that's what the problem was. Still failed.
I have checked the SDK and have googled frantically. Can I find even a remotely plausible explanation for why this isn't accepted? NO. And that's the biggest failing of all with Sharepoint. If you want to know how something works, or why it works one way rather than another, there's no documentation to speak of.
If you follow the AdventureWorks examples by rote then things will probably work. But that's completely beside the point. Real world implementations frequently don't look anything like the canned demos and tutorials - and if BDC had fallen down every time you strayed off the AdventureWorks path - like it has with me - perhaps you wouldn't be so quick to use the word "simple".
Thursday, December 13, 2007 8:51 PM
Some good posts on this topic (sorry I’m getting to it a bit late). One of the things I have always liked about using SP is how it gives users a lot of power to users without making high demands on the administrators or IT folks. One of the big exceptions to this is the BDC (there are others). The simple “Can I put some CRM data on this page?” request quickly turned into a nightmare as we tried to overcome the learning curve of the BDC object model, definition file generation, etc. Certainly an XML generation tool helps but the point is (I think Tim got this right) it SHOULDN’T be that hard.
The BDC (IMHO) is not intuitive to the way people (especially data people) expect to have to request data. It puts strange rules such as the key restriction and others that seem to have to do with how the BDC web parts will ultimately render the data than how the data should be requested.
We worked with the BDC and then (being the resourceful MS partners we are J) decided to develop an alternative for people who wanted all the functionality in a simpler, easier to implement way. It just seems so strange that SP is so easy to use/develop/maintain in many ways, but every once in a while you run into these areas that are very difficult. As another example of this, compare “homegrown” workflow in SP to add-ons developed by partners. Can you do everything in “homegrown” SP? Sure, as long as you have time, patience, and developers. Can you use the BDC to get at your data? Absolutely, just be prepared to deal with some headaches along the way.
This is not intended to bash MS or the BDC. I think the posts regarding what could be done in the time allotted make a good point. It is important to remember that MS has given us one way to get data into our SP pages, partners and others have provided ways to make it easier and/or alternative ways to accomplish this. We have the ability to use these based on our abilities, limitations, budgets, etc. to provide solutions to our end users.