Microsoft .NET & C#: Complete OLAP infrastructure without Microsoft Analysis Services, part 3

 

Complete OLAP infrastructure without Microsoft Analysis Services, part 3

In previous part of this tutorial we've built an analytical database using Microsoft Analysis Services. We've been able to browse the database using SQL Management Studio and execute MDX queries on it.

In this part of the tutorial we will:

• build an offline static cube (*.cub file) from the relational database using C# and ADOMD.NET
• query the static cube using Microsoft Excell as static cube browser
• query the static cube with MDX queries using C# and ADOMD.NET

Microsoft .NET & C#: Complete OLAP infrastructure without Microsoft Analysis Services, part 3

Internals Viewer for SQL Server - Home

 

Project Description
Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.
All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works
Note There was a problem with the first release of Beta 2 20081228. If no add-in is visible please follow the instructions in Troubleshooting.
User Guide
Troubleshooting
Features

• Integration with SSMS (SQL Server Management Studio) 2005 and 2008

• The application is installed as a SSMS add-in
• Internals information integrated into the Object Explorer
• Transaction Log viewer integrated into the Query Results

• Allocation Map

• Displays the physical layout of tables and indexes
• Displays PFS status
• Overlay pages in the Buffer Pool

• Page Viewer

• Displays Data pages including forwarding records and sparse columns
• Displays Index pages
• Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
• Displays pages with SQL Server 2008 row and page compression

Internals Viewer for SQL Server - Home

Quick and dirty clone table schema

 

SELECT top 0 * into customer_backup
  FROM customers

Be sure to create your primary keys and indexes afterwards!

DM Companion – Data mining in cloud city

If you would like a better understanding of the Data Mining Experience from the web, DM Companion looks like a good learning tool.

Of course, there’s always this tool too:
http://sqlserverdatamining.com/cloud

DM Companion

DataPort Web | Sql bulk insert and paramaters

 

Sometimes transferring data quickly and safely can be a major task. In the following example I’ll show you how you can do a parameterized insert and how to use the sql transaction. This will speed up your insert tremendously.

DataPort Web | Sql bulk insert and paramaters

Data Dude - VSTS Database GDR VPC

 

You can download the VPC from the following location:

• VSTS “all-up” Virtual PC/Virtual Server image (6 GB download, expands to 15 GB)
• VSTS “all-up” Hyper-V image (6 GB download, expands to 15 GB)

Data Dude

Metadata Toolkit - Windows Live

The BI Metadata Toolkit

Overview

This white paper covers several interesting and unique methods for managing metadata in SQL Server Integration Services, Analysis Services and Reporting Services using built-in features including data lineage, business and technical metadata and impact analysis.

http://www.microsoft.com/downloads/details.aspx?FamilyID=182bd330-0189-450c-a2fe-df5c132d9da9&displaylang=en

Metadata Toolkit - Windows Live

Download details: SQL Server 2005 SP3

 

Microsoft SQL Server 2005 Service Pack 3

Brief Description

Download Service Pack 3 for Microsoft SQL Server 2005.

Download details: SQL Server 2005 SP3

Andrew Fryer's Blog – creating a “time of day” dimension

Highlighting some of the pluses of SQL 2008

Following on from my previous post, in some data warehouses there is a separate dimension for time of day, so that demand through a day can be modelled. Storing time in SQL server 2005 was a bit of a cludge typically involving picking an arbitrary date (like 1/1/1900) and then tacking the time on to the end of that.  Now there’s a separate time data type so it’s easy to store the right data and create the time dimension using a script like this:

declare @time time = '00:00'
declare @timekey int = 0
declare @timegrain int =15

if not exists
    (select  * from sys.tables where name = 'dimTimeofday')
create table dimTimeofday( timekey int, TimeofDay time)
while @timekey < 1440 begin   
    insert into dimTimeofday(timekey,Timeofday) values (@timekey, @time)
    set @time = dateadd(minute,@timegrain,@time)
    set @timekey += @timegrain
end

For more on the new time data type check books on line here.

Andrew Fryer's Blog

SQL Server Matrix Workbench

Excel inside SQL Server?  It’s possible…

/*In this workbench, Robyn Page and Phil Factor decide to tackle the subject of Matrix handling and Matrix Mathematics in SQL. They maintain that 'One just needs a clear head and think in terms of set-based operations' */

SQL Server Matrix Workbench

Download details: SQL Server 2008 Upgrade Technical Reference Guide

 

