Siddharth Mehta's Blog

What is Excel Services ? What is REST API ? What is Excel Services 2010 REST API ?

2009-11-06T16:56:58.945-08:00

What is Excel Services ?

Excel Services 2007 shipped in Microsoft Office SharePoint Server 2007 as part of the Enterprise CAL. Excel Services 2010 provides real-time, interactive, Excel-based reporting and dashboard capabilities which ship as part of SharePoint Server 2010. Also it includes APIs which enable rich business application development. One such API is Representational state transfer ( REST ) API.

What is REST API ?

A RESTful web service (also called a RESTful web API) is a simple web service implemented using HTTP and the principles of REST. Such a web service can be thought about as a collection of resources. The definition of such a web service can be thought of as comprising three aspects:

• The base URI for the web service
• The MIME type of the data supported by the web service. This is often JSON, XML or YAML but can be any other valid MIME type.
• The set of operations supported by the web service using HTTP methods (e.g., POST, GET, PUT or DELETE).

More about REST can be read on Wikipedia.

What is Excel Services 2010 REST API ?

The Excel Services 2010 REST API is a new programmability framework that allows for easy discovery of and access to data and objects within a spreadsheet. The data, including charts, that is returned by the REST API is not static – it’s live and up-to-date.

With the REST API, any changes in the workbook are reflected in the data that is returned. This includes the latest edits made to the workbook, functions that have recalculated (including User Defined Functions), and external data that is refreshed. The REST API can also push values into the workbook, recalculate based on those changes, and return the range or chart you requested after the effects of the change have been calculated.

By crafting the proper URI, the REST API allows you to:

• Discover the items that exist in a workbook, such as tables, charts and named ranges
• Retrieve the actual items in the workbook in one of the following formats: Image, HTML, ATOM feed, Excel workbook
• Set values in the workbook and recalculate the workbook before retrieving items

The content above is a re-draft of the original article on Microsoft Excel Blog, and it has been re-drafted for the sake of simplicity of understanding.

Agile Microsoft Business Intelligence Implementation - My next presentation at London SQL Server Users Group

2009-11-05T14:10:48.425-08:00

I have also submitted an article on http://SQLServerCentral.com on the same topic, so I would not publish any write-up on the same topic. Soon the article should be published and available for public reading.

Those of you who would like to make to the event, please register your seat. Entire details about the agenda, speakers and registration can be accessed on this URL:

http://sqlserverfaq.com/events/193/Joing-SQLUGWindows-Manageability-UG-SQL-Server-internals-and-best-practice-for-Absolute-Beginners-Tony-Rogerson-SQLUG-Session-from-WMUG-to-follow-MS-Business-Intelligence-Project-Boosters-Siddharth-Mehta.aspx

Free E-book : Sharepoint for SQL Server Database Administrators ( DBA )

2009-11-05T14:01:15.728-08:00

