SQL SERVER – Few Notes on Fast Track Data Warehouse

I recently delivered fast track data warehouse training. This training was very challenging as this training requires very specific hardware and extremely different way of looking at data warehousing. While training I have made few notes and I will now share the same notes with you. Please note that this are just notes and not learning material.

fast track DW SQL SERVER   Few Notes on Fast Track Data Warehouse

  • Fast Track Data Warehouse has a primary emphasis on eliminating potential performance bottlenecks.
  • It supports maximum of 48 TB data at this moment.
  • Currently HP, Dell, Bull, IBM and EMC2 provides necessary hardware for Fast Track Data Warehouse.
  • All the Software and Hardware comes in a single package.
  • The Base OS is Windows Server 2008 and Base Database is SQL Server 2008 R2.
  • FTRA stands for Fast Track Reference Architecture.
  • SMP stands for Symmetric Multiprocessing.
  • Fast Track is pre-configured and it just works out of the box.
  • PDW stands for Parallel Data Warehouse.
  • Fast Track Data Warehouse is based on Symmetrical Multi Processing (SMP) and PDW is based on Massively Parallel Processing (MPP).
  • Each CPU Core delivers 200 MB per second data.
  • This hardware architecture is available at $13,000 per Terabyte.
  • It uses RAID 1 Mirror sets
  • Recovery model of FTDW is recommended to Simple Recovery mode.
  • Turn off AutoGrow for user defined database.
  • Leave AutoGrow On for TempDB and set at 4 MB.
  • Update the database statistics regularly.

I really had great time doing training for this subject. If you have worked with Fast Track Data Warehouse, what was your experience?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fast Track Data Warehouse for SQL Server 2008

fast track DW SQL SERVER   Fast Track Data Warehouse for SQL Server 2008

I recently attended a wonderful training session organized by Microsoft on Fast Track Data Warehouse Reference Architectures. If you are regular reader of my blog, you will be well aware of the fact that I am more of the Relational guy than a Business Intelligence professional. I was initially a bit skeptic about this training. However, once I start learning about it, to my surprise, I thought that I am the perfect guy to learn this. In fact, I realized that few of the tricks which this course is suggesting have already been implemented in my earlier consulting assignments.

Fast Track Data Warehouse is a very unique effort by Microsoft, where few Reference Architectures which offer scalability and reliability to huge database are proposed. Fast Track Datasheet provides some excellent points about this reference architecture. I am listing few of the same here.

  • Scale from 4 up to 48 Terabytes using compression capabilities in SQL Server 2008 Enterprise
  • Choose from industry-standard hardware from Dell, HP, Bull, IBM, EMC and other leading vendors
  • Pre-configured servers, storage and networking, specifically balanced and optimized for warehousing
  • Implement an enterprise-class solution for less than one-third the price of a comparable Oracle system
  • Fast Track Data Warehouse has a primary emphasis on eliminating potential performance bottlenecks.
  • Optimized for sequential IO rather than random IO, each Fast Track Data Warehouse is designed to provide up to 200 MB/s per CPU core