Thank You for Downloading

SQL Server 2008 Upgrade Technical Reference Guide

Download details: SQL Server 2008 Upgrade Technical Reference Guide

Killing sessions automatically with SSIS

Poor man's query governor for MDX here.

...or, what to do while you're waiting for Analysis Services to get a proper resource governor (fingers crossed for the next version).

Chris Webb's BI Blog - Windows Live

Report Viewer Redistributable 2008 from 9/9/2008 still does not read 2008 reports??? - TechNet Forums

SQL 2008 launched a few months ago... and left Reporting Services developers in the dust.

Good things will come to those that wait.

The current target date for a control that can read the 2008 RDL schema is currently the first calendar quarter of 2009.  These dates can and are subject to change.

Report Viewer Redistributable 2008 from 9/9/2008 still does not read 2008 reports??? - TechNet Forums

"So, a booth babe and a geek walk in to a bar..." and they get certified.

Notes from a former certification blogger.

I really recommend that you subscribe to Born to Learn.

Ask Ken why it's called Born to Learn, while you're over there.

The first stop for MCP, certification, or exam help continues to be your regional helpdesk: http://www.microsoft.com/learning/support/worldsites.mspx, as you know. The MCP newsletter is still the best, official way to get news about the program and exams: Subscription info. And don't forget the other great bloggers in Microsoft learning--if you get stuck somewhere, check in with one of these people or teams:

• Gerry: Developer Certification
• Mike: E-Learning
• Beta exams
• The Microsoft Learning web site
• Jeff: Developer Courseware
• Juliana: Microsoft Press
• ILT – Courseware Team: Instructor-Led Training
• Bill: MCT and Trainer Readiness

While I'm at it, I thought I'd answer some of your other, recent questions, too, in a little Q&A.

"So, a booth babe and a geek walk in to a bar..."

Seven Steps to Certification Success : Training and Certification : Learning : Microsoft Forums

Where to start when dealing with certifications

Before writing this article, I posed a question to many certified individuals. The question I asked was, “What was the hardest part of your certification journey?” You would expect to hear that the hardest part was the exam(s) themselves. However, many people responded that the most difficult challenge was that they simply did not know how or where to start. They would describe how they heard of a certification, bought a book and started studying. There was no research performed, no plan established, and no evaluation afterword to debrief and learn from the experience.

Seven Steps to Certification Success : Training and Certification : Learning : Microsoft Forums

JAM Software - SpaceObServer - The Hard Disk Space Manager with Database Storage

 

SpaceObServer - The Complete Disk Usage Management Solution

V3.3.2

SpaceObServer is a powerful and flexible hard disk space manager for Windows. It scans local and network drives using a background service and stores their structure, sizes and properties in an SQL database. In an Explorer-like user interface the collected data can be viewed and browsed in hierarchical or tabular views, 3D bar, pie, line charts and tree maps. Using the archived data you are able to track the development of the space usage from past to present, and forecast future size usage. A flexible file search, with predefined searches for very big, old or obsolete files allows filtering and listing files directly from the database. A duplicate file search is also included.

JAM Software - SpaceObServer - The Hard Disk Space Manager with Database Storage

I Shot the Serif - Free 3 of 9

 

Can you believe there are people who would charge you for a barcode font like this?

I Shot the Serif - Free 3 of 9

TFS & Virtual Machines

 

What to do if you need to roll back \ undo your virtualized client

Use the Force -  To get the server and workspace versions in sync again perform a get using the force option. 

OR

If you are using Hyper-V a better solution is to put your workspace on a drive that is offline to the host OS (Windows 2008) but known to the Hyper-V machine.  You have to disassociate this offline drive from the Hyper-V machine before you do the rollback and re-attach afterwards.  This keeps the rollback from affecting the workspace drive, leaving your workspace files the same. 

Ed Hintz (MSFT)

Download SQL-RD Subscription Free Trial - Schedule SQL Server Reporting Services reports in daily, weekly, monthly etc. A single report in an e-mail, or a batch of...

 