The content in this book is a copy of selected content in the Office SharePoint Server technical library (http://go.microsoft.com/fwlink/?LinkId=84739) as of the date above. For the most current content, see the technical library on the Web.

Download this book as a Microsoft Word document (.docx) (http://go.microsoft.com/fwlink/?LinkId=168786).

Download this book as a PDF file (http://go.microsoft.com/fwlink/?LinkId=168793).

SSRS / BIDS 2008 Reports cannot be consumed by report rendering control in SSMS 2008, but only SSRS / BIDS 2005 Reports

2009-11-01T16:00:14.804-08:00

How to automate SSRS reports deployment or How to create a setup for SSRS Reports deployment

2009-10-29T16:34:15.440-07:00

Today on 29th Oct 2009, I had a chance to present the use of a tool called RSScripter for deployment of SSRS Reports at London SQL Server Users Group. SQL Server MVP Jamie Thomson presented a nice session explaining use of MS Build to automate SSRS Reports deployment. In alignment to the same topic, I got a few minutes to present my knowhow on the same topic.As documented on the product site, "Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters."It has a very nice GUI and the options are pretty self-relevant. There are a few steps that one needs to follow to create a setup out of this utility for reports deployment.Step 1: Configure paths for the below a) Point to correct path of RS.exe location on the Globals Tab b) Target report server url where you intend to deploy your SSRS Reports c) SSRS Solution or Project file under which all the SSRS reports are added or select individual reports that you would want to target. Also you can click on the Get Catalog button to find out the reports on the server that you have configured.d) Output location where you want the setup files to be created e) Select Generate Report Script on the Report Tabf) Select Specify New Parent Folder / Create Folder Hierarchy option from the General Tab as per your requirement. g) Use other properties as per requirements, which can be used to fine tune the settings for deployment. Detailed information about the effect of using these properties can be found from the readme file of the tool, hosted on its product site.Step 2: Click the Script button to create setup filesStep 3: Execute the setup batch file created, and this would deploy all the reports.Behind the scenes, the batch file executes the VB Script files created for each SSRS Report. This script files contains the code, that creates a corresponding object of SSRS Reports model, and uses the RS.exe utility with these reports as parameters. This utility sets all the properties of these objects that are collected thru the user interface. The biggest assets of this utility is its a FREEWARE and it uses VBScript, MS DOS Batch File, and RS.exe to create a setup for deployment. Also the output can be version controlled by using some version control software. The setup created by this utility is very transparent to understand, no extra assemblies (.dll) and it builds on RS.exe and VBScript which doesn't require any additional piece of code or software to interpret.If still there is someone left, who is a fan of coding, all the information on how to create scripts using VBScript to deploy each different object of SSRS can be found on this page.[...]

What is a Dashboard , What is a KPI , Real World Example of Performance Dashboard

2009-10-22T15:35:20.284-07:00

It's said that "A Picture is worth a thousand words" and I say that "A Dashboard is worth a thousand queries". You can get all the definitions and pictures of a Dashboard from Wikipedia or some books. But if you want to get a real feel of how a dashboard looks like, there are two great places where one can take lessons from:

1) MS BI VPC: This one is a good example for BI developers, as it comes with all the great examples of everything that can be developed using Microsoft BI Stack of Technologies. It also includes a dashboard for an AdventureWorks cycle, that is created using Performancepoint Monitoring module. This VPC comes loaded with an evaluation version of all the softwares starting SQL Server and MS Excel to Performancepoint which has one or another feature of Business Intelligence.

2) Realtime Example: This one is for best ways to learn of how a dashboard would look like and how it would help management to take strategic decisions based on the information presented in a form of decision support system. Someone who is fresh might not be able to quickly dive into BI tools, but there's a very nice site where one can see a Dashboard in live action.

This website is called the Federal IT Dashboard. It a magnificient website and the Dashboard presentation is majestic. This website is probably owned by the US government to publish to the public, of their IT spending on different portfolios. Even if you are a seasoned BI Developer or a Novice BI aspirant, I recommend taking a look at this website to get a real feel of how Dashboard can make a difference and how an interactive dashboard can look like.

Project Gemini is now SQL Server PowerPivot for Excel and SharePoint

2009-10-28T11:23:04.523-07:00

Below in an excerpt from the post where they declared the same:

Historically, business intelligence has been a specialized toolset used by a small set of users with little ad-hoc interactivity. Our approach is to unlock data and enable collaboration on the analysis to help everyone in the organization get richer insights. Excel Services is one of the popular features of SharePoint 2007 as people like the ease of creating models in Excel and publishing them to server for broad access while maintaining central control and one version of the truth. We are expanding on this SharePoint 2010 with new visualization, navigation and BI features. The top five investment areas:

1. Excel Services – Excel rendering and interactivity in SharePoint gets better with richer pivoting, slicing and visualizations like heatmaps and sparklines. New REST support makes it easier to add server-based calculations and charts to web pages and mash-ups.

2. Performance Point Services – We enhanced scorecards, dashboard, key performance indicator and navigation features such as decomposition trees in SharePoint Server 2010 for the most sophisticated BI portals.

