Friday, February 29, 2008

Monitoring and Recording DDL changes on SQL 2005 (NARC) - SQL Server Central

Poor man's SQL auditor.

The classic rhetorical question when diagnosing a technical issue "what changed on the server?", may now be possibly answered. Since some of my servers do not have Service Broker and Notification Services activated or installed, I had to come up with a solution that would work on my installed base of SQL 2005 as I originally intended to make this a Notification Services running asynchronously just a learning exercise.

Monitoring and Recording DDL changes on SQL 2005 (NARC) - SQL Server Central

Thursday, February 28, 2008

How to Summarize Excel Data For Reporting and Analysis, An Excel 2003 Tutorial

 

Learn something new every day... maybe even a couple things.

Business Intelligence with Microsoft Excel Formula Arrays and Named Ranges.

In Excel, you normally enter a formula by typing it into the formula bar and then pressing the Enter key. You array enter a formula by typing it into your formula bar, holding down the Ctrl and Shift keys, and then pressing Enter.

After you array-enter a formula, the formula bar shows that it begins and ends with braces, like this:

{=A1}

To be clear, you do not type in those "{" and "}" characters yourself. Instead, Excel displays them after you array-enter a formula by pressing Ctrl+Shift+Enter.

How to Summarize Excel Data
For Reporting and Analysis

Excel offers several ways to summarize data quickly and easily.
We explain the most powerful and flexible approaches, which
include using Excel arrays.

How to Summarize Excel Data For Reporting and Analysis, An Excel 2003 Tutorial

Microsoft vs. Teradata | Reg Developer

Comparing Microsoft vs. Teradata.  Don't flame me, I'm just the messenger! :)

Given this common wish list, how did Microsoft and Teradata end up with such different strategies?

Microsoft vs. Teradata | Reg Developer

SSIS Junkie : SQL Server Analysis Services - Best Practices

 

As a pointer to best practice I would recommend that you always use named queries in your DSV.

SSIS Junkie : SQL Server Analysis Services

Chris Webb's BI Blog: Using Non_Empty_Behavior With YTD calculations

Chris hacks away at a slow performing MDX query... by creating a new fact table view.

The query now runs in, wait for it, in 7 seconds on a cold cache in SQLMS and if you look in Profiler you'll see that in fact the majority of that time is taken up by SQLMS rendering the resultset - it takes just over 2.5 seconds to actually run on the server.

Chris Webb's BI Blog: Using Non_Empty_Behavior With YTD calculations

SQL Server 2008 CTP6 gives you PowerShell - Rob Farley

 

SQL Server 2008 CTP6 gives you PowerShell

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

SQL Server 2008 CTP6 gives you PowerShell - Rob Farley

Free Online CSV to SQL Converter - mySQL Native Support!

 

This tool provides an easy way to convert CSV files to SQL to be inserted into a database. Simply upload your CSV file and the resulting page will display the INSERT statements to turn the CSV file into records in a database table!

Free Online CSV to SQL Converter - mySQL Native Support!

Wednesday, February 27, 2008

Aggregation design, row counts and the 1/3 rule

 

Mosha and Thierry give us multiple examples of how to provide annotations in SSAS 2005.

The solution revolve around the fact that Analysis Services Storage engine doesn’t know how to handle String for Measure data but does know for Dimension data and also the fact that the Formula engine does know how to handle text data.

So in a nutshell, Comment and Annotation are handled as Dimension members, entered by the user using Dimension writeback, then are retrieved as part of the measure group using a calculated member and thus queried from any front-end using regular MDX.

Handling Comment or Annotation as dimension member can seem scary at first, but since Yukon doesn’t handle the entire Dimension in memory anymore, its scalability capability in term of size of dimension members has greatly increased and can easily go above 50 millions members. For the very large majority of applications it seems reasonable to assume that this type of volume will never be reached. Indeed if we assume that an application has a lifespan of 5 years, then it gives us 10 millions members or Comment per year. Now let’s say that this application has 1000 active users, then every user has a workspace of 10 000 comments per year or 833 comments per month. It is a lot of text to enter every month for a single user…

Aggregation design, row counts and the 1/3 rule

Thursday, February 21, 2008

The Road to Know Where: Download Free Office 2007 Software from Microsoft

 

Even though Microsoft Office 2007 has only been release for a short time, there are already some free additions from Microsoft. If you're looking for free Microsoft Office 2003 software -- click here!

The Road to Know Where: Download Free Office 2007 Software from Microsoft

K. Scott Allen : Three Rules for Database Work

 1. Never use a shared database server for development work.

2. Always Have a Single, Authoritative Source For Your Schema

3. Always Version Your Database  

K. Scott Allen : Three Rules for Database Work

Five sqlcmd features to automate SQL Server database tasks

 

Five sqlcmd features to automate SQL Server database tasks


Roman Rehak
02.18.2008
Rating: -4.20- (out of 5)

Five sqlcmd features to automate SQL Server database tasks

Wednesday, February 20, 2008

Microsoft Gets Gartner's Business Intelligence Top Ranking -- Business Intelligence


Looks like Microsoft's BI Strategy (KPIs for everyone in all of our products) is working out.