SQL-RD saves time and money by allowing you to schedule and manage MS SQL Reporting Services Reports on numerous servers from a single application. It exports your RS reports to multiple printers, FTP, Secure FTP, email, folders, FAX and SMS. Choose to output to doc, xls, rtf, wk*, dbf, htm, mhtm, pdf and more. Use standard frequencies like daily, weekly, mothly, or set up your own custom calendars and exception calendars. Dynamic schedules provide an unmatched feature for linking and automating reports, data and destinations. It integrates seamlessly with Outlook and Exchange Server. Use Event-Based schedules (triggers) to fully automate your business processes whether they are report-related or not. SQL-RD features a feature-rich intuitive user interface, Folder Housekeeping, Clustering, PDF, PGP, Excel and Zip security, and an NT (Windows) service scheduler. It is fully compatible with SSRS 2000 and SSRS 2005.

Download SQL-RD Subscription Free Trial - Schedule SQL Server Reporting Services reports in daily, weekly, monthly etc. A single report in an e-mail, or a batch of...

Kimberly L. Tripp | Transaction Log VLFs - too many or too few?

 

To have a more ideally sized VLF, consider creating the transaction log in 8GB chunks (8GB, then extend it to 16GB, then extend it to 24GB and so forth) so that the number (and size) of your VLFs is more reasonable (in this case 512MB).

Kimberly L. Tripp | Transaction Log VLFs - too many or too few?

Useful Query #1 - find overlapping SQL Agent jobs

 

In SQL 2008 (or using 3rd party tools) you can run this command against multiple servers to determine which SQL Agent jobs overlap.  Perfect for finding contentious jobs...

use msdb
go

with cte  (name, server, run_date,run_time,run_duration,enddate,startdatetime,enddatetime,retries_attempted) as (
select --j.name, h.step_id, h.step_name, h.sql_message_id, h.sql_severity, h.message, run_status,
j.name,server, run_date, run_time, run_duration, run_time + run_duration enddate,

dateadd(hh, run_time / 10000,
            dateadd(mi, (run_time % 10000)/100,
            dateadd(ss, run_time %100,
            cast(cast(run_date as char(8)) as datetime)))) startdatetime,
            dateadd(ss,run_duration,dateadd(hh, run_time / 10000,
            dateadd(mi, (run_time % 10000)/100,
            dateadd(ss, run_time %100,
            cast(cast(run_date as char(8)) as datetime))))) enddatetime,

retries_attempted
from sysjobs j
inner join sysjobhistory h on h.job_id = j.job_id and h.step_id = 1
where run_date > convert(varchar(10),dateadd(d,-1,getdate()),112)
--and run_status <> 1
)
select cte.server, cte.name, cte.startdatetime, cte1.enddatetime, cte.run_duration, cte1.server, cte1.name, cte1.startdatetime, cte1.enddatetime, cte1.run_duration
from cte
cross join cte cte1
where (cte.name <> cte1.name)

and (cte.startdatetime between cte1.startdatetime and cte1.enddatetime
    or cte.enddatetime between cte1.startdatetime and cte1.enddatetime)
    and cte.run_duration > 300

Carpe Datum - Activity Monitor