3. SQL Server – The SharePoint and SQL Server teams have worked together so SQL Server capabilities like Analysis Services and Reporting Services are easier to access from within SharePoint and Excel. We are exposing these interfaces and working with other BI vendors so they can plug in their solutions as well.

4. “Gemini” – “Gemini” is the name for a powerful new in memory database technology that lets Excel and Excel Services users navigate massive amounts of information without having to create or edit an OLAP cube. Imagine an Excel spreadsheet rendered (in the client or browser) with 100 million rows and you get the idea. Today at the SharePoint Conference, we announced the official name for “Gemini” is SQL Server PowerPivot for Excel and SharePoint.

5. Visio Services – As with Excel, users love the flexibility of creating rich diagrams in Visio. In 2010, we have added web rendering with interactivity and data binding including mashups from SharePoint with support for rendering Visio diagrams in a browser. We also added SharePoint workflow design support in Visio.

Reference: cwebbbi.spaces.live.com

Data Analysis Tool / Add-In to extract and develop Business Intelligence using Excel and SQL Server : PowerPivot

2009-10-28T11:23:04.523-07:00

Overview:

PowerPivot for Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the application users already know and love—Microsoft Excel. It provides users with the ability to analyze mass quantities of data and IT departments with the capability to monitor and manage how users collaborate by integrating seamlessly with Microsoft SharePoint Server 2010 and Microsoft SQL Server 2008 R2.

BI Offerings from PowerPivot:

Give users the best data analysis tool available: Build on the familiarity of Excel to accelerate user adoption. Expand the existing capabilities with column-based compression and in-memory Bi engine, virtually unlimited data sources, and new Data Analysis Expressions (DAX) in familiar formula syntax.

Facilitate knowledge sharing and collaboration on user-generated BI solutions: Deploy SharePoint 2010 to provide the collaboration foundation with all essential capabilities, including security, workflows, version control, and Excel Services. Install SQL Server 2008 R2 to enable support for ad-hoc BI applications in SharePoint, including automatic data refresh, data processing with the same performance as in Excel, and the PowerPivot Management Dashboard. Your users can then access PowerPivot workbooks in the browser without having to download workbooks and data to every workstation.

Increase BI management efficiency: Use the PowerPivot Management Dashboard to manage performance, availability, and quality of service. Discover mission-critical applications and ensure that proper resources are allocated.

Provide reliable access to trustworthy data: Take advantage of SQL Server Reporting Services data feeds to encapsulate enterprise systems and reuse shared PowerPivot workbooks as data sources in new analyses.

Reference: www.powerpivot.com

SSRS Performance Optimization and Analysis

2009-10-14T07:17:39.314-07:00

This article demonstrates the process of debugging performance issues of SSRS reports at the client end. When everything seems okay right from the execution of stored procedures related to the report, to network and report server configuration, one is required to look into what's going over the wire i.e. what's going out and coming in from the client network. This issue is not specific to SSRS reporting. In any web-based server side application development, when the web application is to be debugged for performance issues or even when it needs to be benchmarked for certain parameters, this method is used. And the method is placing an HTTP Proxy or HTTP Sniffer.

This tool can be installed on any of the client machines, and any HTTP activity effective due to report execution on the client machine and network can be logged and investigated. The most promising features of this tool are, firstly its a freeware. This tool comes with several add-ons which are very much suited for analyzing the content and requests that flow over the wire, with all the minute details that can possibly be captured. It's a very nice tool for performance debugging and in my viewpoint, BIDS should have shipped with this kind of tool. This tool is developed using Microsoft .NET and also provides the flexibility to extend the code.

Thumbs up for this tool and the article which itself is a great tutorial explaining the methodology of performance debugging of SSRS reports from a client network.

Reference: Tom Mills Blog

Use of Data Source Views in SSIS

2009-10-13T16:42:06.690-07:00

Less often I have seen Data source Views (DSV) getting used in an SSIS solution. The biggest trait of DSV is that, once its defined, it doesn't get refreshed by itself. In a way it is desireable, and in a way it is not. This is harmful in the way that, if the underlying source structure has changed, there would be no notification to the package of the same, until and unless you explicitly hit the refresh button. But once refreshed, it catches all the changes from the underlying data source and updates the view.

