 siddhumehta
Rating: 62
Member since: 2009-03-05
Feeds: 1
Bookmarks
Categories
|
Preview: Siddharth Mehta's Blog
Siddharth Mehta's BlogThis blog is home to share my experiences, views, learning and findings on SQL Server 2005 & SQL Server 2008, Microsoft Business Intelligence - SSIS, SSAS, SSRS, Performancepoint, MDX and Proclarity. Feel free to contact me @ siddhumehta_brain@yahoo.co.inUpdated: 2009-11-21T02:49:18.209-08:00
Powerpivot Books , Training , and Installation 2009-11-19T07:19:30.947-08:00 Powerpivot resources are now out and available for public download. The long awaited learning material on Powerpivot is now available online on BOL, and can be accessed from here. Download instructions and links to download locations for different flavours of Powerpivot can be accessed from here. Those who are not able to download and install Powerpivot (as it also requires installation of Office 2010 beta in one or another way), need not to get disappointed. A Virtual Lab of Powerpivot for Excel 2010 Introduction is available from Microsoft. Aspirants can use this lab, and get their hands-on this lab to get the feel of Powerpivot without bothering about any download or installation. This Virtual Lab can be accessed from here. 
Microsoft Business Intelligence ( MS BI ) products on 64 bit / x64 2009-11-18T16:30:56.627-08:00 Today all the 2010 version of products got released, and made available for public download. If you carefully study the preliminary requirements of the new line of products, most of them are shifting towards the primary requirement of 64-bit. Let's start one-by-one. Sharepoint Server 2010 contains Performancepoint Services for Sharepoint, which is the performance measurement or dashboarding solution candidate from Microsoft. Also I anticipate that it will also contain Powerpivot Services for Sharepoint (PSS). Sharepoint Server 2010 has the below mentioned preliminary requirements:
From the above set of requirements it's very easy to make out that if one wants to have Performancepoint Services and/or Powerpivot Services for Sharepoint or even just Sharepoint Server 2010 for hosting of it's dashboarding solution, the entire landscape of Microsoft BI line of products used in the solution including the Operating Systems needs to be 64-bit. Also the hardware needs to be 64-bit compliant. If the above is not enough, there's still more left. I recently came to know from one of the threads on SSIS forum that Master Data Services, which is Microsoft's new offering in SQL Server 2008 R2 (and is expected to be released in the coming quarter) for Master Data Management is going to be 64-bit only. So for aspirants who are keen on getting their hands-on the new products are left with fewer options to try out all these new features, as most of us do not have 64-bit operating systems or hardware within our accessibility. The only hope is that, if Microsoft releases a new Microsoft Business Intelligence VPC that comes loaded with all these softwares, like the previous ones. 
Powerpivot Data Analysis Expression ( DAX ) Functions PDF 2009-11-13T07:04:36.430-08:00 Data Analysis Expressions ( DAX ) is the new query language or expression language of Powerpivot. It also has a rich set of functions which are almost similar to Excel functions. A dictionary of all the DAX functions are available for download from PowerPivot-info.com. Most of the functions sound very similar to Excel, except the Time-Intelligence functions. The functions listed in the Time-Intelligence section looks very much aligned towards the structure and usage that we find in the Date / Time dimension. I wonder why all this information is not available on MSDN yet, or at least I am not able to locate down on MSDN. 
Powerpivot Client Architecture 2009-11-12T15:41:45.741-08:00 With Powerpivot now available to users, more information about architecture and theory revolving it is emerging out slowly on Blogosphere. I recently went thru an article where the author has posted about the client side of powerpivot, which is the powerpivot add-in. Below is the summary of what I was able to extract out of the article, that I felt of interest:
Use this link to read the original article. 
How to generate SSRS Report output by programming using SSIS or .NET 2009-11-11T15:41:36.963-08:00 A new SSIS Task is available on codeplex, named SSIS ReportGenerator Task. As described on it's homepage, the description about this task is : "SSIS Task for SQL Server 2008 to create Reports from a recordset data source." This task wraps a nice functionality of generating report output behind the scenes, without requiring any programming knowledge for the same. It has a nice User Interface available, with option to view the report during task execution. All the options are self-relevant and the author has provided a nice example of how to use the task on the home page. The technique employed by this task to generate report output is quite common in the .NET programming arena. Report Server Web Service endpoints are used for managing objects on the report server and execution of reports using reporting service. ReportService2005 / ReportService2006 Endpoint is used for managing objects on the report server, and ReportExecution2005 Endpoint is used for execution of reports. This component collects all the data from the options selected by the user, and sets it's properties. Value of these properties are in turn passed on to the object of ReportService2005 Endpoint. Finally this object is passed as a parameter to ReportExecution2005 Endpoint and the report is generated programatically using the WebService. Those who are novice to the concept of webservice and end-points in the context of reporting services, start from this link to learn about the same. After the same, the next step can be to learn more about Report Server Web Service end-points and how to create a client for the same. Finally once you master the same, the source code of this task is the final lesson to learn the best way to implement it. The source code of this task is available for download on codeplex. 
Free Tool to synchronize files and folders 2009-11-11T04:50:14.951-08:00 SyncToy 2.1 is a free application that synchronizes files and folders between locations. Typical uses include sharing files, with other computers and creating backup copies of files and folders. In project environments, where data feeds are received in the form of files, in a development environment such tools are quite helpful. Also for general content management, having one such tool which is also FREE is a big boon. Below in an excerpt from the download page: In some cases it is necessary to regularly get copies of files from another location to add to primary location; in other cases there is a need to keep two storage locations exactly in sync. Some users manage files manually, dragging and dropping from one place to another and keeping track of whether the locations are synchronized in their heads. Other users may use two or more applications to provide this functionality. Now there is an easier way. SyncToy, a free PowerToy for Microsoft Windows, is an easy to use, highly customizable program that helps users to do the heavy lifting involved with the copying, moving, and synchronization of different directories. Most common operations can be performed with just a few clicks of the mouse, and additional customization is available without additional complexity. SyncToy can manage multiple sets of folders at the same time; it can combine files from two folders in one case, and mimic renames and deletes in another case. Unlike other applications, SyncToy actually keeps track of renames to files and will make sure those changes get carried over to the synchronized folder. 
New Features in Performancepoint Services 2010 , Difference between Performancepoint Server 2007 and Performancepoint Services 2010 2009-11-09T09:35:11.844-08:00 I worked on a Performancepoint last year for one of the end-to-end cycle of BI Implementation using the Monitoring and Analytics Module. There were quite a lot of shortcoming and it seemed very apparently like an incomplete product, released in a hurry. With the advent of Performancepoint Services in Sharepoint Server 2010, it seems like there are lot of new improvements from a user interface and designing point of view. One of the feature that was very strange was that, the graph that was available had only two colours, and there was no option to add another colours to it. We struggled quite a lot to find out a solution for the same but without any success.
Looking at the new features that are available in Performancepoint Services, the graph control looks much more matured now with the Pie-chart that was missing in the earlier version. Also there are new visualization available for the representation of data, more detailed representation of KPIs and many more. It seems like now Performancepoint has digested Proclarity to a great extent.
For a complete list of new features in performancepoint services 2010, check out this article. 
Free Dashboard Basics Tutorial / Online Training 2009-11-08T09:14:46.274-08:00 For those who are new to Performance Management, there is a FREE and useful course for Dashboard basics. This course would also give insight on how a Performancepoint Dashboard can help business. Check out this online course: PerformancePoint Monitoring and Analytics: Dashboard basics I PerformancePoint Monitoring and Analytics: Dashboard basics II 
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:
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:
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 BIDS and SSMS are not always sufficient for any MS BI Project. Right from the documentation that might be required just for compliance to tools that are able to fill up the gaps that SSMS or BIDS leave behind, a lot of accessories are required for a project for a leveraged development and delivery. MS BI Project Booster is a kind of kit that consists of a collection of tool & utilities (freewares) and douments and/or documenting methods, that is good to have installed or available in advance, that can help any MS BI Team to tackle issues or cater requirements in a speedier manner at various stages of a project development life cycle. It's a resource kit, and can be used at various stages of the project and by the use of the same, I believe that a project can have the benefit of an Agile BI Development from an implementation perspective. 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.
Free E-book : Sharepoint for SQL Server Database Administrators ( DBA ) 2009-11-05T14:01:15.728-08:00 Below is the excerpt from the Microsoft Technet site, where I found the below information regarding the free download of this e-book: This book is a guide for database administrators who implement or maintain Microsoft Office SharePoint Server 2007. It provides guidelines for improving the performance of Office SharePoint Server 2007 solutions that run on Microsoft SQL Server 2008. It also provides information about key considerations for configuring storage and SQL Server 2008 before installing Office SharePoint Server 2007; the required steps in installing Office SharePoint Server 2007 when using DBA-created databases; backup and recovery considerations for Office SharePoint Server 2007, including what can and cannot be protected by SQL Server 2008 backups; and maintaining and monitoring SQL Server 2008 databases that support a Office SharePoint Server 2007 implementation. 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 I started working on a task to create a report that can be used in SSMS to keep track of SSIS packages and profile them. Jamie Thomson has written a code snippet that I would be using as a starting point for this project. In the course of developing this, I encountered my first barrier which I would like to share. Develop any regular report using BIDS 2008 and SSRS 2008. If you use the same report for rendering in SSMS 2008 by using the Custom Reports functionality, the report won't get rendered however simple it would be. I got confused with this behaviour, and started investigating this issue over web. The error that I was getting continuously was "The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition". After spending quite some time, I was able to find an issue on Connect that this is a bug with SSMS. The bug definition is as follows: When I try to run a custom report in SSMS created with BIDS 2008 it fails with the following error message: The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded. (Microsoft.ReportViewer.Common) As of now this bug has no workaround, and the only possible solution is to install BIDS 2005 and even probably SSRS 2005 to develop a report (or if someone has a tool to work with RDL 2005 specifications) side by side to SQL Server 2008. I can't believe, all this to just develop a simple SSRS 2005 report which can be consumed by dumb report control in SSMS 2008. 
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 Most of those who know what is a Dashboard and/or a Key Performance Indicator (KPI), must have read some bookish examples where the book explains that it's similar to the Dashboard available in your car or a flight cockpit. One might have even seen some pictures of what a dashboard would look like, but once you use it you know how management can take Strategic Decisions based on the top-level or summarized data of the organization. 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-11-12T15:27:44.405-08:00 I posted yesterday regarding the entry of a new tool called powerpivot, but I didn't realize that its Gemini. After a post from Chris Webb's blog, I read it from a post on Microsoft Sharepoint Team Blog that at a Sharepoint Conference, they announced that official name for “Gemini” is SQL Server PowerPivot for Excel and SharePoint. 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-11-12T15:27:44.405-08:00 It looks like Microsoft is all set to deliver a new baby in the parlance of Business Intelligence, and its named PowerPivot. Below is an excerpt from the PowerPivot product site: 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 Recently, I came across a very good article on SSRS Performance Analysis by Tom Mills using a tool called Fiddler. As the homepage of this tool states, "Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP(S) traffic, set breakpoints, and "fiddle" with incoming or outgoing data. Fiddler includes a powerful event-based scripting subsystem, and can be extended using any .NET language." 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. 
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 1) Many a times columns are compared with some value to determine a column value. But if the column contains NULL values, this component would break as it would not be able to make a comparison. So always make a check on the column that you are using for comparison that the value is not NULL using the ISNULL function and then place the formula using the logical AND operator. This would make your formula bulletproof. 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 I have worked on data migration projects where I have been a part of data cleansing activities using SSIS. One of the reader of my blog, requested me to post some tutorials on Data Cleansing. So based on my experiences, I would like to answer a few questions on Data Cleansing. The point of interest is "What is Data Cleansing" and then the next question is "How can Data Cleansing be commenced and concluded" ? 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 There are many ways of importing data into SQL Server, depending upon the source from where data is to be imported. When working with legacy systems or non-relational systems like SAP, CRM applications and Mainframe systems, the source is generally text files or feeds which are in textual format. Professionals having knowledge of SSIS can import data with more fine tuning. All the different ways of importing data, with the caveats of each of the methods are listed on an article that I found on lessthanadot.com. I had the opportunity to place the issue of date conversion with Import Export wizard to the database professionals, and this post is one of the best places where this issue can get it's deserved attention. This article would be of most use specifically in a SSIS Data migration project, where you need to put all the options on the table for importing data from source to SQL Server using SSIS as a part of migration strategy. It's a useful post and can come handy while thinking of options in the way one would want to import data during the technical design planning of any solution. The article also provides the statistics of the test results for various volumes of data with all the methodologies followed for importing the data. If the author would had specified the hardware configuration used and with a little bit change in proforma, it would had been a nice tiny whitepaper in itself. Still nice article worth reading along with comments. 
SQL Server 2008 Import Export Wizard Limitation for Date Conversion 2009-09-24T14:54:53.799-07:00 Import Export Wizard in SQL Server 2008 has nice improvements over what was available in SQL Server 2005, but still there's more that can and should be done to cater the requirements of the real world. 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 Another Ebook bundle from Red-Gate.com containing some of the best technical material of SQL Server 2005 and SQL Server 2008 for free. It also includes one of the must have books for those preparing for a SQL Server developer profile interview. Mind it, it doesn't contain an major SSIS, SSAS or SSRS interview questions, except a few questions on SSIS, SSAS, and SSRS questions which may be less than 10 to 15 collectively in number out of the total 100 interview questions. 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 RedGate has a very unique way of promoting it's product range of SQL Server tools and utilities, and I think that it's working quite well too. RedGate is giving out a free short ebook with a few of its tools, and also makes a few ebook bundles available for free on SQL Server. 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[...] |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
http://siddhumehta.blogspot.com/rss.xml
Copyright 2006-2009 Feedage.com LLC
Privacy policy - Sitemap - Press - Terms of Service - Copyright information - Contact Us - About Us - Feedage
Website Design by: JMVMedia.com - Florida Search Engine Optimization
Language Detection Powered by
Privacy policy - Sitemap - Press - Terms of Service - Copyright information - Contact Us - About Us - Feedage
Website Design by: JMVMedia.com - Florida Search Engine Optimization
Language Detection Powered by