Other than hiding Activity Monitor from us in SQL 2008 (it's on the toolbar now) it has some great new features that make the role of a DBA almost obsolete.

Well, not quite, but it does make things much easier.

Install SQL 2008 and you can even run things like Policy Based Management and Activity Monitor (Super 2008 Ed'n) against SQL 2005 & 2000 instances.

My favourite trick is to show query plan on an active query, show missing indexes, then implement.  Instant performance improvements... and here's another way to track performance (or just snoop).

The new Activity Monitor has another trick up its sleeve: If you open it and then expand the first band of information just below the four graphs, you'll see a list of processes that you can order, sort and filter. If you right-click any process, you'll see an option to "open in Profiler". Click that, and you'll open Profiler with a default trace right on that SPID. Very useful to quickly identify the actions of a connection.

Carpe Datum

Legalizing the crack that is Excel spreadmarts – Chris Webb on Project Gemini

There’s nothing wrong with Excel.  Actually, there’s tons of stuff wrong with Excel.  Here is one example of a Spreadmart gone bad.

Excel error leaves Barclays with more Lehman assets than it bargained for

The law firm representing Barclays filed the motion (download PDF) on Friday in U.S. Bankruptcy Court for the Southern District of New York, seeking to exclude 179 Lehman contracts that it said were mistakenly included in the asset purchase agreement. The firm — Cleary Gottlieb Steen & Hamilton LLP — said in the motion that one of its first-year law associates had unknowingly added the contracts when reformatting a spreadsheet in Excel.

Cut-Paste Wealth Destruction! :)

Actually, many of the problems with Excel aren’t really with the product, it’s how it’s used.  “When the only tool you have is a hammer, everything looks like a nail.”  Ditto when the only tools you are comfortable with for dealing with numbers are Excel and Calc.exe, and it takes 2 weeks (or more) for the same report to be built using a “reporting tool” by the IT team…

Chris has some great points that I wholeheartedly agree with.  In the past, I have been responsible for promoting the view of a “single source of total knowledge” or a “one view” of the organization.  Excel doesn’t fit into this picture as a storage mechanism, but it can be the UI, analysis, modeling, and calculation engine that is supported by a central repository stored in the SQL Server cloud (or wherever you may decide to store your data).  Not knowing the details on Project Gemini, I hope that it continues with this theme of server-based storage and client-based analysis, and doesn’t go the way of Coleco Gemini.  I hope that there are options for clients who don’t adopt the latest technologies.  

Spreadsheets have long been one of the most popular ways for corporate users to store and analyze data. But over the past few years, they have played an increasing role in data breaches because workers are apt to store them unsecured on laptops. In addition, hackers have actively tried to exploit vulnerabilities in Excel.

Since I’m a DBA at heart, I’m not comfortable seeing thousands of silos of varying degrees of accurate information multiplying and dividing around a company. With a DBA mindset, it is all about control, security, maintainability, and performance of your data. 

This kind of desktop, DIY BI is in a way similar to illegal drugs: there are always some people that want it, a certain number of them are always going to do it even though they know they shouldn't, so you've got two choices - either legalise it and then hope to control it, as with Gemini, or throw all your efforts into outlawing it.

Chris Webb's BI Blog: Last thoughts on Gemini for the moment

Project Gemini – Microsoft’s Brilliant Trojan Horse

The concept of transferring some of the calculations and aggregations to the client does make sense.  My laptop is more powerful than many of the 5 year old servers in use at some of the client sites I work in.  It would be great to be able to quickly build models without delving into Business Intelligence Studio, SSIS, SSRS & SMS, or asking a developer.  There’s not much faster than memory on a PC, so in-memory processing sounds great to me.  I just hope there’s a way to push out a lockdown mechanism, for when that laptop and its information disappears from the company.

I hope they include a connector to perform calcs inside the GPU too. 

I like what I see so far with some of the “value-adds” coming out of MS Downloads for Excel like the data mining tools, though in the wrong hands (or even worse, the right hands) the information could be very misleading and lead to disastrous results.  From a marketing and adoption perspective, does it make sense to sell the idea of distributing mass amounts of data down to a client PC?  I’m still waiting on a good forms-based interface to input data from Excel directly into a data repository.  Sort of an InfoPath merged with Excel, without the need to install InfoPath, and with the “always-on” save features from One Note.  Sure, web forms and web services.  What about just Excel to Sql?

Without proper governance and understanding of the technology, publishing to Sharepoint can still lead us to Enterprise Spreadmart solutions and IT maintenance nightmares. 

In my opinion, rather than sheets of 20 million rows of raw data crunched and stored in a spreadsheet on a laptop, file share, or document store, there should be sheets (or something else?) of results available with the data being stored, crunched, and transformed in a central, secure, redundant place (“THE CLOUD?”). I hope that this is the approach Project Gemini will provide.  Magic?

One truth, shared by many, common to one. 

Metadata, semantic web technologies and, yes, Gemini again

Gemini IS Analysis Services

Gemini is Inevitability

SPSFAQ – Scalability and the fact that Sharepoint isn’t a database.

Some people seem to think that Microsoft Business Intelligence is Sharepoint.  Sharepoint is one piece to the puzzle, and it can scale, but there are some inherent limits that need to be avoided.  Eli has a few.

The 2,000 rule: because stored procedure calls to SQL Server slow down as you reach 2,000 items, have less than that in a view on a List. Less than 200 ideally to have optimum performance.

SPSFAQ

Speaking of scalability & Microsoft applications… Windows 3.1 is dead.  Long live Windows 3.11.

Microsoft has officially retired Windows 3.1

news.bbc.co.uk — "An application has expectedly quit. Windows 3.x has come to the closing moments of its long life. On 1 November Microsoft stopped issuing licences for the software that made its debut in May 1990 in the US. The various versions of Windows 3.x (including 3.11) released in the early 1990s, were the first of Microsoft's graphical user interfaces .."

http://digg.com/microsoft/Microsoft_has_officially_retired_Windows_3_1

PSS SQL Server Engineers : SQL Server Support in a Hardware Virtualization Environment

 

SQL Server Support in a Hardware Virtualization Environment

There is no doubt that virtualization is a hot and popular topic (the number of questions over email I get daily are a testament to that). Therefore, I think it is important for our customers to understand the support policies from Microsoft regarding SQL Server running in a hardware virtualization environment.

We have just published the following KB article that outlines this policy:

http://support.microsoft.com/?id=956893

PSS SQL Server Engineers : SQL Server Support in a Hardware Virtualization Environment

BeI - Microsoft Business Intelligence

 

Day after day we are working with Cube Browser but when the project or cube designer has closed we lost our query, sometime it takes a long time to reorder again all our dimensions.

This add-in is giving us the ability to save personal views and run it as a new request.

I develop it for SSAS 2005 & 2008 (SSAS 2008 Pre CTP6 with VS 2008).

Download Add-in For SSAS 2005

Download Add-in For SSAS 2008

BeI - Microsoft Business Intelligence

BeI - Microsoft Business Intelligence – Using WMI & Reporting Services

 

Reporting Services Querying with WQL

Hi Everyone,

From time to time we need to "ask" our Operation system questions such as:

1.       How are you today?

2.       How was your night?

3.       Do you need anything?

4.       Did somebody do something to you?  Who?

Well, the best way to do it is querying the system with WMI queries by WQL language.

Few days ago my customer needed to decide what the best way is.

So, how we can do it? , the following solutions are some examples of ways to do it

BeI - Microsoft Business Intelligence

SSIS Junkie : T-SQL: Generate a list of dates

 

The following bit of code uses a common table expression (CTE) to generate a contiguous list of dates in SQL Server.

with mycte as

(

select cast('20080101' as datetime) DateValue

union all

select DateValue + 1

from mycte

where DateValue + 1 < '20080731'

)

select convert(char(8),DateValue,112) DateValue

from mycte

OPTION (MAXRECURSION 0)

SSIS Junkie : T-SQL: Generate a list of dates

SQL Server Forums - Deleting Large Number of Rows

SQL 2008 breaking “feature”

---

I was looking at the ROWCOUNT documentation and BOL states:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.
Something to keep in mind if your scripts will be run against sql 2008

SQL Server Forums - Deleting Large Number of Rows

How to open a website in Visual Studio 2005 from the command line - BradleyB's WebLog

 

How to open a website in Visual Studio 2005 from the command line

I’ve seen several queries asking how to launch Visual Studio 2005 opening a specific folder as a website from the command line. Unfortunately Visual Studio 2005 does not support this by default but you can enable the scenario by writing a macro.

To do this, create a new macro either in an existing module or in a new module. For this example I’m going to add a module call Website to the MyMacros project.

If you’re new to macros in Visual Studio 2005, select menu Tools/Macros/Macros IDE, then select the MyMacros project and add a module.

Before you can use the new Website extensibility objects within VS you’ll need to add a reference to VsWebSite.Interop.dll.

Once added you can add the following code:

Public Module Website

Sub OpenWebsite(Optional ByVal path As String = "")

If (String.Compare(path, String.Empty) = 0) Then

MsgBox("Must supply a folder path to the OpenWebsite macro", MsgBoxStyle.OkOnly)

Else

Dim webPkg As VsWebSite.VSWebPackage

webPkg = DTE.GetObject("WebPackage")

webPkg.OpenWebSite(path, VsWebSite.OpenWebsiteOptions.OpenWebsiteOption_None, False)

End If

End Sub

End Module

After this code is added you’ll be able to run the macro. You can test it out in the command window.

From the Command Window in VS:

>Macros.MyMacros.Website.OpenWebsite C:\MyProjects\MyCompany\CompanySite

From the Command Line:

devenv /command "Macros.MyMacros.Website.OpenWebsite C:\MyProjects\MyCompany\CompanySite"

After its working you can register a shell command enabling an “Open as Visual Studio Website” command on any folder in windows explorer.

To do this copy the following into a OpenWebsite.reg file and run it.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Folder\shell\OpenVSWeb]