The main advantage of using a DSV in a SSIS solution would be centralizing the connection logic and the scope of database objects to be used. All the packages in the solution can use the same DSV and connect to objects limited to the scope of the DSV. In case of change in logic, it would get easily cascaded to the respective packages using this DSV. Also one DSV would always run under one connection object, which would mean that by enforcing the use of DSV, uncontrolled creation of private database connections would be implicitly controlled. Also at times, it help in continued package development even if Server is offline or disconnected.

And if one needs to connect to some database objects that one would not wish to include in the DSV, a connection can always be created using the Shared Data Source created for the DSV. This would be a direct connection to the database, without any kind of filtering of database objects that a DSV would have. All in all, DSV is a good practice to centralize and control connection usage in a SSIS Solution.

Common mistakes or errors while working with Derived Column transformation component

2009-10-06T12:39:50.603-07:00

2) When you change an existing formula, the data-type is automatically changed by this component to Unicode until and unless it is specifically casted to ANSI. And this sudden change can break components ahead in the flow like Sort for example, which detects any metadata changes. A better solution can be to edit the formula in the Advanaced Editor to get rid of this buggy behaviour of this transform. This specially is the case in SSIS 2008.

3) While developing a formula, many get confused with the return types. Make sure that across the formula, return type should be of the same data type irrespective of the condition in the formula. Even if the intention is to return NULL, just cast the NULL to specific data type that the other part of the logic is returning.

Data Cleansing Tutorial : What is Data Cleansing

2009-10-04T16:07:54.979-07:00

When is Data Cleansing required ?

Generally Data Cleansing is required in a systems integration flavor of project. For example when different systems which might be logically related like back-office and front-office systems, or non-related systems like different business application that might be independent in its own respect but functioning under a common umbrella. In both these examples one thing is common, these systems operate differently and are subject to integration.

What is Data Cleansing ?

Data is the above mentioned systems are not always as expected, or the data is not of the quality that is required to be fed into new system. For example, there can be OLTP applications or front-office systems where data is not fed in the precise manner due to operator error or limited system design. So these data issues needs to be fixed, mostly in the host or intermediate system which is mostly effective to the requirements of the new system where data of a particular quality level is required.

How to do Data Cleansing, using SQL Server in specific ?

There are different ways of doing Data Cleansing, and there is not particular theory or formula to it, in the best of my knowledge. But one thing that applies universally to any data cleansing exercise is, the data should be profiled thoroughly. Thorough data profiling is directly proportional to level of data cleansing. Mostly the hard part is data profiling, than data cleansing. It takes more business knowledge combined with technical analysis of the data for profiling. Once the details about what to fix in the data is catalogued, it's generally not that hard to fix those issues using ETL tools like SSIS. SSIS comes with a dedicated task for Data Profiling and a dedicated tool for viewing the profiled data.

Different ways of importing Data into SQL Server for Data Migration

2009-09-28T08:41:12.857-07:00

SQL Server 2008 Import Export Wizard Limitation for Date Conversion

2009-09-24T14:54:53.799-07:00

I feel that while designing of Import Export wizard, equal importance has not been given to each data types. For example, there are properties to adjust the precision and scale when numeric data types are selected. But when datetime data type is selected, there should be option to specify the style to use while making the conversion during the import process. This feature is absent and while making text file imports for example, the dates would be imported and converted to mm/dd/yyyy format i.e. US Standard (provided the installation language is US English). But if the source data contains dates in dd/mm/yyyy format i.e. British Standard, there is no means to specify within the wizard, what format or standard should be used for conversion. This can be a great shortcoming at times.

The workaround can be use another tool, or import the same as Varchar column, and convert is using the Convert system function before using the field. I know that it's not a great solution, but because of the limitation that exists on the date format conversion using this wizard, it becomes a SISO show (Shit In Shit Out). Still it's not that bad and the solution is workable. I hope that Microsoft realizes this shortcoming and adds this feature in a service pack, instead of the next release.

