Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions. BI applications include the activities of decision support, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining. In particular to Microsoft, it supports 3 types of OLAP technology, they are
1. MOLAP: Multidimensional OLAP
2. ROLAP : Relational OLAP
3. HOLAP: Hybrid OLAP
I shall post these definitions in my further posts.
As promised in my previous post please find the definitions, PLUS some more information on the characteristic nature of the post title.
This is the traditional mode in OLAP analysis. In MOLAP data is stored in form of multidimensional cubes and not in relational databases. The advantages of this mode is that it provides excellent query performance and the cubes are built for fast data retrieval. All calculations are pre-generated when the cube is created and can be easily applied while querying data. The disadvantages of this model are that it can handle only a limited amount of data. Since all calculations have been pre-built when the cube was created, the cube cannot be derived from a large volume of data. This deficiency can be bypassed by including only summary level calculations while constructing the cube. This model also requires huge additional investment as cube technology is proprietary and the knowledge base may not exist in the organization.
The underlying data in this model is stored in relational databases. Since the data is stored in relational databases this model gives the appearance of traditional OLAP’s slicing and dicing functionality. The advantages of this model is it can handle a large amount of data and can leverage all the functionalities of the relational database. The disadvantages are that the performance is slow and each ROLAP report is an SQL query with all the limitations of the genre. It is also limited by SQL functionalities. ROLAP vendors have tried to mitigate this problem by building into the tool out-of-the-box complex functions as well as providing the users with an ability to define their own functions.
HOLAP technology tries to combine the strengths of the above two models. For summary type information HOLAP leverages cube technology and for drilling down into details it uses the ROLAP model.
Comparing the use of MOLAP, HOLAP and ROLAP
The type of storage medium impacts on cube processing time, cube storage and cube browsing speed. Some of the factors that affect MOLAP storage are:
Cube browsing is the fastest when using MOLAP. This is so even in cases where no aggregations have been done. The data is stored in a compressed multidimensional format and can be accessed quickly than in the relational database. Browsing is very slow in ROLAP about the same in HOLAP. Processing time is slower in ROLAP, especially at higher levels of aggregation.
MOLAP storage takes up more space than HOLAP as data is copied and at very low levels of aggregation it takes up more room than ROLAP. ROLAP takes almost no storage space as data is not duplicated. However ROALP aggregations take up more space than MOLAP or HOLAP aggregations.
All data is stored in the cube in MOLAP and data can be viewed even when the original data source is not available. In ROLAP data cannot be viewed unless connected to the data source.
MOLAP can handle very limited data only as all data is stored in the cube.
Finally, what we’ve all been waiting for, today the entire next wave of Microsoft Business Intelligence is available in Beta. Grab yourself a copy of Microsoft Office 2010 beta, Microsoft SharePoint Server 2010 beta, and SQL Server 2008 R2 CTP3 and try out the exciting new BI capabilities from Microsoft.
Take a look at the new BI capabilities available with SQL Server in Microsoft website with Master Data Services, the new Report Builder, and more…
Top New Features Create high-performance Analysis Services solutions with optimized cube designers, subspace computation, and MOLAP-enabled writeback capabilities Implement enterprise-scale Reporting Services solutions through new on-demand processing and instance-based rendering Build flexible and effective reports with the new Tablix data structure and rich formatting capabilities Expand reach, and empower more users through optimized integration with the 2007 Microsoft Office system.
With SQL Server 2008 version there are quite a number of new features are added in. Those are listed below. (I know this is pretty old update, but new when SQL 2008 was released).
I had a live scenario where my servers were upgraded with the memory and all my ETL & Replication were totally down. Couldn’t figure out the actual issue and need to reset the whole process and run manually. I have about 6TB of database and 4Bn records. My initial workout with the admin guy is to find out the server problem itself and then came out with the server is kind of parallelized with the faulty motherboard. I have 2 processor and equal amount of RAM supplied to both of them. When the memory was upgraded equally one part of the processor couldn’t recognize the memory and found the faulty motherboard. One side it was good to find out the actual memory in place helped to identify and replace the faulty one. But on the other side the production system was totally disastrous. Then need to configure with the resource governor to distinguish the workload to help more for replication and also the default one. After the resource governor was setup properly, the following query produced the results to show average time take per millisecond.
rpool.name as PoolName,
COALESCE(SUM(rgroup.total_request_count), 0)as TotalRequest,
COALESCE(SUM(rgroup.total_cpu_usage_ms), 0)as TotalCPUinMS,
WHEN SUM(rgroup.total_request_count) > 0 THEN
END as AvgCPUinMS
FROM sys.dm_resource_governor_resource_pools AS rpool LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS rgroup
ON rpool.pool_id = rgroup.pool_id
GROUP BY rpool.name
PoolName TotalRequest TotalCPUinMS AvgCPUinMS
PP_BusUsers 0 0 0
PP_SSUsers 0 0 0
distribution 2613809 30510900 11
default 5730860 179191854 31
internal 0 4966873 0
This article will help you create your a new linked server from one instance to another instance.
1. Connect to the 1st instance of SQL Server.
2. Under “Server Objects” look for “Linked Servers” and right click on it and select “New Linked Server”. A new popup box will appear like the below one.
3. In the linked server text box, type the second instance name.
4. In the server type choose Other data source and then select SQL Server Native Client 10.0.
5. In the product name and data source key in the same instance name that was entered in point 3 above. The entry in point 3 is to make sure that we know what we are connecting to.
6. In the catalog key in the specific database name that you want to connect to.
7. In the security tab, click “Be made using this security context” option and key in a remote login & password that already has access to the second instance. If not available then create a new one and put that information here.
8. Thats it, you are now done creating Linked Server.
If you have any issue in creating a linked server then you may not have enough permission on the instance. Even if you have sysadmin access and have problem creating it then get in touch with me on email@example.com for further information.
All the best!