@="Open as Visual Studio Website"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Folder\shell\OpenVSWeb\command]

@="devenv.exe /command \\\"Macros.MyMacros.Website.OpenWebsite %1\\\""

Now you should be able to right click on a folder in Windows Explorer and select “Open as Website”.

How to open a website in Visual Studio 2005 from the command line - BradleyB's WebLog

Querying and Reporting on Report Execution Log Data

SELECT 'http://localhost/reportserver?'+cast(c.path as nvarchar(max))+'&'+cast([Parameters] as nvarchar(max))
FROM [ReportServer].[dbo].[ExecutionLog] e
INNER JOIN catalog c on c.itemid = e.reportid
WHERE ….

This lets you query the execution log and generate a list of URLs to run reports in Reporting Services.

Querying and Reporting on Report Execution Log Data

Andrew Clancy's Blog : NHibernate Query Generator in Action

 

NHibernate querying is great – it gives us the ability to query objects directly without the need to know anything about the underlying relational schema. It has a couple of major flaws though: 1. property names are accessed via strings, so we don’t know until runtime whether they exist, or are mapped 2. it’s ugly and cumbersome – frankly probably one of the major barriers I’ve had to ORM until now.

Ayende’s NHibernate Query Generator fixes both of these things. Mapped properties are accessed via properties as nature intended, and querying is a lot easier to read and write. Not quite as neat as Linq-to-NHibernate will be, but so much better than native NHibernate querying that I wonder why it wasn’t included in the trunk.