BTW, if anyone is aware of a better solution or would like to correct me on this, I would be glad to learn the same. I might not be completely updated in the usage of SQL Server Tools and it can be my ignorance, but to the best of my knowledge, this shortcoming exists in SQL Server 2008 Import Export Wizard.

Free Ebooks , Study Guide and Interview Questions on SQL Server Security, Performance and Administration

2009-09-23T04:29:53.162-07:00

Ebook on SQL Server Execution Plans (Dissecting SQL Server Execution Plans.pdf)
Takeaway: In depth technical explanation on SQL Server Query plan execution

Ebook on SQL Server XML Schema Collections (The Art of XSD.pdf)
Takeaway: Technical content to master your skills on usage on XML and XSD with SQL Server

Ebook on SQL Server Interview Questions (SSC Stumper Vol 5.pdf)

Takeaway: Interview questions, answers and links to reference material put together by Red Gate and SQLServerCentral.com. It's one of the best SQL Server Interview Material, that I have came across on the web which is available in such an informative and structured manner for free.

Ebook on SQL Server Security and Encryption (Protecting SQL Server Data.pdf)

Takeaway: Best technical material available on the web for free on SQL Server Security and Encryption.

Download Courtsey: www.red-gate.com

Download Free SQL Server 2005 Ebooks , Download Free SQL Server 2008 Ebooks

2009-09-22T17:19:31.207-07:00

This marketing strategy is keeping this company closer to the SQL Server community of professionals and also it shows how well this company has reach and insight into the product. Though I have not used many of SQL Server tools, as they are more concentrated on a DBA or Developer level of profile, and I work more onto the Business Intelligence side of SQL Server, but I am confident that they are one of the healthy market competitors.

Below is one of the EBook Bundle available for free from RedGate.

Free Ebook on DBA Best Practices (DBA_Best_Practices_ebook.pdf)

Takeaway: A kind of cramsheet of best practices for a SQL Server DBA

Free Ebook on SQL Server 2008 New Features (Brads Sure Guide to SQL Server 2008.pdf)

Takeaway: Higher level introduction to almost all the new features of SQL Server 2008 with examples

Free Ebook on Mastering SQL Server 2005 or SQL Server 2008 - Profiler Tool (Mastering Profiler eBook.pdf)

Takeaway: In depth technical content on the Profiler Tool

Free Ebook on How to be an Exceptional DBA by Brad McGehee (Exceptional DBA 2ndEd.pdf)

Takeaway: How to grow your image and career as a DBA, with complete focus on softskills

Free Ebook on SQL Server Tools and How to do with SQL Server (SQL_Server_Tacklebox_Ebook.pdf)

Takeaway: A SQL Server cookbook that shows how to use different SQL Server features and tools for different purpose

Download Courtsey: www.red-gate.com

Hardware configuration for SSIS and SSAS on SQL Server 2005 and SQL Server 2008

2009-09-17T16:00:21.864-07:00

If you are a blogosphere bee, you would definitely be knowing the new news of MS BI town. If no is the answer, keep reading ahead. If you are working on estimation of implementation of any ETL or Data warehousing solution, one is often confronted with the issues and questions about hardware estimation and sizing. As I had mentioned in my previous posts, Project Real implementation and the whitepaper published my microsoft on the project configuration where they loaded 1 TB in 30 mins, configuration can be nice-to-have reference points. But they are more inclined from a very high performance point and not from the aim of a typical production system.HP has come out with a new set of whitepapers and a tool which they claim can help in making recommendations for the hardware configuration required for Data warehousing and ETL. Based on the features that the tool claims to provide, it seems to be nice tool and can really provide informed recommendations about the hardware configuration, and the greatest point is that the tool comes from the experts of hardware. On the product page of this tool, they claim that the tool would model configurations for:1. ETL (Extract, Transformation, and Load) layer using SQL Server Integration Services 2. OLAP cube build using SQL Server Analysis Services 3. Queries accessing data used in a data warehousing ROLAP (Relational Online Analytical Processing) environment 4. Queries executed against OLAP cubes using SQL Server Analysis ServicesFollowing downloads are available from the HP site: HP Business Intelligence Sizer for Microsoft SQL Server 2005/2008 http://h71019.www7.hp.com/ActiveAnswers/us/en/sizers/microsoft-sql-bi.html HP Whitepapers on SQL Server 2008 Data Warehousing / Business Intelligencehttp://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-5263ENW.pdf http://h20195.www2.hp.com/V2/getdocument.aspx?docname=4AA2-8173ENW.pdf http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-7162ENW.pdfReference: Benjami Wright Jones Blog[...]

