Hi Folks,
This post is part of Series Business Intelligence – Tools & Theory
Currently running topic for this series is listed as below :
Series Business Intelligence – Tools & Theory
>>Chapter 1 : Business Intelligence an Introduction
>>Chapter 2 : Business Intelligence Essentials
>>Chapter 3 : Business Intelligence Types
>>Chapter 4 : Architecting the Data<You are here>
Continuing from my previous post on this series, If you have missed any link please visit link below
In this article we are going to cover
- Data Reporting and Query Tools
- Data Partitioning
Data Reporting and Query Tools
In today‟s challenging marketplace, it is essential that decision-makers throughout an organization have access to relevant, accurate and timely reports regarding their operations. Getting the data is the one of the capability of business query tools. Presenting and formatting the data in a meaningful way is referred as data reporting. The term querying and reporting can be interchanged since in business data reporting and query tools have the capabilities to get data and format it to get the required report. Data reporting and query tool gives the user a power to query complex and generate the report rapidly.
Query tools enable the user to explore the data through user friendly exploration interface.
These tools are typically:
· Interactive.
· Ad Hoc.
· Driven By Spontaneous User Questions.
· Display Lower Volumes of data.
The familiar patterns provided by query tools to view data are as follows:
· Spreadsheet
· Drill Down
· Roll Up
· Pivot
The names given below are the some of the examples of query tools:
· Access and Excel: Microsoft
· ProClairity: Microsoft
· Business Query: Business Objects (now SAP)
· Report Net: Cognos
Data reporting tools are used to produce the outputs that can be stored and reviewed by the user. The reports generated by the data reporting tools are often produced on time schedule such as weekly and monthly.
· Reporting tools are typically:
· Less Interactive.
· Less Ad Hoc.
· Report View of Data (Header and Detail).
· Driven By Pre-established user questions.
· Display Moderate Volumes of data.
The names given below are the some of the examples of reporting tools include:
· Access: Microsoft
· Crystal Reports
· Managed Reporting Environment: SolutionsIQ
· SQL Server 2005 Reporting Services: Microsoft
There are number of data report and query tool vendors in the market. There are certain specifications on which an organization should concentrate before buying a data reporting and query tool. They are:
· Data source connection capabilities: There are two types of data sources in general. They are the relationship database source and OLAP multidimensional data source. In today business, the user requires both the data sources. Therefore, a user has to ensure the tool supports both the data sources.
· Scheduling and distribution capabilities: The data reporting and query tool must have scheduling and distribution capabilities to meet the needs of senior executives of the organization to satisfy their business intelligence needs. For example, weekly report can be scheduled to run on every Monday morning and the resulting report can be distributed to the senior executives either by email or web publishing. The tool vendors says that the tool can send report through various interfaces, but ensure that it can send through emails as it is the basic requirement of any organization.
· Security Features: Data reporting and query tools are geared to a number of users. In this case, it is very important to make sure that people see only what they are supposed to see. Security resides at the report level, folder level, column level, row level, or even individual cell level. A tool must have this feature and also it has a security layer that can interact with the common corporate login protocols. There are cases where large organizations have developed their own user authentication mechanism and have a single sign-on policy. In such cases, a seamless integration between the tool and the in-house authentication is required.
A data reporting and query tool must be compatible with this security feature.
· Customization: Every one of us spends a lot of time tinkering with some office productivity tool only to make the report or presentation look good. This way of working is definitely a waste of time, but unfortunately it is a necessary task to accomplish. Analysts who spend most of their time on making reports wish to take a report directly out of the reporting tool and place it in their presentations or reports. If a data reporting and query tool provides an easy way to pre-set the reports to look exactly the way that adheres to the corporate standard, it makes the analysts jobs much easier, and the time savings are tremendous.
· Export Capabilities: The report produced by the good report tool should be compatible to export it to the most common export needs of an organization like Excel, flat file, and PDF. For Excel, if the situation warrants it, the user has to verify the reporting format, not just the data itself, has exported out to Excel. This process is a time-saver.
· Integration with the Microsoft Office environment: As most people are used to work with Microsoft Office products, especially Excel, for manipulating data. People used to export reports into excel and then perform additional formatting tasks. Now some reporting tools offer a Microsoft Office-like editing environment for users, so all formatting can be done within the reporting tool itself, with no need to export the report into Excel. This is a nice convenience to the users.
Data Partitioning
Data partitioning is a process of logically and physically dividing data into segments that are more easily maintained and accessed. Data partition helps in performance and utility processing. Data partitioning is very useful in facilitating the efficient and effective management of huge relational data warehouse. It determines the data subject, data occurrence groups and data characteristics that are required at each data site. Data partition is an orderly process that allocates data to data sites that is carried out in same data architecture.
Data partitioning is a complex process which has several factors that can affect partitioning strategies. This also affects the design, implementation and consideration of management in a data warehousing environment.
A data warehouse that is power-driven by a relational database management system provides a comprehensive source of data and infrastructure to implement business intelligence solutions. Implementation of database for a data warehouse involves creation and management of dimension tables and fact tables. A dimension table and fact table provide details about the attributes used to describe the business facts. Dimension table is smaller in size compared to a fact table. The fact tables should be updated periodically using the most recently collected data from the various data sources.
As we know, a data warehouses need to manage and handle high volumes of regularly data updated and hence it requires careful long term planning. The factors to be considered for long term careful planning of data warehouses are data volume, data loading window, index maintenance window, characteristics of work load and data aging strategy.
There are two approaches to implement a relational data warehouse namely:
· Monolithic approach
· Data partitioning approach
· Monolithic approach contains huge fact tables those are difficult to manage. Data Partitioned approach offers various advantages to implement relational data warehouse. It is easy to implement and has provides efficient maintenance. Data in the database grows as the organization grows. The critical data is essential in data warehouse and it should be accommodated in a small database maintenance window which is indispensable.
· Data partitioning serves the need of small database maintenance window requires in a large business organization. The issues pertaining to the support large database can be solved by using data partitioning approach where the large data is decomposed smaller partitions which results in better management. Data partitioning approach to relation data warehouse also results in faster data loading, easy monitoring of aging data and efficient data retrieval system. In relational data warehouse business, data partitioning can implemented by objects partitioning of base tables, clustered and non-clustered indexes, and index views. Table partitions are referred by range partitions which are defined by a customizable range of data.
Hope you will like Series Business Intelligence – Tools & Theory series !
If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !
So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!
Happy Learning and Sharing !!
For More information related to BI World visit our all Mentalist networks Blog
SQL Server Mentalist … SQL Learning Blog
Business Intelligence Mentalist … Business Intelligence World
Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ
MSBI Mentalist … MS BI and SQL Server
NMUG Bloggers …Navi Mumbai User Group Blog
Architectural Shack … Architectural implementation and design patterns
DBA Mentalist …Advance SQL Server Blog
MVC Mentalist … MVC Learning Blog
Link Mentalist … Daily Best link @ your email
Infographics Mentalist … Image worth explaining thousand Words
Hadoop Mentalist … Blog on Big Data
BI Tools Analysis … BI Tools
Connect With me on
| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |
(B)usiness (I)ntelligence Mentalist 