Andrew Clancy's Blog : NHibernate Query Generator in Action

MS BI Conference: Monday Keynote - Mike Diehl's WebLog

Waiting on 2010…

Timeframes: the next major release of SQL will be 24-36 months from release of SQL 2008, but in the meantime, there are a number of releases coming: Madison and Gemini will be coming in the first half of 2010, and CTP's will be available sometime early next year. There are some incremental releases of Analysis Services, Integration Services and Reporting Services coming - the next gen of Reporting Services in particular will become available in a Feature Pack "real soon now".

MS BI Conference: Monday Keynote - Mike Diehl's WebLog

The Programmer's Weblog: Inserting Blob into Microsoft Sql Database

 

It took me a while to figure a good way to insert Blob information into a Microsoft Sql database. One of the things you need to do is to insert an empty Blob, and I wasn't finding that on the internet. In Oracle you will use an empty_blob() function. So, I managed to get my code to work and decided to post it here so people can find an easy example. If you need more information, let me know.

The Programmer's Weblog: Inserting Blob into Microsoft Sql Database

Using Grouping Sets in SQL Server 2008 | Servers and Storage | TechRepublic.com

Cube-like functionality in SQL 2008.

Today I took a look at the usefulness of the new GROUPING SETS clause in SQL Server 2008. The results of the GROUPING SETS clause can be achived in other ways, but it takes more programming work to do so. Next time I will take a look at SQL Server 2008 enhancements to the CUBE and ROLLUP; two constructs that can produce similar results to the GROUPING SETS clause, but in a different manner.

Using Grouping Sets in SQL Server 2008 | Servers and Storage | TechRepublic.com

Cumulative update package 1 for SQL Server 2008

Some important fixes here.

FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order

FIX: The result for the Sum or Count function returns an empty value when you query a SQL Server 2008 Analysis Services cube

FIX: In SQL Server 2008 or in SQL Server 2005, the session that runs the TRUNCATE TABLE statement may stop responding, and you cannot end the session

FIX: The Processes pane in the Activity Monitor incorrectly shows sessions as head blockers in SQL Server 2008

Plus plenty of Reporting Services updates.

Cumulative update package 1 for SQL Server 2008

Hey Scripting Guy! How Can I Write to Excel Without Using Excel?

The scripting guy answers this question.

How Can I Write to Excel Without Using Excel?

Hey, Scripting Guy! I need to be able to write information to an Excel file. I do not want to install Microsoft Office Excel on my server, but the spreadsheet format is a nice way to store information. I can then give it to my pointy-headed boss, and he can create pivot tables, charts, or whatever exactly it is he does all day. Basically, I think if I give him lots of data, he will leave me alone and let me do my job. Can you help?

Hey Scripting Guy! How Can I Write to Excel Without Using Excel?

How Can I Read from Excel Without Using Excel?

Hey, Scripting Guy! I have an Microsoft Office Excel spreadsheet with server names in it, and I would like to be able to use this in a Windows PowerShell script. The thing is, I do not have Microsoft Office or Excel installed on the server. How can I use this spread sheet without installing Office on my server (which I understand is unsupported anyway)?

 

http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept08/hey0911.mspx

Nick MacKechnie : Microsoft Announces SQL Server 2008 Experience

 

