What Is Data Mart In Sql Server

WHAT IS DATA MART?

A data mart is a structure / access pattern specific to data warehouse environments, used to retrieve client-facing data. The data mart is a subset of the data warehouse and is usually oriented to a specific business line or team

A data mart is a subject-oriented database that is often a partitioned segment of an enterprise data warehouse. The subset of data held in a data mart typically aligns with a particular business unit like sales, finance, or marketing. Data marts accelerate business processes by allowing access to relevant information in a data warehouse or operational data store within days, as opposed to months or longer. Because a data mart only contains the data applicable to a certain business area, it is a cost-effective way to gain actionable insights quickly.

In a market dominated by big data and analytics, data marts are one key to efficiently transforming information into insights. Data warehouses typically deal with large data sets, but data analysis requires easy-to-find and readily available data. Should a business person have to perform complex queries just to access the data they need for their reports? No—and that’s why companies smart companies use data marts.

3 Types of Data Marts

There are three types of data marts: dependent, independent, and hybrid. They are categorized based on their relation to the data warehouse and the data sources that are used to create the system.

1. Dependent Data Marts

A dependent data mart is created from an existing enterprise data warehouse. It is the top-down approach that begins with storing all business data in one central location, then extracts a clearly defined portion of the data when needed for analysis.

To form a data warehouse, a specific set of data is aggregated (formed into a cluster) from the warehouse, restructured, then loaded to the data mart where it can be queried. It can be a logical view or physical subset of the data warehouse:

Logical view – A virtual table/view that is logically—but not physically—separated from the data warehouse
Physical subset – Data extract that is a physically separate database from the data warehouse

Granular data—the lowest level of data in the target set—in the data warehouse serves as the single point of reference for all dependent data marts that are created.

2. Independent Data Marts

An independent data mart is a stand-alone system—created without the use of a data warehouse—that focuses on one subject area or business function. Data is extracted from internal or external data sources (or both), processed, then loaded to the data mart repository where it is stored until needed for business analytics.

Independent data marts are not difficult to design and develop. They are beneficial to achieve short-term goals but may become cumbersome to manage—each with its own ETL Tool and logic—as business needs expand and become more complex.

3. Hybrid Data Marts

A hybrid data mart combines data from an existing data warehouse and other operational source systems. It unites the speed and end-user focus of a top-down approach with the benefits of the enterprise-level integration of the bottom-up method.

Data Mart vs Data Warehouse

Data marts and data warehouses are both highly structured repositories where data is stored and managed until it is needed. However, they differ in the scope of data stored: data warehouses are built to serve as the central store of data for the entire business, whereas a data mart fulfills the request of a specific division or business function. Because a data warehouse contains data for the entire company, it is best practice to have strictly control who can access it. Additionally, querying the data you need in a data warehouse is an incredibly difficult task for the business. Thus, the primary purpose of a data mart is to isolate—or partition—a smaller set of data from a whole to provide easier data access for the end consumers.

A data mart can be created from an existing data warehouse—the top-down approach—or from other sources, such as internal operational systems or external data. Similar to a data warehouse, it is a relational database that stores transactional data (time value, numerical order, reference to one or more object) in columns and rows making it easy to organize and access.

On the other hand, separate business units may create their own data marts based on their own data requirements. If business needs dictate, multiple data marts can be merged together to create a single, data warehouse. This is the bottom-up development approach.

	Data Mart	Data Warehouse
Size	< 100 GB	100 GB +
Subject	Single Subject	Multiple Subjects
Scope	Line-of-Business	Enterprise-wide
Data Sources	Few Sources	Many Source Systems
Data Integration	One Subject Area	All Business Data
Time to Build	Minutes, Weeks, Months	Many Months to Years

Structure of a Data Mart

Similar to a data warehouse, a data mart may be organized using a star, snowflake, vault, or other schema as a blueprint. IT teams typically use a star schema consisting of one or more fact tables (set of metrics relating to a specific business process or event) referencing dimension tables (primary key joined to a fact table) in a relational database.

The benefit of a star schema is that fewer joins are needed when writing queries, as there is no dependency between dimensions. This simplifies the ETL request process making it easier for analysts to access and navigate.

In a snowflake schema, dimensions are not clearly defined. They are normalized to help reduce data redundancy and protect data integrity It takes less space to store dimension tables, but it is a more complicated structure (multiple tables to populate and synchronize) that can be difficult to maintain.

Steps in Implementing a Datamart

Implementing a Data Mart is a rewarding but complex procedure. Here are the detailed steps to implement a Data Mart:

Designing

Designing is the first phase of Data Mart implementation. It covers all the tasks between initiating the request for a data mart to gathering information about the requirements. Finally, we create the logical and physical Data Mart design.

