From various student questions over the last few weeks. More to come …
Q. I had heard that SQL 2005 was going to have an UPSERT (try UPDATE, if it fails, do an INSERT) capability. What’s the status?
A. UPSERTs have been requested for many years and always wind up on the request list for features. It does not exist in SQL Server 2005 and isn’t going to be added. There was, however, mention of an Insert With Merge (“Upsert”) in some early Yukon documentation and books, but nothing there now.
Q. When I upload an RDL file in SQL 2005, is that RDL stored in an XML data type column somewhere? If so, which table?
A. Not in XML. I believe it is stored in the Catalog.Content column which is a blob.
Q. Will SQL/Express have DTSRun capability? I know it won’t have the designer.
A. No. unless something changes
Q. Will the Office Business Scorecards Accelerator be the “Microsoft way” of viewing KPIs, besides from BI Dev. Studio?
A. Not sure where they are with MOBSA and updating it 2005, and as you know, the KPIs that are in MOBSA are not exactly the same since they simply point to an AS2000 measure and have the ability to also handle metadata and weighted rollups.
Q. Is the XML support in SQL 2005 provided by .NET or MSXML? I’m guessing MSXML, because that’s what’s listed during install.
A. I believe this is correct. This is why the MSXML6 Parser and SDK get installed
Q. The Website www.sqlserverdatamining.com has some sample code on how to host the DM viewers in your Windows apps. What about licensing?
A. This is still an open question with marketing, they have not nailed down that specific issue but should by Beta3 timeframe as a number of questions have come up on that.
Q. Any late breaking news on SQL 2000 DTS packages being able to be opened, edited, migrated, etc. into SIS packages?
A. There is a migration tool that will perform some upgrades to DTS packages, but there are certain things that need rework, such as scripting, Data pump transforms, and dynamic properties. A good Whitepaper was just published on this coming out of the Project REAL work it outlines most of the details. However, I hear that there is more work being dome on the upgrade process so stay tuned. Also, when installing SSIS, there is an advanced option to install the DTS runtime components which will allow DTS packages to run with SSIS packages.
Q. Any other ways to persist SIS packages besides as an XML file?
A. Packages can be saved as external files or in SQL Server tables, but both are stored as XML. I’ve asked about a way to encrypt the XML, but this is not an option unfortunately unless Windows file level encryption is used, but an administrator can always take control of the files.
Q. I thought I had heard that SIS packages get compiled to an assembly and run as .NET code, like BizTalk maps and orchestrations? Not true? What gets “deployed” then?
A.The deployment utility has some nice features in that it can package up the runtime DLLs for SSIS packages to run on other servers, it also will include any compiled custom components, adapters and tasks. The SSIS packages are just XML and nothing changes there when deploying them. There is a SSIS service, however, that can run on the server and it helps performance by allowing the built-in components to remain in memory for quick use when a package is executed.
Q. Is there a way to put a breakpoint on a particular SIS task and the execute the package, stopping at that breakpoint? If not, how else to “debug”?
A. Yep, full breakpoint support and its very cool for development… with all the standard VS options of watches, stepping through code, etc There’s also something called a data viewer that can show sample/full data at different transformations in the form of a grid or even graphs. It halts the data flow while the data is reviewed and then the designer can continue on with the dataflow.
Q. Why doesn’t Reporting Services 2000/2005 support an .RTF or .DOC export format? Their competitors do, so why not Microsoft?
A. This is on the Office team. Expect this in the next Office release (hopefully). Until then, check out OfficeWriter.
Q. Any word on other .RDL converters out there? I had heard that Hitachi (I believe) had one for Crystal Reports, but that they are sitting on it.
A. Crystal got out the big stick and went after Hitachi and RPTtoSQL and others offering either a utility or a service since the .rpt is a blackbox and folks had to use Crystal objects to perform the conversions and that is against the legalize in the Crystal licensing. Since Microsoft does not want to give the perception of intentionally stomping on Crystal, they have steered clear of the topic all together.
Q. Is the Sharepoint BI portal ready for SQL 2005?
A. RS2000 SP2 will have SPS webparts for RS, these will also be offered for 2005. Will it work on Windows Sharepoint Services? They work with SPS and WSS Where do I find this? SP2 should be out in soon. I have not looked for them in 2005 IDW13 Licensed? There will be no licensing for the webparts, they will be covered by SPS (or Win2K3/WSS) and RS licensing, nothing additional for the use of them
Q. Hooking up a Reporting Services matrix control to an AS cube is a daunting task. Are there any good step-by-step guides out there or links I can share with my students?
A. It is a little easier in 2005 given the MDX query builder.
Q. Besides Report Builder, what other cool and interesting features are going to be part of SQL Server 2005 Reporting Services?
A. There will be a configuration tool that will keep people from screwing up their config files. There will be an API for Report Controls that will crack the ISV market open by permitting folks to plug-in items to RS — the most referenced is that Dundas will offer their Enterprise Charting capabilities that you can license above and beyond the OEM version. Of course, a very large feature is the UDM integration, they are really peas & carrots how they are meant to work together to offer a “single version of truth.”
Q. Can you connect to Analysis Services 2005 via mixed mode?
A. No.
Q. Will you be able to secure Perspectives? In other words, are they just used to simplify viewing, or can they be used to restrict what a User can access?
A. No.
Q. If a cube has 4 partitions, and 1 of the 4 is ROLAP without caching, and that data source goes down, will the entire cube be unavailable or just that aggregation?
A. The other partitions remain available.