What is the role of OLAP in decision - making? What does the term drill mean down in an executive information system

the Short for Online Analytical Processing, a category of software tools that provides analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data. For example, it provides time series and trend analysis views. OLAP often is used in data mining.
The chief component of OLAP is the OLAP server, which sits between a client and a database management systems (DBMS). The OLAP server understands how data is organized in the database and has special functions for analyzing the data. There are OLAP servers available for nearly all the major database systems.

OLAP (online analytical processing) is a function of business intelligence software that enables a user to easily and selectively extract and view data from different points of view. Designed for managers looking to make sense of their corporate data and related information, OLAP tools structure data hierarchically – the way managers think of their enterprises.  But the best OLAP tools also allow business analysts to rotate their views on the information, changing the relationships in order to get more detailed insight into corporate trends and identifiy potential issues and opportunities.

Information Builders' WebFOCUS combines all the functionality of query tools, reporting tools, analytics and OLAP into a single powerful solution with one common interface so business analysts can slice and dice the data and see business processes in a new way. WebFOCUS makes data part of an organization's natural culture by giving developers the premier design environments for automated ad hoc and parameter-driven reporting and giving everyone else the ability to receive and retrieve data in any format, performing analysis using whatever device or application is part of the daily working life.
WebFOCUS ad hoc reporting features allow users to slice and dice data in an almost unlimited number of ways. Satisfying the broadest range of analytical needs, business intelligence application developers can easily enhance reports with extensive data-analysis functionality so that end users can dynamically interact with the information. WebFOCUS also supports the real-time creation of Excel spreadsheets and Excel PivotTables with full styling, drill-downs, and formula capabilities so that Excel power users can analyze their corporate data sources in a tool with which they are already familiar.

A way of viewing related items of a Dimension as defined in a Hierarchy by expanding Members to access a more detailed data range; put simply, this means displaying Child Members associated with a specific Parent Member or Aggregate Member within a defined Hierarchy.

DRILL DOWN/UP
Drilling down or up is a specific analytical technique whereby the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down). The drilling paths may be defined by the hierarchies within dimensions or other relationships that may be dynamic within or between dimensions. For example, when viewing sales data for North America, a drill-down operation in the Region dimension would then display Canada, the eastern United States and the Western United States. A further drill- down on Canada might display Toronto, Vancouver, Montreal, etc.

Explain the advantages of outsourcing computer facilities. Also, explain its major drawbacks. In Indian context.

The advantages and Risks of Outsourcing  Services

Introduction

For small and medium size organizations, there are benefits and risks to consider when outsourcing information technology (IT) infrastructure, support, and maintenance.

If you are a business owner or CFO, you are probably looking at IT outsourcing first and foremost as a cost saving measure. Sophisticated technology and support solutions continue to grow in accessibility, and return on investment (ROI) can be excellent.

If you are a network administrator, you are interested in using tools that can automate repetitive, simple tasks so you can focus your time on higher level projects.

Either way, there are both advantages and disadvantages to outsourcing IT services. Weighing them properly for you specific needs is important for ROI and to guarantee that the tools and support that are in place bring value to your company’s bottom line.

The Benefits of IT Outsourcing

As a business owner or manager, when you have a solid service taking care of IT, it lightens your workload.
For example, if you are using an IT managed service provider, you can pay a flat-fee for all ongoing IT support and service. Your underlying technology platform is taken care of and the price is set, so decisions involved in these areas do not have to be made on a regular basis.

These solutions are designed to be proactive: your service provider will call you about a problem that was prevented rather than you having to call them to arrange service.

Depending on the size of your network, this type of service can save money. If your technology needs are great enough to require at least one full-time staff member for support, then outsourcing to a managed service provider should actually lower your costs.

If you use an outside contractor who comes onsite to do most network support, then you will only have to pay for services rendered. If you are lucky enough to have few problems with your network or your reliance on technology is fairly minimal, then using an outsourced, individual contractor will be the least expensive way to set-up support.

If you are a time-strapped network administrator, outsourcing to a service that has automated, Internet-based support architecture will allow you relegate low-level, time consuming maintenance and support to a data center with tools you would otherwise not have access to.

Security updates, data backup procedures, and low-level user support can all be done using remote access tools. This frees you to take care of internal, proprietary planning and support for your business network.

 drawbacks

The risks involved with outsourcing mainly involve accessibility to service.

If you rely on an outside contractor who generally has to come onsite to support your network, then you live in a world where you simply hope critical services don’t go down at a time when your ability to conduct business will be interrupted.

Busy IT contractors may take days before they can come in to help, and this will likely be unacceptable if the problem is making it impossible for your staff to work. All the money saved by using a pay-as-you-go contractor can be lost with one serious issue within your network.

With remote IT support such as managed services, some business owners are uncomfortable with having less personal touch and immediate feedback. When you have your own network administrator onsite, you can call them into your office and get a report or have questions answered. A remote based service will never be able to match that level of personal touch.

Also, with remote based managed services, Internet conductivity is critical. The entire support structure relies on being able to maintain access to network nodes via the Internet. If conductivity fails, then the necessary maintenance and support cannot be delivered.