The design step involves the following tasks:

Gathering the business & technical requirements and Identifying data sources.
Selecting the appropriate subset of data.
Designing the logical and physical structure of the data mart.

Data could be partitioned based on following criteria:

Date
Business or Functional Unit
Geography
Any combination of above

Data could be partitioned at the application or DBMS level. Though it is recommended to partition at the Application level as it allows different data models each year with the change in business environment.

What Products and Technologies Do You Need?

A simple pen and paper would suffice. Though tools that help you create UML or ER diagram would also append meta data into your logical and physical designs.

Constructing

This is the second phase of implementation. It involves creating the physical database and the logical structure

This step involves the following tasks:

Implementing the physical database designed in the earlier phase. For instance, database schema objects like table, indexes, views, etc. are created.

What Products and Technologies Do You Need?

You need a relational database management system to construct a data mart. RDBMS have several features that are required for the success of a Data Mart.

Storage management: An RDBMS stores and manages the data to create, add, and delete data.
Fast data access: With a SQL query you can easily access data based on certain conditions/filters.
Data protection: The RDBMS system also offers a way to recover from system failures such as power failures. It also allows restoring data from these backups incase of the disk fails.
Multiuser support: The data management system offers concurrent access, the ability for multiple users to access and modify data without interfering or overwriting changes made by another user.
Security: The RDMS system also provides a way to regulate access by users to objects and certain types of operations.

Populating:

In the third phase, data in populated in the data mart.

The populating step involves the following tasks:

Source data to target data Mapping
Extraction of source data
Cleaning and transformation operations on the data
Loading data into the data mart
Creating and storing metadata

What Products and Technologies Do You Need?

You accomplish these population tasks using an ETL extract transform local tool This tool allows you to look at the data sources, perform source-to-target mapping, extract the data, transform, cleanse it, and load it back into the data mart.

In the process, the tool also creates some metadata relating to things like where the data came from, how recent it is, what type of changes were made to the data, and what level of summarization was done.

Accessing

Accessing is a fourth step which involves putting the data to use: querying the data, creating reports, charts, and publishing them. End-user submit queries to the database and display the results of the queries

The accessing step needs to perform the following tasks:

Set up a meta layer that translates database structures and objects names into business terms. This helps non-technical users to access the Data mart easily.
Set up and maintain database structures.
Set up API and interfaces if required

What Products and Technologies Do You Need?

You can access the data mart using the command line or GUI. GUI is preferred as it can easily generate graphs and is user-friendly compared to the command line.

Managing

This is the last step of Data Mart Implementation process. This step covers management tasks such as-

Ongoing user access management.
System optimizations and fine-tuning to achieve the enhanced performance.
Adding and managing fresh data into the data mart.
Planning recovery scenarios and ensure system availability in the case when the system fails.

What Products and Technologies Do You Need?

You could use the GUI or command line for data mart management.

Best practices for Implementing Data Marts

Following are the best practices that you need to follow while in the Data Mart Implementation process:

The source of a Data Mart should be departmentally structured
The implementation cycle of a Data Mart should be measured in short periods of time, i.e., in weeks instead of months or years.
It is important to involve all stakeholders in planning and designing phase as the data mart implementation could be complex.
Data Mart Hardware/Software, Networking and Implementation costs should be accurately budgeted in your plan
Even though if the Data mart is created on the same hardware they may need some different software to handle user queries. Additional processing power and disk storage requirements should be evaluated for fast user response
A data mart may be on a different location from the data warehouse. That’s why it is important to ensure that they have enough networking capacity to handle the Data volumes needed to transfer data to the data mart.
Implementation cost should budget the time taken for Datamart loading process. Load time increases with increase in complexity of the transformations.

Advantages and Disadvantages of a Data Mart

Advantages

Data marts contain a subset of organization-wide data. This Data is valuable to a specific group of people in an organization.
It is cost-effective alternatives to a data warehouse which can take high costs to build.
Data Mart allows faster access of Data.
Data Mart is easy to use as it is specifically designed for the needs of its users. Thus a data mart can accelerate business processes.
Data Marts needs less implementation time compare to Data Warehouse systems. It is faster to implement Data Mart as you only need to concentrate the only subset of the data.
It contains historical data which enables the analyst to determine data trends.

Disadvantages

Many a times enterprises create too many disparate and unrelated data marts without much benefit. It can become a big hurdle to maintain.
Data Mart cannot provide company-wide data analysis as their data set is limited.

WHAT IS SQL SERVER?

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network.

What is SQL and why it is used

?Structured Query Language (SQL) is the standard and most widely used programming language for relational databases. It is used to manage and organize data in all sorts of systems in which various data relationships exist. SQL is a valuable programming language with strong career prospects