Business Objects, Cognos, and Microsoft were placed among the leaders in Gartner's just-released Magic Quadrant for Business Intelligence Platforms, 2008. But the research firm placed Microsoft above the other two in its ability to execute, including the competitiveness and success of its BI goods and services, its viability and investment in BI, and the execution of its sales and pricing. In last year's BI platform report, Gartner put Microsoft in the challenger quadrant.

Microsoft Gets Gartner's Business Intelligence Top Ranking -- Business Intelligence

Cubes and Universes « Stéphane-Robert Langer

A couple years old but still worth a read to better understand differences between BO and SSAS.

Mark Miller recently posted a link to a whitepaper comparing the BI offerings from Business Objects and Microsoft. This is something I had been looking for for quite a while and I think it does a good job of presenting a comparative history of both platforms. The content remain fairly high level, though. What I’d like to do here is to discuss some of the conceptual differences between a Business Objects (BO) universe and a Microsoft SQL Server Analysis Services (SSAS) cube.

Cubes and Universes « Stéphane-Robert Langer

Tuesday, February 19, 2008

format date excel export reporting services - Google Search


Not a lot of results on this one... so I figured it out myself.

Instead of formatting just the field, you need to format the entire column in order to retain the date format in Reporting Services Excel export.

Hope this helps someone else.

format date excel export reporting services - Google Search

Microsoft Research: MSAGL: Microsoft Automatic Graph Layout

A cool Visio-like graph tool for under $300 that you can drop into C# code.

MSAGL is a .NET tool for graph layout and viewing. It was developed in Microsoft Research by Lev Nachmanson. MSAGL is built on the principle of the Sugiyama scheme; it produces so called layered, or hierarchical layouts. This kind of a layout naturally applies to graphs with some flow of information. The graph could represent a control flow graph of a program, a state machine, a C++ class hierarchy, etc.

Microsoft Research: MSAGL: Microsoft Automatic Graph Layout

JIT Development - Managed Visio

Visio (and office in general) is still in the dark ages with its automation support.  Namely COM+ & VBA.  It works, though I'm a C# kind of guy.

Luckily here's one project that may help to improve the coding experience.  It helps that I just installed VS 2008.

Project Description

VisioAutoExt is a library that simplifies using Visio 2007 from C#

VisioAutoExt - Home

Diagrammatic Reasoning Related Research Sites

Interesting site for those looking at Data Visualization, OLAP and Silverlight application ideas, as I am.

Diagrammatic Reasoning Related Research Sites

Diagrammatic Reasoning Related Research Sites

and this to demonstrate a cube example.

http://www.wideman-one.com/gw/Models/sdsubigpic/DataCube1.htm

This is from '96.  How long has OLAP been around anyway?

According to the OLAP Report, before 1962...

http://www.olapreport.com/origins.htm

What's even more incredible than this tidbit of information?  Cognos Planning is based on APL, the OLAP language designed by IBM... and once again owned by IBM as of last year.  Over 37 years later.

Who has one of the most long-lived software products ever?  Oracle's Express, which has gone through over 35 years of development.

Who had features that are still not widely adopted?  Comshare's System W had these features:

  • Full non-procedural rules
  • Full screen multidimensional viewing and data editing
  • Automatic recalculation
  • Batch integration with relational data

#1 is in IBM Cognos Applix TM1.  I can see Silverlight hitting number 2 pretty easily with a writeback cube and a cube browser.  #3 is in Analysis Services.   #4... well... that would be nice.  I have built a few custom solutions around merging MDX & SQL data.  It would be great to see these pulled into a single view without writing complex MDX.

Oracle might offer #4 with its materialized views.

Which OLAP server didn't integrate with Excel when it was first released?  Microsoft OLAP.

What's the most popular BI tool in the world?  Microsoft Excel.

So the killer app to promote Silverlight adoption for BI (other than installing it for the MS downloads site) is to create a Silverlight visualization plugin for Microsoft Excel (Services?) and PerformancePoint.

I guess I've answered my data visualization question.

Visio Guy » Blog Archive » SharePoint Planning Visio Diagrams

 

For your convenience, here's the list of Visio drawing-links:

Models

Scenarios

Flowcharts

Related Posts:

Visio Guy » Blog Archive » SharePoint Planning Visio Diagrams

Monday, February 11, 2008

Check List for Performance Optimization in SQL Server Analysis Service 2005

 

No matter what your platform or the tools that you are using, performance is almost always the number one feature that customers require.  In the case of SQL Server Analysis Service (SSAS), there are four areas where we can improve the performance. They are database design, processing, querying and configuring SSAS.

Check List for Performance Optimisation in SQL Server Analysis Service 2005

Wednesday, February 06, 2008

Generating Missing Dates and Numbers - SQL Server Central

Here's how to find gaps in data with an extremely fast solution. 

This article presents an approach to generate missing numbers and dates without using a temp table. I do not intend to claim that this approach is better than the others. There is no programming approach that suites all requirements. Based on the specific application scenario a certain approach may be found suitable than the others. I guess some of you may find this approach easier to integrate into your application specific requirements.

Source: Generating Missing Dates and Numbers - SQL Server Central

Friday, February 01, 2008