Microsoft Announces SQL Server 2008 Experience

On Monday, Sept. 29, Microsoft is launching the SQL Server 2008 Experience online at http://www.SQLServerExperience.com. With more than 500 short videos in 11 different languages, the SQL Server 2008 Experience is a Web site that helps Microsoft’s global customers and partners learn more about SQL Server 2008, Microsoft’s recently released data management and business intelligence platform. SQL Server 2008 provides a trusted, productive and intelligent data platform for business-critical applications. The launch of the SQL Server 2008 Experience kicks off a worldwide readiness outreach that will reach over 350,000 customers, partners and community members through in-person events over the next year.

Visitors to the SQL Server 2008 Experience can expect the following:

• Brief interviews with the SQL Server Engineering team, providing a behind-the-scenes view of the many technologies in SQL Server 2008

• Guidance on a wide range of topics such as business intelligence, compliance, upgrades and application development, including videos from customers showing how SQL Server 2008 is helping them be more successful. Customers that will be featured include Xerox Corp., Baltika Breweries and DriveCam Inc.

• Great deals on SQL Server 2008 books, courses and exams offered by Microsoft Learning

When: Sept. 29, 2008, at 8 a.m. (US Pacific Time)

Where: Online at http://www.SQLServerExperience.com

Nick MacKechnie : Microsoft Announces SQL Server 2008 Experience

Jamie's Junk : Time Series Reporting Stored Procedure - part 3 of 3

 

Time Series Reporting Stored Procedure - part 3 of 3

This post continues part 2 of the series.  In this part of the series I will demonstrate how to create a report using the stored procedure and also how to call the stored procedure from Excel using the Data Mining Addins for Office 2007.

In creating a report, the first thing I did was to use BI Dev Studio to create a new Report Server Wizard Project.  I then, of course, gave the wizard a connection string to my Analysis Services database that contains my Time Series model.

Jamie's Junk : Time Series Reporting Stored Procedure - part 3 of 3

Web Development Helper

 

Web Development Helper

Web Development Helper plugs into Internet Explorer and provides tools for Ajax and Web development.

Web Development Helper

PSS SQL Server Engineers : How to fix your SQL Server 2008 Setup before you run setup...

 

How to fix your SQL Server 2008 Setup before you run setup...

Huh? This is not meant to be a brain teaser. The purpose of this post is to help you avoid problems with SQL Server 2008 setup before you run..well setup. Why not take advantage of known fixes we have for setup to avoid the problem in the first place? This would include a new installation, upgrade, or a install of a new instance or feature.

In this blog post, I'll give you details on how this works and point you to resources for known setup fixes that might affect you.

PSS SQL Server Engineers : How to fix your SQL Server 2008 Setup before you run setup...

XML Workshop XXII - A TSQL RSS Library - SQL Server Central

 

In the last few sessions of XML Workshop we had been looking at ways of generating RSS/ATOM Feeds. You can find the previous sessions here. We have seen how to generate RSS and ATOM feeds in SQL Server 2005 as well as 2000. In the previous sessions, we have seen how to generate RSS and ATOM feeds using FOR XML PATH as well as FOR XML EXPLICIT. If you are working with SQL Server 2005 (and above), you can take advantage of FOR XML PATH and if you are still in SQL Server 2000, you can use FOR XML EXPLICIT.

..

So the focus of this sessions will be writing a function that accepts two XML parameters containing channel and item information and generates an RSS 2.0 feed. We will be able to call the function as in the given example.

XML Workshop XXII - A TSQL RSS Library - SQL Server Central

iT-Workplace - Home of Intelligencia

 

Intelligencia Query delivers add-in functionality for Visual Studio that transforms the task of creating OLAP reports with Microsoft SQL Server Reporting Services. State-of-the-art query tools are combined with a custom data extension that understands how OLAP reporting should work. With Intelligencia Query you get:

• Queries are built the way that your report is to be displayed. There are no restrictions on where each dimension can be displayed or how many dimensions can appear on an axis.
• Only the data required to fulfil your report is returned making IQ faster and more efficient than the default Reporting Services data extension.
• Performance is further enhanced by providing techniques for building drilldown reports that only return drilled data as it is required.
• The flexibility of the query builder dramtically reduces the need to "hand-crank" Mdx leading to better maintainability and reduced skill requirements.
• Table and Matrix report items are supported allowing all types of reports to be created without having to resort to SQL queries via the Analysis Services ODBO interface.
• Intelligencia Query support SAP BW and Congons TM1 in addition to all versions of SQL Server Analysis Services.    

