Friday, June 25, 2010

Building a cube without a data source

Sometimes we might have to build a cube without a relational data source. In this scenario, we can build a cube without a datasource focusing on designing the dimensions and measures and then BIDS can generate the relational schema that is needed to populate the cube.

The following are the steps to achieve this.

•Start a new analysis Services Project in BIDS.
•In the Select method to build the cube diablog box -- choose "Build the cube without using a data source".
•In the Define New Measrue dialog box -- define the measures and measure groups.
•In the Define New Dimensions dialog box -- define the dimensions and set the basic properties
•In the Define Time Periods dialog box -- Define the first calendar day, last calendar day, first day of the week and the time periods whether it is year, quarter, month, date etc.
•Specify additional calendars if needed
•In the Define Dimension Usage dialog box -- Specify how the dimensions relate to the measure groups in the cube.
•In the completing the Wizard dialog box -- Enter the cube name and click Generate Schema now tickbox. This generates the schema geneeration wizard.
•In the Schema Generation Wizard dialog box -- Specify the Data Source view name and this will create the new Data Source View.
•In the Connection Manager dialog box -- Fill in the Provider, server name, Logon to the server, Connect to database fields.
•In the Impersonation Information dialog box -- Use the Service account (is recommended)
•In the data source wizard -- Specify the data source name
•In the Subject Area Database Schema Options dialog box -- Specify the options, accept the defaults
•In the Specify Naming Conventions dialog box -- just accept the defaults.
•After the above steps are completed, the data source view is generated in BIDS
•Populate the data source with data so that this data can be used to populate the cube.
•The last step is to right click the relevant cube in the solution explorer and select Process. Accept all the defaults and click Run. This step extracts data from the data source view and populates the cube.
The cube is now ready for viewing.

Here is a link to the original blog I posted on BIDN.

Wednesday, June 23, 2010

Silverlight Operating system

Here is a link I found today that is really interesting. It is a windows 7 operating system simulation. Have a look.

Friday, June 18, 2010

Microsoft LogParser

Today I was frustrated with the built in error log in SQL SERVER 2005 and did some research and found the following link to Microsoft LogParser that can be used to search the error logs.

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en



Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

I have found this very useful

A sample query to search in the error logs is as follows:

Logparser.exe "select top 100 substr(text,23,9) as ESource, substr(text,0,22) as EDate, substr(text,32) asEMessage from \\d:\databases\MSSQLTEST\LOG\ERRORLOG.* where EMessage like '%database \'master\'.%'" -i:textline

Wednesday, June 16, 2010

24 Hours of PASS Recordings Ready for Viewing!

24 Hours of PASS Recordings Ready for Viewing!

24 Hours of PASS brought an exceptional lineup of SQL Server and BI experts from around the world in 24 back-to-back webcasts starting at 12:00 GMT (UTC) on May 19 and 20. Attendees got an in-depth look at the hottest SQL Server and BI topics, including - as part of the SQL Server 2008 R2 Community Launch - the new SQL Server 2008 R2, including business intelligence and data management innovations, and much more.

Check out the sessions again or for the first time and learn from the community's top SQL Server experts. You need to be a PASS member (it's FREE to join!) to view the recordings.

Tuesday, June 15, 2010

SSIS Expression Tester

The expression tester tool is a standalone application that allows you to easily develop and test expressions. It is like a workbench to quickly try things without having to open a package or mess up the one your currently working on. It is designed to be easily portable, just extract the files and run, but it does require SQL Server Integration Services to be installed on the machine. The download contains both SQL Server 2005 and SQL Server 2008 versions of the tool and control.

You can download this tool at the following link


SSIS Expression Tester

Friday, June 04, 2010

Shortcut key to display Macro dialogue

In excel the shortcut key to display the macro dialog box is Alt+F8. This can then be used to create, run, edit, or delete a macro.

Thursday, June 03, 2010

Crystal reports not working from CRM system

today I had a problem where the crystal reports that render through the CRM system that print on a label printer were not printing. The error coming up was - "Error printing to crystal reports." Our system admin tried to reinstall the printer software, but that didnot fix the problem. Then I asked him to check whether it is the Microsoft updates.

Then he restarted the system and allowed the updates to go through and that fixed the problem.

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  -- create table #...