main Drawback
With so much IT infrastructure now being delivered via the Internet with significant cost savings, including many of the most critical software applications, most businesses are by default going to be outsourcing at least some of the IT infrastructure.

If you choose to maintain your environment completely onsite (meaning you own and maintain your own hardware and software as well as keep in-house staff) you will keep an element of control over your environment, but it will cost you.  Every year, this option becomes more expensive when compared to outsourcing infrastructure and support.

If you have a very small network environment, you may be able to get away with using an outside contractor for IT support. This will give you the lowest monthly bill unless you have a major issue. Also, you will need a plan in place for how to do business should you have a network failure and have to go an extended period of time before the issue can be resolved.

With advanced data center level support, your business can access technology that is proactive, preventative, and predictable. For network maintenance that must be done on a regular basis, such as security updates and data backup, these solutions are effective, and redundancies with Internet conductivity will greatly minimize the risk of loosing service.

You may loose some sense of control over what is going on with your business IT, but that can also translate into time saved not having to think about it.

 

 

Relational Database Design Tool and Mapping of 4NF And 5NF and ENHANCED ER TOOLS (EER) model Tool Generalisation

ENHANCED ER TOOLS

ER modeling concepts the EER model includes:
1) Subclass and Super class
2) Inheritance
3) Specialisation and Generalisation.
To describe the concepts of subclass and super class first let us revisit the concept of
‘entity’. The basic object that an E-R model represents is an entity, which is a “thing”
in the real world with an independent existence. An entity may be an object with a
physical existence or it may be an object with a conceptual existence. Each entity has
attributes (the particular properties that describe it).

Example : the entity vehicle describes the type (that is, the attributes and
relationship) of each vehicle entity and also refers to the current set of vehicle entities
in the showroom database. Some times to signify the database application various
meaningful sub-groupings of entity is done explicitly. For example, the members of
the entity vehicle are further meaningfully sub- grouped as: Car, Scooter, truck and so
on.
The set of entities in each of the groupings is a subset of the entities that belongs to
the entity set vehicle. In other words every sub-grouping must be vehicle. Therefore,
these sub-groupings are called a subclass of the vehicle entity type and the vehicle
itself is called the super class for each of these subclasses.
The relationship between a super class and any of its subclasses is called
class/subclass relationship. It is often called an IS-A or relationship because of the
way we refer to the concept, we say, “car is-a vehicle”. The member entity of the
subclass represents the same real world as the member entity of the super class. If
an entity is a member of a subclass, by default it must also become a member of
the super class whereas it is not necessary that every entity of the super class must
be a member of its subclass. From the discussion above on sub/super classes we
can say that an entity that is a member of a subclass inherits all the attributes of
9
Relational Database
Design
the entity as a member of the super class. Notice that the type of an entity is
defined by the attributes it possesses and the relationship types in which it
participates; therefore, the entity also inherits all the relationships in which the
super class participates. According to inheritance the subclass has its own
attributes and relationships together with all attributes and relationships it inherits
from the super class.

The process of defining the subclasses of an entity type is called specialisation,
where the entity type is called the super class of the specialisation. The above said
specialised set of subclasses are defined on the basis of some common but
distinguishing characteristics of the entities in the super class. For example, the set
of subclasses (car, scooter, truck) is a specialisation of the super class vehicle that
distinguished among vehicles entities based on the vehicle type of each entity. We
may have several other specialisations of the same entity type based on different
common but distinctive characteristics. Figure 2 shows how we can represent a
specialisation with the help of an EER diagram.



The subclasses that define a specialisation are attached by lines to a circle, which
is connected further with the super class. The circle connecting the super class
with the subclass indicates the direction of the super class/ subclass relationship.
The letter ‘d’ in the circle indicates that all these subclasses are disjoint
constraints.

Attributes that apply only to entities of a particular subclass – such as mileage of
car, stock of scooter and capacity of truck are attached to the rectangle
representing that subclass. Notice that an entity that belongs to a subclass
represents the same real-world entity as the entity connected to super class, even
though the same entity is shown twice − one in the subclass and the other in the
super class. A subclass is defined in order to group the entities to which these
attributes apply. The members of a subclass may still share the majority of their
attributes with the other members of the super class (as shown in Figure 3).


Hence the specialisation is a set of subclasses of an entity type, which establishes
additional specific attributes with each subclass and also establishes additional
specific relationship types between each subclass and other entity types or other
subclasses.

write short note on Merge-Join

The choice of join algorithm is based on the cost estimate. Let us use the following information to elaborate the same:
MARKS (enroll no, subject code, marks):10000 rows, 500 blocks
STUDENT (enroll no, name, dob): 2000 rows, 100 blocks

The merge-join is applicable to equi-joins and natural joins only. It has the following process:

1) Sort both relations on their join attribute (if not already sorted).

2) Merge the sorted relations to join them. The join step is similar to the merge stage of the sort-merge algorithm, the only difference lies in the manner in which duplicate values in join attribute are treated, i.e., every pair with same value on join attribute must be matched

The number of block accesses:

Each block needs to be read only once (assuming all tuples for any given value of the join attributes fit in memory). Thus number of block accesses for merge-join is:

Blocks of STUDENT + Blocks of MARKS + the cost of sorting if relations are unsorted.

Hybrid Merge-Join
This is applicable only when the join is an equi-join or a natural join and one relation is sorted and the other has a secondary B+-tree index on the join attribute.
The algorithm is as follows:
Merge the sorted relation with the leaf entries of the B+-tree. Sort the result on the addresses of the unsorted relation’s tuples. Scan the unsorted relation in physical address order and merge with the previous results, to replace addresses by the actual tuples. Sequential scan in such cases is more efficient than the random lookup method.

write sort note on Indexed Nested-Loop Join

The choice of join algorithm is based on the cost estimate. Let us use the following information to elaborate the same:
MARKS (enroll no, subject code, marks):10000 rows, 500 blocks
STUDENT (enroll no, name, dob): 2000 rows, 100 blocks

Index scans can replace file scans if the join is an equi-join or natural join, and an index is available on the inner relation’s join attribute.

For each tuple si in the outer relation STUDENT, use the index to look up tuples in MARKS that satisfy the join condition with tuple si. In a worst case scenarios, the buffer has space for only one page of STUDENT, and, for each tuple in MARKS, then we should perform an index lookup on MARKS index.

Worst case: Block transfer of STUDENT+ number of records in STUDENT * cost of searching through index and retrieving all matching tuples for each tuple of STUDENT.

If a supporting index does not exist than it can be constructed as and when needed.
If indices are available on the join attributes of both STUDENT and MARKS, then use the relation with fewer tuples as the outer relation.

 Example of Index Nested-Loop Join Costs

Compute the cost for STUDENT and MARKS join, with STUDENT as the outer relation. Suppose MARKS has a primary B+-tree index on enroll no, which contains 10 entries in each index node. Since MARKS has 10,000 tuples, the height of the tree is 4, and one more access is needed to the actual data.

The STUDENT has 2000 tuples. Thus, the cost of indexed nested loops join as:


100 + 2000 * 5 = 10,100 disk accesses

Write Short note on Block Nested Loop Join

The choice of join algorithm is based on the cost estimate. Let us use the following information to elaborate the same:
MARKS (enroll no, subject code, marks):10000 rows, 500 blocks
STUDENT (enroll no, name, dob): 2000 rows, 100 blocks

This is a variant of nested-loop join in which a complete block of outer loop is joined with the block of inner loop.
The algorithm for this may be written has:
for each block s of STUDENT
{ 
     for each block m of MARKS
      {
         for each tuple si in s
           {
              for each tuple mi in m
                {
                   Check if (si and mi) satisfy the join condition if they do output joined tuple to the result
                 };
           };
      };
};

Worst case of block accesses in this case = Number of Blocks of outer relation (STUDENT) ×Number of blocks of inner relation (MARKS) + Number of blocks of outer relation (STUDENT).

Best case: Blocks of STUDENT + Blocks of MARKS

Number of block transfers assuming worst case:

100*500 + 100 = 50,100 (much less than nested-loop join)

Number of block transfers assuming best case:

400 + 100 = 500 (same as with nested-loop join)

Improvements to Block Nested-Loop Algorithm
The following modifications improve the block Nested method:

Use M – 2 disk blocks as the blocking unit for the outer relation, where M = memory size in blocks.

Use one buffer block to buffer the inner relation.

Use one buffer block to buffer the output.

This method minimizes the number of iterations.

Write a short Note On Nested loop join

The choice of join algorithm is based on the cost estimate. Let us use the following information to elaborate the same:
MARKS (enroll no, subject code, marks):10000 rows, 500 blocks
STUDENT (enroll no, name, dob): 2000 rows, 100 blocks

Let us show the algorithm for the given relations.
To compute the theta or equi-join
for each tuple s in STUDENT
{  
   for each tuple m in MARKS
     {
         test s.enrolno = m.enrolno to see if they satisfy the join condition if they do, output joined tuple to the   result.
      };
 };
• In the nested loop join there is one outer relation and one inner relation.
• It does not use or require indices. It can be used with any kind of join condition. However, it is expensive as it examines every pair of tuples in the two relations.
• If there is only enough memory to hold one block of each relation, the number of disk accesses can be calculated as:
For each tuple of STUDENT, all the MARKS tuples (blocks) that need to be accessed.

However, if both or one of the relations fit entirely in the memory, block transfer will be needed only once, so the total number of transfers in such a case, may be calculated as:
= Number of blocks of STUDENT + Number of blocks of MARKS
= 100 + 500 = 600.

If only the smaller of the two relations fits entirely in the memory then use that as the inner relation and the bound still holds.

Cost for the worst case:
Number of tuples of outer relation × Number of blocks of inner relation + Number of blocks of outer relation.
2000 ∗ 500 + 100 = 1,000,100 with STUDENT as outer relation.

There is one more possible bad case when MARKS is on outer loop and STUDENT in the inner loop. In this case, the number of Block transfer will be:

10000 ∗ 100+500 = 1,000,500 with MARKS as the outer relation.