iT-Workplace - Home of Intelligencia

Dynamic MDX in Reporting Services | The Frog-Blog

 

This post explains how you can build a dynamic MDX query in Reportins Services, customised to the users requirements. This can often bring some quite major performance benefits.

Dynamic MDX in Reporting Services | The Frog-Blog

SQL Server Policy-Based Management in SQL 2000/2005

 

Using PBM Against SQL2K and SQL2K5

We get this question a lot: can I use PBM against a SQL2K & SQL2K5 instances. The quick answer is yes but in a limited fashion. PBM is ultimately based on SMO (SQL Server Management Objects) and SMO supports SQL2K, SQL2K5, and SQL2K8. PBM relies on some changes to the DB engine which are not available in versions below SQL2K8, therefore, not all PBM functionality  is available in SQL2K and SQL2K5.

SQL Server Policy-Based Management

SQL Server Data Mining in the Cloud | SQLServerPedia

Analyzing Perfmon data with the SQL 2008 data mining tools.

In our particular example, we can see that Category 8 is the highest TempDB load.  Innnnteresting - earlier, I’d discarded categories over 5 because they represented a low amount of time, but it looks like we’ll have to go back and revisit category 8. Category 8 was Friday mornings from midnight to 4am, so we might have something going on then.

SQL Server Data Mining in the Cloud | SQLServerPedia

Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

 

Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

The Data Miner: September 2008

 

Here's a recap of what's new for Data Mining in SQL Server 2008:

• The Microsoft_Time_Series algorithm has been enhanced to include ARIMA in addition to the existing ARTxp method, and a blending algorithm is now used to deliver more accurate and stable predictions, both short and long term, from a hybrid model. In addition, a new prediction mode allows you to add new data to time series models. (See below for a neat app that will let you explore these features.)

• Built-in support for holdout has been added. You can easily partition your data into training and test sets that are stored in the mining structure and are available to query after processing.

• You can now build mining models on filtered subsets of a mining structure's data (e.g. just male customers), which means that you no longer have to create multiple mining structures and re-read the source data for such variations over a dataset.

• Drillthrough functionality has been extended to make all mining structure columns available, not just columns included in the model. This allows you to build more compact models without sacrificing the ability to producing actionable output reports like targeted mailing lists.

• The much-requested cross-validation feature has been added, allowing users to quickly validate their modeling approach by automatically building temporary models and evaluating accuracy measures across K folds. The feature is available through a new cross-validation tab under Accuracy Charts in Business Intelligence Development Studio, in addition to being accessible programmatically via a stored procedure call.

The Data Miner: September 2008

Business Intelligence LAB - Home

 

Project Description
Business Intelligence LAB is a Microsoft Business Intelligence Framework that is developped based on real life needs.
This repository hosts a set of development done upon SQL Server Business Intelligence Stack.
This covers :
- Data Access
- Sample Cube
- Sample OLAP Viewer
- XMLA
- SSIS custom component
Those component comes from my experience and my needs. Some were published on French .net community (http://www.techheadbrothers.com/) under :
http://www.techheadbrothers.com/Auteurs.aspx/renaud-harduin

Last edited May 18 at 11:21 AM by rhmBILAB, version 2

Business Intelligence LAB - Home

Andrew Kennedy's Blog : Units of Measure in F#: Part One, Introducing Units

Why doesn’t the US use the metric system?  That year, Congress went on summer vacation before the bill was supposed to be passed….

Measure conversion has caused numerous (and disastrous problems).  Think of litres to gallons conversion in an airplane, and really quickly you see how someone could run out of gas if they aren’t using the correct units of measure.

MS is trying to fix this with typed unit recognition in programming languages.

As recently announced in the September 2008 F# CTP (Community Technical Preview), the F# programming language now has full support for static checking and inference of units-of-measure. In this series of articles I'll gently introduce the feature. (If you're not familiar with F#, look here.) We've already been testing out the units-of-measure feature inside Microsoft and I'm amazed at the diversity of applications that are turning up. Of course, there are the obvious applications to scientific computing, and games (which are all about physics, after all), but we're seeing applications in machine learning, finance, search (think click rates, etc) and others.

Andrew Kennedy's Blog : Units of Measure in F#: Part One, Introducing Units