Excel 2010 - Premier Tool For Business Intelligence

Excel 2010 - Premier Tool For Business Intelligence

By   13:19   No comments:

Every new Office version has a set of themes, and the new features are grouped around those  themes. For Excel 2010, the themes were to improve Excel’s reputation in the scientific community and to make Excel the premier tool for Business Intelligence.

As I have done lots of data analysis in the past and continuing to do that, I love the new features for analyzing data. I also remember that while I loved wrangling large data sets into meaningful analyses, I never wanted to spend the time to make those meaningful analyses look “pretty.” Excel 2010 offers new graphics improvements that make it easy to add some visual interest to your numbers. Follow this link to know more about the new features of Excel 2010.

Here lets see what makes Excel 2010, a better tool for Business Intelligence:

PowerPivot Add-In: You can now sort, filter, and pivot data sets that are beyond 1 million rows. The PowerPivot tool allows you to mash up 100 million rows of data from Excel, text files, RSS Feeds, SQL Server, Oracle, and more. A new DAX expression language offers time intelligence functions that enable you to compare fiscal year-to-date sales with the parallel period from a year ago.

Pivot Table Slicers: Filtering data in pivot tables becomes visual with graphical filters known as Slicers. In previous versions of Excel, the filter drop-downs offered the capability to choose Multiple Items, but no one reading the report could tell what was included or not included. These new graphical filters show what is in the summary report and invite people to do ad-hoc analyses by choosing new options from the slicers.

Asymmetric Pivot Tables: Do you need to show last year’s actuals versus this year’s budget? That was hard to do in previous versions of Excel, but the Named Sets command for pivot tables created from OLAP data make it easy in Excel 2010. Don’t have OLAP? Run your Excel data through PowerPivot to enable Named Sets.

Percentage of Parent Item in Pivot Tables: New calculations in the Show Values As drop-down allow for calculations such as Percentage of Parent row, Rank, and more.

AGGREGATE function: Whereas Excel 2007 added the plural SUMIFS function, the killer function in Excel 2010 is AGGREGATE. This function is like the SUBTOTAL function on steroids. You have 19 calculation options instead of the 11 in SUBTOTAL, plus the capability to ignore hidden rows, filtered rows, or error cells.

And the above discussed 5 points also seems to be an improvement over Excel 2007 and which in turn makes Excel 2010 a better tool for Business Intelligence. Follow this link to know more about the new features of Excel 2010.

Read More

Re-Initialize The Offline Files Cache And Database

Re-Initialize The Offline Files Cache And Database

By   09:17   1 comment:

I have installed Windows 7 (clean 64-bit) in my desktop. Before that I also upgraded my laptop, which has an offline connection with a shared folder on my desktop. Now, after updating my desktop, there is still an offline folder (inactive/greyed/disconnected) in the sync partnership, which points to the old shared folder on my desktop which does not exist anymore! I cannot get back and delete it since that partition on my desktop is gone. Is there a way to delete this connection from my laptop?

Solution:

The steps given below will suggest you to delete the office line folders:

Method 1:

The Offline Files cache is a folder structure located in the %System Root%\CSC folder, which is hidden by default. The CSC folder, and any files and subfolders it contains, should not be modified directly; doing so can result in data loss and a complete breakdown of Offline Files functionality.

1.In Folder Options, on the Offline Files tab, press CTRL+SHIFT, and then click Delete Files. The following message appears:

The Offline Files cache on the local computer will be re-initialized. Any changes that have not been synchronized with computers on the network will be lost. Any files or folders made available offline will no longer be available offline. A computer restart is required.

If you wish to re-initialize the cache:

2. Click Yes two times to restart the computer.

If the issue persists, then I would suggest you to modify the registry settings and check whether it helps. But before you modify or change registry. I would suggest you to create a restore point and then proceed with the further steps.

To create a restore point follow the steps mentioned below.

• Open System by clicking the Start button, right-clicking Computer and then clicking Properties.
• In the left pane, click System protection. If you're prompted for an administrator password or confirmation, type the password or provide confirmation.
• Click the System Protection tab and then click Create.
• In the System Protection dialog box, type a description and then click Create.

Create a restore point:

 

A restore point is a representation of a stored state of your computer's system files. You can use a restore point to restore your computer's system files to an earlier point in time. Restore points are automatically created by System Restore weekly and when System Restore detects the beginning of a change to your computer, such as when you install a program or a driver.

System image backups stored on hard disks can also be used for System Restore, just like the restore points created by system protection. Even though system image backups contain both your system files and personal data, your data files won't be affected by System Restore.You can create a restore point manually at any time by following the steps below.

• Open System by clicking the Start button , right-clicking Computer, and then clicking Properties.

• In the left pane, click System protection. If you're prompted for an administrator password or confirmation, type the password or provide confirmation.

• Click the System Protection tab, and then click Create.

• In the System Protection dialog box, type a description, and then click Create.

 Method 2

Use Registry Editor

If you cannot access the Offline Files tab, use this method to reinitialize the Offline Files (CSC) cache on the system by modifying the registry. Use this method also to reinitialize the offline files database/client-side cache on multiple systems. Add the following registry sub key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\NetCache
Key Name: Format Database
Key Type: DWORD
Key Value: 1

The actual value of the registry key is ignored. This registry change requires a restart. When the computer is restarting, the shell will reinitialize the CSC cache, and then delete the registry key if the registry entry exists.

All cache files are deleted and unsynchronized data is lost.

Use Reg.exe

You can also automate the process of setting this registry value by using the Reg.exe command line editor. To do this, type the following command in the Reg.exe window:

REG.EXE. REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\NetCache" /v FormatDatabase /t REG_DWORD /d 1 /f

 

Modifying REGISTRY settings incorrectly can cause serious problems that may prevent your computer from booting properly. Microsoft cannot guarantee that any problems resulting from the configuring of REGISTRY settings can be solved. Modifications of these settings are at your own risk.

Read More

Problem Sharing Access Database (MDB) By Multiple Users

Problem Sharing Access Database (MDB) By Multiple Users

By   23:47   No comments:

When you have Access databases that are configured for multiple user access, you might have come across a problem that only the first user to connect can access the database - all later access will be blocked.

If you have such problem, make sure all those who have access to the database file have full right on that file. That is, can they able to change the database?

In theory, the path to the databases always remains the same, so Access should not have an issue handling multiple users, but share does appear to be causing some issue here. This fairly clearly indicates that the issue is an Access problem with share, not share directory or the domain server.

Problem:

When multiple users try to use Windows Explorer or a shortcut to open a Microsoft Access database that has already been opened by a user, the users experience one of the following symptoms:

• Nothing occurs.
• An hourglass appears for several seconds, and then nothing occurs.

Solution:

To work around this problem, use one of the following methods.

• Method 1: Modify the shortcut, In the target of the shortcut, include the path of MSAccess.exe.
• Method 2: Open the database in Access

Follow these steps:

1. Start Microsoft Access.

2. Open the database.

Read More

Distinction Between OLTP And OLAP Databases

Distinction Between OLTP And OLAP Databases

By   14:36   No comments:

Another important distinction is that between online transaction processing (OLTP) and online analytical processing (OLAP) databases. The distinction is not as clear-cut as that the standard distinction between file-server and client-server. In fact, most databases will be used as both OLTP and OLAP products during their lifetime.

OLTP refers to a usage pattern involving rapid insertion, deletion, and updating of data. This is typical of many applications. For example, suppose you’re running a travel agency and have 20 agents all updating a database of customer trip information.

This would be a typical OLTP application. The ability to quickly locate and change data is of paramount importance to avoid the database becoming a bottleneck for the entire operation.

On the other hand, suppose you’re the manager of the travel agency. You might be interested in seeing summary information from many bookings. Perhaps there’s a pattern where women travel more to Greece and men more to Spain; knowing this could enable you to better target your advertising to appropriate periodicals. Such analysis, involving summaries of all or most of the data in a database, is the hallmark of OLAP applications.

It’s very difficult for a server to be efficient for both OLTP and OLAP applications.

The data structures that are appropriate for fast updating are suboptimal for aggregate querying. Microsoft solves this problem by shipping two servers together. The first, Microsoft SQL Server, is mainly an OLTP server. It can perform summary queries, but it’s not optimized for them. That’s the job of the second program, Microsoft SQL Server  Analysis Services. This second program ships with every copy of SQL Server and is designed to build efficient structures for OLAP applications to use.

Read More

Connecting Access To SQL Server

Connecting Access To SQL Server

By   14:32   No comments:

Although Query Analyzer is a useful tool, it’s not all that user-friendly. You need to understand SQL to do much of anything with Query Analyzer. Wouldn’t it be nice to just view your SQL Server data through a more friendly interface? Well, if you’re familiar with Microsoft Access and you have Access 2000 or new , you can do just that.

Tested With Access 2000 And SQL Server 2000

Since Access 2000, Access includes a new type of database called an Access project. An Access project includes all of the familiar Access user-interface tools such as forms and reports. However, instead of storing its data in a Jet database, it stores its data in a Microsoft SQL Server database. In fact, Access even comes with a desktop version of SQL Server, the Microsoft Database Engine (MSDE).

You can also create an Access project that shows data from an existing SQL Server database. To do so, follow these steps:

1. Launch Access.
2. Choose Create a New Database Using Access Database Wizards, Pages and Projects from the opening dialog box.
3. Choose the General tab in the New dialog box.
4. Choose the icon for Project (Existing Database) and click OK.
5. Assign a name to your project and click Create.
6. Enter your SQL Server name, authentication information, and database name in the Data Link Properties dialog box, and click OK.

That’s all there is to it.

Read More

Administrative Activities That Are Required For SQL Server

Administrative Activities That Are Required For SQL Server

By   09:49   No comments:

The practical behavior and acceptability of an application based on SQL Server depends on several administrative activities:

1. Backing up your data regularly to minimize the possibility of losing important business data. You need to consider issues such as hardware failure (for example, a hard drive that fails) or external events such as a fire in the building that houses your database server(s).
2. Verifying that data has backed up successfully. Storing backups in a remote location (or locations) ensures that no single disaster can destroy your business while you attempt to get SQL Server up and running again.
3. Ensuring that you can restore backed up data. 
4. Replicating data between business sites if it’s important that both sites have access to synchronized data.
5. Selecting hardware that supports scalability or high performance; for example, hard-drive size and configuration, and clustering of SQL Server machines.
6. Using database mirroring (introduced from SQL Server 2005) to allow rapid failover from a failing SQL Server machine to another SQL Server machine that has the database in the same state. That allows your application to continue on the other machine with little or no appearance of a problem to users or customers.

The database engine in SQL Server is designed to support robust, reliable processing of data. In addition, it’s designed to support configurations that ensure high availability and scalability. 

If you’re going to design database applications that support your business’s interaction with its customers, the database must be accessible when customers need it. It’s bad business to lose orders simply because the database isn’t available when your customer wants to place an order.

Read More