The Fast Track approach derives its strength from the advanced data warehouse enhancements included in SQL Server 2008 Enterprise such as compression, parallel partitioning and star join query optimizationfficon SQL SERVER   Fast Track Data Warehouse for SQL Server 2008

    Well, I think it is great effort by Microsoft and I must express deep gratitude to Microsoft for giving me opportunity to learn this unique initiative. I clearly see that in near future, lots of Data Warehousing solutions will be switching to this solution. You can read more about this reference architecture over here. The image used in blog post is taken from MS official site of Fast Track.

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQL SERVER – What is MDS? – Master Data Services in Microsoft SQL Server 2008 R2

    What is MDS?

    Master Data Services helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets company wide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time. (Source: Replace with Microsoft)

    Today I will be talking about the same subject at Microsoft TechEd India. If you want to learn about how to standardize your data and apply the business rules to validate data you must attend my session. MDS is very interesting concept, I will cover super short but very interesting 10 quick slides about this subject. I will make sure in very first 20 mins, you will understand following topics

    • Introduction to Master Data Management
      • What is Master Data and Challenges
      • MDM Challenges and Advantage
    • Microsoft Master Data Services
      • Benefits and Key Features
      • Uses of MDS
      • Capabilities
    • Key Features of MDS

    This slides decks will be followed by around 30 mins demo which will have story of entity, hierarchies, versions, security, consolidation and collection. I will be tell this story keeping business rules in center. We take one business rule which will be simple validation rule and will make it much more complex and yet very useful to product.

    I will also demonstrate few real life scenario where I will be talking about MDS and its usage.

    Do not miss this session. At the end of session there will be book awarded to best participant.

    My session details:

    Session: Master Data Services in Microsoft SQL Server 2008 R2
    Date: April 12, 2010  Time: 2:30pm-3:30pm

    SQL Server Master Data Services will ship with SQL Server 2008 R2 and will improve Microsoft’s platform appeal. This session provides an in depth demonstration of MDS features and highlights important usage scenarios. Master Data Services enables consistent decision making by allowing you to create, manage and propagate changes from single master view of your business entities. Also with MDS – Master Data-hub which is the vital component helps ensure reporting consistency across systems and deliver faster more accurate results across the enterprise. We will talk about establishing the basis for a centralized approach to defining, deploying, and managing master data in the enterprise.

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQLAuthority News – SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update)

    Note: Download SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update) Authored by Microsoft

    SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

    The training kit is designed for the following technical roles:

    • Developers who build applications for the Microsoft platform.
    • Microsoft evangelists, technical specialists and consultants.

    Download SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update)

    Abstract courtesy Microsoft

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQLAuthority News – Links to Book On Line – Data Mining Algorithms (Analysis Services – Data Mining)

    I have quite often received request for the Data Mining Algorithms details. Book Online has wonderful resources for the same. I suggest to read them here.

    Data Mining Algorithms (Analysis Services – Data Mining)

    The data mining algorithm is the mechanism that creates a data mining model. To create a model, an algorithm first analyzes a set of data and looks for specific patterns and trends. The algorithm uses the results of this analysis to define the parameters of the mining model. These parameters are then applied across the entire data set to extract actionable patterns and detailed statistics.

    Read more here.

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQLAuthority News – Webcasts – Resources for IT Managers and their Teams

    Pinal Dave and Jacob Sebastian are both SQL Server MVP are doing webcasts for IT Managers and their Teams.

    Join us for a 4 series webcast as follows:

    itmanager SQLAuthority News   Webcasts   Resources for IT Managers and their Teams

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQLAuthority News – Migrating DTS Packages to Integration Services

    Migrating DTS Packages to Integration Services
    Writer: Brian Knight
    Published: July 2008

    SQL Server Integration Services (SSIS) brings a revolutionary concept of enterprise-class ETL to the masses. The engine is robust enough to handle hundreds of millions of rows with ease, but is simple enough to let both developers and DBAs engineer an ETL process. In this whitepaper, you will see the benefits of migrating your SQL Server 2000 Data Transformation Services (DTS) packages to Integration Services by using two proven methods. You will also see how you can run and manage your current DTS packages inside of the SQL Server 2005 and 2008 management tools.

    Ready white paper Migrating DTS Packages to Integration Services

    Abstract &  courtesy : Microsoft

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQL SERVER – Future of Business Intelligence

    Business Intelligence (BI) is slated to play bigger roles in all kinds of businesses in the coming years. This is not surprising as data analysis and smarter decision making has made the use of BI inevitable in all sizes of businesses across all sectors, including Real estate, IT, mobile devices, governmental agencies, scientific and engineering communities and R&D labs, banking and insurance, to name a few. BI can effectively deal with industry-specific constraints, operations and objectives thereby helping organizations to better understand their customers, optimize their operations, minimize risk, manage revenue, and ultimately improve their results. Moreover, the changing economic environment, which is marked by shrinking budgets, is making way for advancement of successful BI initiatives.

    Business Intelligence Forecasting – Predicting Your Way To Success

    Today, technology has created one solid platform for the world market, eradicating geographical boundaries. The escalating number of consumers with wide-ranging demands and expectations is making it extremely difficult to conduct business. So meeting customer’s demands has become a top-most priority for all organizations to gain competitive advantage. However, with the implementation of Business Intelligence solutions, companies can stay ahead in the race and keep pace with the market trends and more importantly, meet customers’ expectations. BI Forecasting will help them prepare business strategies while keeping in mind the future events by analyzing the available past data.

    Predictive Analytics is the branch of data mining that is used to analyze current and historical data to make predictions about future events. It can help companies optimize their existing processes, well understand customer behavior, spot unexpected opportunities, and anticipate problems before they occur. More and more organizations are realizing the benefits of using data to align their current actions with their future objectives. By incorporating predictive analytics into their daily business operations, they can have better control over the decisions they make every day, which in turn will help them maximize their ROI.

    Business Intelligence – Paving Way For Information Democracy

    The number of BI users has been constantly growing past the traditional IT “power user”, from dozens, to hundreds and in some cases now thousands of users. Soon this number will be in the millions. Earlier, BI was restricted to only statisticians and corporate analysts and only these selected few could access BI. But today, BI is spreading to nearly every strata of organization, as companies attempt to provide critical data to business users who need it to perform their jobs.

    Increasingly, companies are realizing the importance of users having access to timely and relevant insight, which will help in managing performance and aligning them with the mission of the organization. BI is bringing information democracy by providing everyone the insights they require and delivering information to the right people at the right time across the enterprise. More and more organizations are expected to empower their employees with BI for productivity and operational gains.

    Software as a Service Business Intelligence –Broadening Usage of BI

    A rising number of BI vendors are providing BI software as a service (SaaS) or on-demand business intelligence service. SaaS has been increasingly gaining popularity among small and mid-sized companies. It offers companies a practical option to deploy applications that can provide significant business value. With BI SaaS, companies are not required to make major upfront investments in a hardware server and licenses for the BI software, operating system, web servers and the like. In this case, the software provider hosts them over the Internet for a fee, which can be monthly, quarterly or yearly basis.

    The high implementation cost of an end-to-end BI solution was an important factor which discouraged small and mid-sized businesses to adopt BI. With on-demand BI, all sizes of companies can avail the benefits of BI and enhance their business growth. Apart from being cost-effective, it provides several other benefits such as shorter implementation cycles and no maintenance of IT support staff.

    Real-time Business Intelligence – Instant Information For Success

    Over the last few years BI has been gradually growing in importance and in future, organizations will depend only on real-time information related to their business for smarter decision making. Real-time business intelligence can be defined as the process of delivering information on business operations devoid of any latency. Real-time BI disseminates information about a business in a range from milliseconds to a few seconds after the business event. While traditional business intelligence gives users only historical information, real time business intelligence provides a comparison of present business events with historical events, which helps in identifying a range of issues thereby allowing them to resolve it on time. Moreover, the primary aim of real-time BI is to enable corrective actions to be initiated and business rules to be attuned to optimize business processes.

    With consumers demanding faster and improved services from businesses it has become imperative for companies to pay even better attention to consumer satisfaction. They now demand near real-time analysis for intelligent decisions for business success. The rising demand for near-real-time data analysis is making way for a new framework for business intelligence. Business intelligence 2.0 is the recently-coined term to describe the acquisition, provision and analysis of real-time data, which was earlier lacking in BI. BI 2.0 is a part of the constantly developing business intelligence industry and indicates the next step for BI.

    BI has truly empowered every businessperson who can now easily use BI to make better decisions and that too on their own, without relying on IT or power analysts to prepare and interpret results for them. In a couple of years BI applications will become as commonplace as spreadsheet applications within all organizations that are midsize or larger. Organizations making effective use of BI technologies will rise and stay far ahead from their competitors. It is expected that BI will soon replace the gut-feel management with real data-based decision-making. Over the coming years, business intelligence will undergo transformation that will have a broad and lasting impact. It will revolutionize the way that we think about business and the way business decisions are made. It’s only when thoughtful analysis supersedes gut feeling and conventional perception, we will enter the next level of business intelligence that will empower businesses with the capacity to reason, prepare, forecast, resolve issues and innovate.

    Reference: Pinal Dave (http://blog.sqlauthority.com),DNS

    SQL SERVER – Business Intelligence – Aligning Business Metrics

    Today, executive management and managers need the latest information to drive intelligent decisions for business success. More informed decisions mean more revenue, less risk, decreased cost, and improved operational control for business agility and competitiveness. Besides, in today’s fast paced, technology-driven business world, organizations are continually struggling to deal with growing data volumes and complexity to use their own data efficiently. Constrained with competitive environments and data complexity are COO, IT Managers and Business Consultants who are asking for less information more easily for smarter, faster decision-making. They want information that is highly visual, up-to-date, personalized and secure. Also, they want information delivered in line with where and how they work.

    By leveraging on the power of Business Intelligence (BI) organizations can understand and analyze large volumes of rapidly changing data for effective decision-making. BI helps decision makers to harness the advantage of change to create competitive advantages, achieve corporate objectives, and make better decisions, faster.

    Business Intelligence to put it simply, is all about improving decision making within an organization. By presenting the latest information to the right people at the right time the quality of decisions as well as their timeliness can be improved. These days, organizations are embedding BI into business process to create a better workflow, apart from gaining other benefits of BI. In fact, BI can make all areas of business strategic and make them value-rich.

    Business Intelligence

    Proper BI deployment – Aligning all business metrics

    Business Intelligence adds value to all departments or business units within an organization. In general, revenue-generating areas of business such as sales and product manufacturing are often considered critical to a company’s success. While other areas like HR and operations are a requisite to do business but are not considered crucial for revenue generation and for improving business performance. So naturally, when it comes to adding BI value to a business, only revenue-generating departments and areas strike the mind first. However, this is not the case and with appropriate metrics and data this thought process needs to be changed.

    Traditionally, Business Intelligence focused primarily on siloed information. Functional areas like Finance, Sales, Marketing and HR created metrics. To provide value to an organization from top to bottom, metrics must align from top to bottom across business units and do away with silos. Undoubtedly, there is still value in maintaining HR, Finance and Sales-specific metrics; however, if the metrics of all areas of an organization are aligned to corporate metrics and objectives, it can prove to be a potent tool to facilitate both the top and bottom lines of an organization. From line leaders to middle managers to executives, they all must have metrics and dashboards that are congruent and support one another.

    Nowadays, businesses are putting in a lot of effort to come up with smart, intelligent decisions that will help them run a strategic operation and gain competitive advantage. One of the key aspects of proper Business Intelligence deployment is the alignment of metrics from top to bottom across functional areas, which can go a long way in ensuring business success.

    business intelligence graph SQL SERVER   Business Intelligence   Aligning Business Metrics

    Scenario: Business Intelligence in real world

    Let us take a real world scenario and try to understand how proper alignment of business metrics makes all areas of a business more strategic and value-rich.

    In our example, let’s take a car manufacturing company Speed Motor Manufacturing. The top priority corporate objective of the company is to increase its profit margin by 5%. To attain this target, it is important to consider each area of business to help impact the bottom line, including the HR department. In general, HR department is considered important by all organizations; however, it is not considered strategic or a contributor to the bottom line.

    Now, let’s assume that the operation’s business unit of Speed Motor Manufacturing has ascertained that the best way they can contribute to the overall margin objective is to make 20% more use of a new type of machine which can speed up assembling car components, which will in turn improve the speed of manufacturing process and more cars will be produced per day. However, this complex machine cannot be operated by all individuals. It requires specialized training and more experienced users. Now, in order to meet the objective, the new car manufacturing machine requires an increased usage by 20% for the year.

    Here, HR department can play a major role. The HR department ties its goals and metrics to help operations meet their goals, thus directly contributing to the corporate objective of increasing the profit margin by 5%. To meet the objective, HR department must retain the skills required for operating these complex machines, train individuals to operate the new machines and even hire new skills that can operate the new machines. HR’s metrics map specifically to the operations team’s metrics, which map directly to the overall corporate metrics.

    In our example, we saw how metrics are aligned top to bottom and across the organization.

    Having said that, in order to successfully measure these metrics and create dashboards to check advancement and achievement, the data and the data structure must be available and appropriate. If the data structure is present and the objectives are properly aligned, all aspects of an organization can be strategic and contribute to enhance the business performance for assured success. The HR business unit measures the training it conducted, which increased the number of qualified operators of the new machineries. The operations leader measures the number of hours the new machine was operated compared to the previous year. The finance team measures the profitability of the operations in the current year compared to last year and quantifies operations increase in margin. Thus, we see how all the departments work together towards a common objective, set up metrics to monitor and measure their success and link them accordingly.

    In a nutshell, proper deployment of Business Intelligence makes way for perfect objective alignment and improved vision throughout the organization for business success and competitive advantage.

    Reference : Pinal Dave (http://blog.SQLAuthority.com), DNS

    SQL Server – White Paper – An Introduction to Fast Track Data Warehouse Architectures by Erik Veerman

    An Introduction to Fast Track Data Warehouse Architectures
    SQL Server Technical Article
    Writer: Erik Veerman, Solid Quality Mentors
    Technical Reviewer: Mark Theissen, Scotty Moran, Val Fontama
    Published: February 2009

    The performance and stability of any application solution—whether line of business, transactional, or business intelligence (BI)—hinges on the integration between solution design and hardware platform. Choosing the appropriate solution architecture—especially for BI solutions—requires balancing the application’s intended purpose and expected use with the hardware platform’s components. Poor planning, bad design, and misconfigured or improperly sized hardware often lead to ongoing, unnecessary spending and, even worse, unsuccessful projects.

    The ultimate goal of the Fast Track reference configurations is to take the guesswork out of hardware and architectural decisions for the database layer of Microsoft SQL Server-based BI solutions. To help you get started with the Fast Track reference architectures, understand their approach and value, and use them to implement the best possible solution, this paper includes:

    • An overview of the new Fast Track reference architectures
    • A review of BI fundamentals and applicable hardware considerations
    • The tested Fast Track reference architecture components and options
    • Prescriptive guidance for designing and optimizing a solution
    • Resources available to help you choose or create a new hardware configuration

    fasttrackdw SQL Server   White Paper   An Introduction to Fast Track Data Warehouse Architectures by Erik Veerman

    The Fast Track Data Warehouse reference configurations focus on the central database component of a BI solution, commonly called the data warehouse or data mart. These Fast Track reference configurations target solutions on a single server estimated at up to 32TB of data.

    Read An Introduction to Fast Track Data Warehouse Architectures

    Abstract courtesy : Microsoft

    Reference: Pinal Dave (http://blog.SQLAuthority.com)