Sunday, March 28, 2010
Thursday, March 25, 2010
I approached one of the popular SQL Server websites (I would keep the name anonymous) to help me create awareness and gather public opinion about the needs of these features from the developer community, but I got a denial from their side. I got a suggestion from their side to request individual blogger to help me to create awareness. I have created a Connect item to request the same from Microsoft, but most of the developers would not be browsing connect digest everyday or even once a month to take a look at feedback / suggestions / bugs and vote for / against them.
After going through the above mentioned post, if you feel that it's a valid requirement, please vote the Connect item I have created here. If you have a blog and feel like supporting my request, please blog about it so that more attention from the community can be brought to request these features from Microsoft for BIDS and SSMS.
Now coming back to the answer of the question in subject, which is the reason for which you came to this post, just browse this SSIS Forum thread to read the scenario where this feature was required by a professional and my solution for the same.
Wednesday, March 24, 2010
Today I was going through an article featured on powerpivot-info.com, and I stumbled upon an interesting set of components offered by this company called "Theobald software". Before I go ahead with the entire story, a little background about SAP and why a MS BI geek would be interested in SAP. Every mature and informed professional in BI should be knowing that SAP is the world-class ERP solution for almost every kind of business and Germany can be considered as Mecca of SAP. Now like any Enterprise level products it provides an application level interface and also holds data within itself in a proprietary format. Entire business process workflow for almost any kind of business and any kind of process can be managed using this product, and the biggest USP of this product is that it comes with a proprietary programmable API called BAPI. I can write for an year about this product and still I would fall short, so I would cut this story short here and move ahead to how on earth I know about SAP and what a MS BI guy has to do with it.
I have been working since the past 1 year on a systems integration programme where I am involved in migrating data from a proprietary ERP application to SAP, and the tool we have been using is SSIS for data migration. It has been a very interesting, challenging and one of it's kind of experience as a Technical Lead as this is the first time I am working in such combination of technologies.
Coming back to where we started, this company offers components that can be used as a wrapper between SAP and Microsoft technologies like .NET, SSIS, SSRS, Sharepoint and Powerpivot. I have glided over the features that this product offers and based on my whatsoever little experience of working with SSIS and SAP, below are some points that should be considered before using these or any components while working with SSIS and SAP.
1) Business data in held in proprietary format in SAP, and any change triggers a workflow or a set of processes in SAP. It can be thought of an application equivalent of mainframe systems, and it would take you to be no less than a senior level business analyst or senior SAP functional consultant to change any data into SAP.
2) Based on point 1, it can be deemed that for other applications which may aspire to hook into SAP, like SSIS or SSRS, it can only be a read mode access and not a write mode access.
3) The components that this company provides, claims to have features that facilitate to read data directly from SAP tables. My experience has been that, first you need to have a broad knowledge about what these tables contain, as neither the names would be self-relevant nor it would be that easy to figure out relationships from any table like foreign-keys in a database. For example, in the Materials Management module would have following tables: MARA, MAKT, MBEW, MLTX and others. Even all the column names would be in German. A VC++ programmer would easily co-relate with these kind of nightmares.
4) Another feature that these components offers is of SAP queries, something like our stored procedures. Believe me, you would never require any reporting tool if you are using SAP. Almost any kind of reports can be created with any level of flexibility that can be imagined. To put just one of the fact to the table, just any single module of SAP comes with approximately 800 built-in different kind of reports which can be modified too. Also if the report is provided in Excel format, Excel itself can handle much of the graphical or pivoting features to a considerable extent.
5) If I consider the usage of mySAP portal (which can be thought of similar to Dashboards hosted on Sharepoint created using Performancepoint Dashboard Designer), master data, and BW Cubes which might need to be used and/or analyzed with another system that is hosted on a different platform like Sharepoint, in this case I see a good use of these components to create a BI solution on the top of SAP and any other ERP level product or any data models hosted in a different platform (including Microsoft). I recently read on a whitepaper that Panorama Novaview is able to hook into these SAP ERP Tables, and Microsoft SSIS also has a connector to read from SAP but we do not have any components out-of-box in SSIS / SSRS specially designed to work with SAP.
This company is German in my understanding, has all the German business partners, and claims to have 650 clients out of which half of them are german. But even if I consider the rest, it should be having 300+ clients who are using MS BI integration with SAP, and in my career till date I have worked and even heard of only a single project where SSIS is used with SAP. I really wonder how big is the BI Universe and how smaller is my knowledge !! :) These components are interesting and if you have even any brittle idea of a few terms of SAP, I suggest to check out these components and catalogue them in your list as this cannot be considered less than an MDX library available in the form of Excel Functions ( I should have said DAX in short ).
I plan to publish another post sometime after I recover from the above shock, to share how we use SSIS and SAP R/3 for data migration from a proprietary CRM application that is stored in Oracle Warehouse to SAP for all the modules like Materials Management, Customers, Vendors, Account Receivable, Accounts Payable, Human Resources with Parallel Payroll (the most complex module I have ever worked in my life), Cost Centres, WBS, Fixed Assets, Purchase Orders, GL Balances and many more.
Thursday, March 18, 2010
SSAS + Excel Services + SSRS + Performancepoint Services = Powerpivot + Office 2010 + Panorama Novaview ?I'm reading: SSAS + Excel Services + SSRS + Performancepoint Services = Powerpivot + Office 2010 + Panorama Novaview ?Tweet this !
Panorama has come out with quite a handful of products or product features that works with powerpivot and microsoft office 2010. After going through the features that Novaview offers for leveraging the capabilities of Microsoft Powerpivot, I particularly feel interested with the Universal Data Connector capabilities of NovaView, as it can hook into a variety of data sources ranging from Excel to SAP ERP tables.
My equation might not be precise, but from my initial grazing on the Parorama's datasheets and whitepapers, I have summarized my understanding in a single equation. And as it is said that "Keep your friends close, and your enemies even closer", I like keeping a watch on other competitive (I mean to say Partnering) products. And if you adopt the same philosophy, check out these panorama's integration with microsoft business intelligence whitepapers from here, and one another great source of information that is more precisely banged on target is a post on Ella Maschiach's BI Blog.
Tuesday, March 16, 2010
The steps towards using this web part from a very higher level can be as below:
Monday, March 15, 2010
Friday, March 12, 2010
Thursday, March 11, 2010
I felt the need to compare two databases and I thought of trying out the Compare Schema feature. Surprisingly I was not able to find it out at all, and then my real issue started. I did all I can to figure out what would be the real cause of the same, and I also felt into a suspicion that, it can be the case that SQL Server 2008 R2 might not be supported by this tool like it was with SQL Server 2008 in the GDR 2 release. Out of my impulsive nature, I also posted a query on the forums.
Finally, I figured out a small point which solved this entire puzzle. The installation was of "Professional" edition !! You might not be able to figure out yet, what it has to do with this issue. My feeling is that VSTS 2010 Professional Edition is almost a paralyzed edition for DB development using VSTS. If you compare the database development features available in Professional vs Premium/Ultimate edition, almost no features of DB Pro are available. Check out the feature comparison between these editions from here.
Monday, March 08, 2010
Microsoft Business Intelligence ( MS BI ) Missing Features even after SQL Server 2008 R2 - My Wish List request to MicrosoftI'm reading: Microsoft Business Intelligence ( MS BI ) Missing Features even after SQL Server 2008 R2 - My Wish List request to MicrosoftTweet this !
Before I start with my greedy list, I would like to make one point clear. My intention is to help make the product better to help it's real users (developers) tackle real life problems more efficiently. I by no mean intend to discourage the product (coz even my bread and butter depends upon it), but highlight the shortcoming so that the same can be addressed if the community and Microsoft feels appropriate. I keep on providing workaround for the shortcomings of any feature on MSSQLTips.com to help the community do bridge the gap of our present shortcomings, which can be read from here.
1) Addition of a few VSTS 2010 features to BIDS : When I started working with VSTS 2010 RC, I saw that an almost full fledged SQL Query Analyzer is available and honestly I got super jealous. The reason for the same is that when I work with BIDS, I need to build and manage database objects for which I need to use SSMS. Using Server Explorer still I can get similar kind of environment, but it's an MS Access kind of query designer and not SSMS style, that too with no intellisense. VSTS has got classic interface to create class diagrams, and on contrary to that one can't even export SSIS package design to any other formats. Whether it's a master-child package design in SSIS or a Reports and Subreports (which can also been seen as a master child kind of design), a class designer should be available in BIDS where one can map the components to entities of a design.
2) Search in the development environment of SSRS and SSIS in BIDS : I mentioned this topic in one of my earlier posts also. When one lands on the moon of a messy package or report with lots of components added to it, finding a component becomes as good as finding water on moon. There should be features in BIDS which can help locating components by just providing the name of the component. I honor the argument that other tools which might be providing similar features are expensive, but here we are not discussing project economics. It's about how a product can be made an enterprise class tool, providing all the tools of trade a developer would require in day to day development work.
3) SSMS should be added more feature for metadata management : No doubt SSMS has come a long way from it's previous birth where is was known as Enterprise Manager. But still it lacks features, which surprisingly are found in freeware third party add-ins like SSMS Tools pack and SQL Prompt. VSTS 2010 has got DB Pro, which can be best used from SSMS but we use tools like TableDiff for the same. Custom Reports feature was introduced in SSMS, but there is one bug in it which few people would be knowing. If you create a report using BIDS 2008 in SSRS 2008 format, you won't be able to use it even in the R2 release. If you use the SSRS 2005 version, it would work fine. The reason for the same is that the Custom Reports feature use the Reports viewer which is capable of displaying on SSRS 2005 version reports. Such bugs should be cleared off as the functionality has completely got paralyzed due to such bugs.
4) Profiler for BI Deliverables : Profiler is the best surveillance tool I have ever worked, and I have always wished for such tool for SSIS Development. We get a detailed Progress Tab to see the execution progress, but I don't think it matches any near to that class of Profiler. Also if Profiler itself can be made dockable in BIDS with the option of hooking the worker threads of SSIS, Progress Tab can be made much more organized and usable. Presently it loads tons of periodic information which not expand or collapse option in these slices of the same higher level information.
5) Design Pattern Templates for BI Projects Types in BIDS : Microsoft .NET has got some very widely accepted design patterns like MVC and MVP. I have worked in the capacity of a Team Lead for .NET projects at some point of time in my career, and based on my career experience I can say one thing that design patterns are admired like Beer Festival in Europe, but in MS BI world I find design patterns as less as number of pandas left in this world. To provide what I mean by Design Pattern Template, go through one of the design pattern I suggested here. Some of such design patterns can be standardized in SSIS and SSRS development too, and can be provided in the form of template too.
6) Better Deployment Mechanisms in BIDS : I bet that many professionals would agree that the deployment mechanism offered for SSIS and SSRS packages is completely below professional quality. How to automate SSRS Reports Deployment is one of the most searched topic for SSRS, and the page on my blog where I have explained use of RSScripter receives very high hits due to the same reason. SSIS involves deployment of database objects that support it along with SSIS packages, which is not supported out of the box from BIDS.
7) Better Debugging Mechanisms for SSRS and SSAS in BIDS : I have no grievances for SSIS debugging tools, but for SSRS I feel Microsoft completely forgot to provide debugging tools at all. Reports are viewed over a browser, and in a client server environment that same would obviously work over HTTP. There are no tools to debug if a report starts performing too poorly, and the only means to resort it use some HTTP Proxy tools to profile and dissect the flow of content over the wire to debug performance. There is no mechanism to debug the performance of a complex query language like MDX in SSAS development environment and we resort to tools like MDX Studio and MDX Script Analyzer.
This is just my open opinion, and feel free to leave your comments to provide your opinion or educate me wherever you feel my ignorance.
Sunday, March 07, 2010
This brings an added advantage to both the .NET developers and DB developers community. But I see it more advantageous to the .NET community, as SSMS has not done the same level of feature addition as VSTS team. If you go thru the feature list, you would find that entire toolset of DB Pro (Data Dude) is available now in VSTS 2010, but the same tools are more required for folks who work with SSMS, and the same is not available in SSMS. For example the "Compare Schema" feature of DB Pro/Data Dude is available in VSTS 2010 now but as a DB professional I would like it to be available is SSMS as I need to use it more often from there, than from VSTS. An almost full fledged query editor like the one we have in SSMS, is available right from VSTS. But the diagramming capabilities in SSMS are still not as strong as the ones availalbe in VSTS 2010.
As per my opinion, VSTS team has done a great job of fertilizing their platform by borrowing features from SSMS or SMO, but SSMS team has still got a great scope of improvement when compared to VSTS 2010. You can download Visual Studio 2010 RC from here.