How to store files in SQL Server 2008 using ASP.NET

2009-09-14T02:15:47.104-07:00

PCI Audit , C2 Compliance and Common Criteria Compliance in SQL Server 2008

2009-09-07T15:59:50.063-07:00

How to read a zip file using SSIS or unzip a zip file using SSIS

2009-08-28T06:17:08.492-07:00

But I was really surprised, when I found that it's not the case. Author has used a Visual J# library (vjslib.dll) to read the zip file, which ships as a part of Visual J# Redistributable Package. Before a couple of years, I had worked for a few weeks in Java to help out a project and from that experience I knew that Java Runtime Engine probably comes with this kind of libraries to read .jar files.

It's a brilliant discovery, and serves a great purpose. By this functionality, one can download archived data from web, unzip it and process it to it's destination. Downloading data using a web-service and processing is very much possible using SSIS, but the missing bridge was unzipping files using SSIS without using 3rd party components. And now the bridge is ready !!

The library comes for free, and the author has published a nice step by step tutorial describing the implementation of the same. The entire article can be read on this link.

Dimensional Modeling Tutorial : Employee Dimension

2009-08-27T06:30:22.061-07:00

Articles on the Kimball University website or other related websites like Intelligent Enterprise for example are some of the best sources who wish to start or improvise their knowledge on dimensional modeling. This even applies to me. I believe it's a matter of experience, by which your skill improvises in this area, not by just reading a book or webcast.

In dimensional modeling, generally we work on at least some of the common entities which generally exist in any regular business application, for example : Materials, Customers, Employees, Payroll, HR etc.

There's a good article published recently which can server as a good tutorial on how to architect dimensional model for Employee by Kimball University on IntelligentEnterprise.com. I believe that this article presumes some level of data warehousing knowledge and understanding from the reader.

I suggest, if one wants to take the first step on dimensional modeling, then one should read this article. Thereafter, being an MS BI professional, I would implement the same using SQL Server 2008 and SSAS, populate the relational data warehouse and cube and attempt experimenting different permutations and combinations in the model to experience the various technicalities of dimensional modeling.

What is project Gemini

2009-08-25T17:05:40.099-07:00

1) It has a very very strong integration with Microsoft Excel and it's the primary runway from where Gemini takes it's flight to the Business Intelligence space. It is also heard to have nice integration with Sharepoint and other report authoring or rendering tools.

2) It's heard to be an in-memory storage form of Analysis services, i.e. it can be said as an another compound of analysis services. In fact data stored in Excel via Gemini add-in for excel is stored in the form of a .AS database, which means that it can be consumed by any applications which can consume data from analysis services. I may not be precise in the above statement, but the summary is data projected by gemini to its host applications can be or rather would be in the form of an analysis services database.

3) Gemini add-in for Excel is one of the fantastic ways for business users to get the most out of Excel, still having the capabilities of the likes of any data warehouse analysis hosted in a dashboard hosting environment like performance point. Here Gemini is the analysis tool, Excel can store the data in place of data warehouse and Excel charts / graphs act as the dashboard or visualization holders. Also Gemini add-in makes use of Silverlight for it's visualizations.