WHAT IS DATA MART IN SQL SERVER

MICROSOFT’S SQL SERVER IS A NICE PRODUCT FOR BUILDING OUT YOUR FIRST SIMPLE DATA MART. YOU CAN RUN IT LOCALLY ON YOUR OWN SERVER, OR YOU CAN HOST IT VERY COST EFFECTIVELY AT RACKPACE. AN 8GB (MEMORY), WITH 320 GB OF DISK STORAGE, WINDOWS 64-BIT SERVER WITH SQL SERVER – WEB EDITION (PRE-INSTALLED) WILL RUN YOU $468/MONTH. BASICALLY, $6K A YEAR FOR A BOX THAT YOU DON’T HAVE TO MANAGE OR MAINTAIN.

Why not MySQL? — after all, it’s free. You can just as easily host a MySQL server internally or at Rackspace — where it will cost you a paltry $409/month. MySQL is a fine choice, nothing wrong with it at all.

Having said that, here’s my case for spending the extra $60/month to go with SQL Server.

Active Directory. Chances are pretty good that you’re running a Windows domain at your company and that you are using Active Directory for security and authentication. SQL Server is tightly integrated with Active Directory — so your data mart users can use their pre-existing domain accounts to login to SQL Server.
SQL Server connects very well with other Microsoft Office components. In particular, MS-Query works really well with SQL Server. Users can story queries directly in Excel spreadsheets to pull data from your data warehouse. (MS-Query works with any ODBC data source – so you can do the same thing with MySQL – but it is easier to set up with SQL Server).
SQL Server’s “query” dialect, T-SQL, is a very mature and powerful version of the venerable SQL language. So you can tackle very complex queries and sophisticated multi-step processes directly in your database without having to resort to using external code.
Linked Servers. One of SQL Server’s most powerful features is its’ ability to connect to other 3rd party databases (including any ODBC data sources). This lets you pull data from operational data stores into your data mart (or a staging area enroute to the data mart) from directly within the database. I’ve successfully connected to many, many different external data sources, including legacy ISAM file-based systems, using Linked Servers.
SQL Server Agent. A full-featured scheduling system that is built directly into the database server. You can create complex T-SQL scripts and schedule them to run on just about any timetable that you can imagine. The scheduler can email out success/failure messages and it logs output to standardized windows logs. The combination of linked servers and SQL Server agent provides you with the equivalent of an “application server” within the database.
Alerts. Over time, I think that you’ll find that your data mart becomes a critical element of your computing environment. SQL Server’s alerting system provides a framework for alerting staff when problems arise — things like disks filling up, users getting locked out, etc.
Access to the CLR. Microsoft’s CLR, or “Common Language Runtime”, is the engine that runs C#, ASP.NET and Visual Basic .NET. If you have to, you can write complex code in C# and connect to it from directly within SQL Server.
SSIS. SQL Server Integration Services. SSIS is an advanced toolkit for converting and cleansing data on its way into the data mart. I’ve always found that I can do most of the transformations that I need to do via linked servers, bcp and T-SQL scripts. But, SSIS is a powerful alternative for really complex transformation coding. (SSIS requires at least the “standard” edition of SQL Server). (Take a look at Business Intelligence Studio at the same time).
Advanced analytical capabilities — such as hypercubes and bitmapped indexes. At a certain point you might find yourself wanting do some more advanced analytics, which might require hypercubes and/or bitmapped indexes. You can easily upgrade your SQL Server to the Enterprise Edition to access these features. (Which, I will admit, is a little more pricey).
A wealth of 3rd party tools and products. There a TONS of great 3rd party products that integrate well with SQL Server. For example, DBAmp, from forceAmp, is a SQL-Server based integration engine that connects SQL-Server databases to Salesforce.com instances. I’ve used DBAmp to integrate sales data with production data from our ERP systems inside of a datamart built with SQL Server.

Gartner agrees. SQL Server gets the “Magic quadrant” distinction for operational database management systems.
SQL Server only runs on Windows-based operating systems and some of the advanced management features of the server need to be accessed from the Management Studio which only runs on Windows. However, almost EVERYTHING that you can do from the management console has a T-SQL equivalent — so you can generate T-SQL scripts from the Management Studio and run them from other platforms as you like. For this purpose, I recommend that you get a copy of RazorSQL, a dynamite multi-platform query and analysis tool.

No need to wait. It’s easy and cost-effective to build your first data mart with SQL Server.

Drop your comment

Share on Facebook

Post on X

0Shares

refugeictsolution.com.ng

refugeictsolution.com.ng

What Is Data Mart In Sql Server

3 Types of Data Marts

Data Mart vs Data Warehouse

Structure of a Data Mart