4) This tool as heard and discussed by industry experts, is a Microsoft's competition to tools like QlikView in the self service or I would like to use the word "portable" business intelligence arena for business (non technical) users. The advantage that gemini has, is the immense propagation of Microsoft Excel in the industry with its added popularity among business users, and its tight integration with the same.

5) Below is a link of few videos which provides preview of project Gemini:

http://blogs.msdn.com/bi/archive/2009/05/04/preview-of-project-gemini.aspx

Using SSIS with SAP

2009-08-18T16:51:05.233-07:00

1) An SSIS package designed for IDocs would be much simpler to understand and design compared to the same for Screen recorded sessions.

2) Always sort the data after reading (or you can also use the IsSorted property setting) and sort the data before you write it down to the file output.

3) Audit the data to the necessary and sufficient level. More auditing would break a developers shoulder and less would break the functional / technical SAP consultant's shoulders.

4) Never let your main package read the data directly from file input. Stage the data into a relational storage like a table. Also never read directly from the table. Always keep an interface like a stored procedure or a view from which your package should read. This design would have the advantage of more resistance towards changes, which is the theme of any such project.

5) Always keep the main control in the database which designing the architecture, for eg. configuration settings, loop iterator collections, file paths etc.

6) Generally data objects like Materials, Customers, Vendors, Finance, Payroll, Employee, etc... are the kind of data objects that gets designed in the SAP system. The first three are the foundation stones to the best of my SAP knowledge till date.

7) Try to use less variety of data types, and the rule of best fit data type does not apply to data migration project. More the synchronization you have at your relational staging area, the less pain you would have while designing your package.

8) Be well versed with casting operators and functions, as generally you would require to use them a lot in such projects.

9) Import - Export wizard can be a nuisance if the text file from which you are importing the data have duplicate column names. If you are working with text files as input source files, make sure you do a basic minimum profiling of the source files.

There are many more points worth mentioning, but for now this is just a quick starter.

Why only one of my package hangs out in BIDS , while other packages run faster ?

2009-08-10T08:33:18.866-07:00

So in essence, the time taken is more. And the time taken is for the entire process and not just placing of control on the package, though it might seem that just placing of control on the package is taking a lot of time.

The resolution is, disable all the control flow elements that have errors due to logical reasons or due to incomplete configuration. Unfortunately as there is no disable functionality in the data flow, remove all the tasks that have an error due to known reasons. Save the package, and try to execute it again. Now the package designer in BIDS should run much faster than what was the case earlier, provided you have quite a few erroneous tasks on the package.

Database Modelling Reverse Engineering , LINQ to SQL - Entity Developer Express

2009-08-27T06:33:08.944-07:00

Coming to the point, there is new tool out called Entity Developer Express. This is a tool not exactly for MS BI or SQL Server development or maintenance. But I am listing it as it's foundations are laid on SQL Server. While working on projects that have DotNet, SQL Server and/or Microsoft Business Intelligence features involved, this kind of tools makes a developer life easy.

I have neither evaluated this tool, nor I am going to do it as presently I am working on system integration project involving SAP and SSIS. So to be honest, no time and less interest to evaluate it.

The description on the product page is quite sufficient to describe itself, which is as below:

Entity Developer is a free of charge powerful modeling and code generation tool for ADO.NET Entity Framework and LINQ to SQL. You can design an entity model from scratch or reverse-engineer an existing database, in which case you will have the classes and the relations with just several clicks. The model is used to generate C# or Visual Basic code with flexible predefined code templates. You can also customize the templates to fit all your needs. Entity Developer for SQL Server uses standard Microsoft SqlClient, LINQ to SQL, and Entity Framework, all of them bundled with .NET Framework 3.5 SP1.

Key Features:
- ADO.NET Entity Framework support
- LINQ to SQL support
- Visual schema modelling
- Customizable and intuitive user interface
- Reverse engineering (database object to entity model)
- Well-thought predefined templates for class generation
- GUI for predefined templates
- State-of-art diagram with zooming
- Diagram overview and printing
- Undo/Redo support
- One-click help system
- Visual Studio is not required
- Free of charge

Comments (0) - Read and Add your own Comments