Friday, March 09, 2007

SqlBI.eu

 

Distinct 1.0

Distinct is a partially blocking component that remove duplicates from one flow. Its main advantages against the sort component provided in SSIS are:

  • Memory usage: Distinct does not cache the whole flow (as Sort does) but retains in memory only the distincts, consuming less memory then Sort
  • Distinct is partially blocking where Sort is fully blocking
  • Distinct is freeware, you can easily download sources and adapt it to your needs

Distinct sources can be easily download from the download section at www.sqlbi.eu.

Read More...

Comments (0)

MdxScriptUpdater

MdxScriptUpdater is a simple C# class that simplifies updating MDX Scripts into a cube in a production environment. MdxScriptUpdater is provided in form of a sample source code as is.

There are a lot of scenarios where nightly batches would update parts of the MDX Script of a cube. For example, I had a customer with a calculated member for each year with data. We can define the calculated member by hand, but we would need to remember to create a new one each year. Another case is the customer that wants to consolidate his own calculated members, without requiring a new cube deployment.

Read More...

Comments (0)

The many-to-many revolution

This is the introduction of a paper that describes how to leverage the many-to-many dimension relationships, a feature that debuted available with Analysis Services 2005. After introducing the main concepts, the paper discusses various implementation techniques in the form of design patterns: for each model, there is a description of a business scenario that could benefit from the model, followed by an explanation of its implementation.

Two separate downloads (available on SQLBI.EU project page) contain the full paper in PDF format and SQL Server database and Analysis Services projects with the same sample data used in the paper.

Read More...

Comments (5)

SqlBulkTool 1.0

SqlBulkTool is a command line utility that is used to quickly create a mirror of a database. It reads its configuration from an XML file containing source and destination command strings and a list of all the tables to mirror and then handles the work of copying the database in an automated and highly parallelized way.

The parallelism can use the partition capabilities of SQL Server 2005: to handle a huge table it is enough to partition it to make the tool load it by running each single partition in a separate thread, dramatically increasing table load time. In the case where no partitioning is defined the parallelism is handled at the table level.

Read More...

Comments (2)

DtsToSsisPrepare

DtsToSsis-Prepare is a command line tool that prepares a DTS package for a better migration to an SSIS package.

This article describes the needs for this tool and how to use it.

The project is freeware, full source code is available. Please register on www.sqlbi.eu site if you want to receive mail notifications when bug fixes and new releases will be available.

Read More...

Comments (0)

Source: SqlBI.eu

ExoLogic

 

CubePort™:

Benefits:

CubePort is helping change the BI landscape. It is a product that feasibly permits BI migration, real ROI against software licensing, and offers dramatically improved cube performance. Open up the world of "BI Standardization" possibilities.

The product enables, in a practical way, either migration or replication from Hyperion Essbase to Microsoft SQL Server Analysis Services.

Source: ExoLogic

BI Blogs

 

Book List

Source: BI Blogs

Monday, March 05, 2007

James Goulding Research & Ebooks

 

Research

This page consists of research files, related to the financial markets. Recently, I completely rebuilt the database on this page.  I will be adding data over February and March of 2007. All links are new and all documents have been renamed. Also, some files will be moved to a new page called "Research-Historical Data". That page will be added at a later date.

Source: Research

Also...

This page is an online library of ebooks. All books are in the public domain. Therefore, no copyright laws are being violated. Feel free to download any book and save it to your hard drive

http://www.jamesgoulding.com/ebooks.html

Plus lots of info on Cycles, Calendars, Day Trading, Bonds.

Tuesday, February 27, 2007

Microsoft Live Clipboard

A simple concept and relatively new idea... cut-paste directly into a web page.

http://rayozzie.spaces.live.com/editorial/rayozzie/demo/liveclip/liveclipsample/clipboardexample.html

A few things to try:

  • Right-click -> copy on the orange icon next to one of the initial five contacts. Right-click > Paste on the bottom orange icon without contact data to paste the contact there.
  • Single left-click one of the orange icons so that its contact is highlighted. Type control-c or edit -> copy to copy the contact, and control-v or edit -> paste to paste it in one of the other two icons to replace their existing contacts.
  • Load the page in IE and Firefox side-by-side. Copy / Paste contacts between the two browsers.

miniajax.com - web 2.0 revisited

A showroom of nice looking simple downloadable DHTML and AJAX scripts

http://www.miniajax.com/

Why 260 characters is too long........................................................................................................................................................................................................

 

Are your .NET apps breaking?  Using Enterprise blocks?  See this post.

http://blogs.msdn.com/tomholl/archive/2007/02/04/enterprise-library-and-the-curse-of-max-path.aspx

Monday, February 19, 2007

IsNull != Coalesce

One of the lesser used Sql functions is Coalesce.  Coalesce replaces any given value with another given value if the first value is null.

IsNull does something similar, however there is a bit of differences.

  • IsNull performs a tad better.
  • IsNull will truncate values.
  • Coalesce looks cleaner in scripts. (if the developer knows what coalesce does)
  • Coalesce is more reliable because it doesn't truncate values.

One scenario for using Coalesce is to toggle between selecting all values and a single value in a query.

For example

WHERE COALESCE(@ProductID,ProductID) = ProductID

would select all products if @ProductID is null.

So what happens with this statement if the Product Name column is 50 characters and the @ProductName variable is 25?

WHERE IsNull(@ProductName, ProductName) = ProductName

If you have a product with 26 characters or more, it will not be returned using IsNull.

For multi-value parameters, one option is to use a split utility to parse comma-delimited parameters and use the following statement:

WHERE (@ProductIDs is null or ProductID in in (dbo.fn_utilsplit(@ProductIDs,'''))

Saturday, February 17, 2007

TaporRecipes < Main < WikiTADA

 

This page describes common or interesting sequences of actions, or recipes, for the TAPoR portal. They are organized according to the objective of the recipe. Recipes fall into the three categories of location and identification of ideas, themes or specific terms; analysis of textual devices or themes; or the construction of new entities or corpus. There are also a set of three tutorial recipes included to introduce three common and specific tasks using TAPoR Tools.

Source: TaporRecipes < Main < WikiTADA

 

What is TaPoR?

 

Text Analysis Developers Alliance.

 

If you're developing anything to do with words or text analysis, this is the place to go.

Thursday, February 15, 2007

Davide Mauri - SQL Server & .NET Specialist

 

SQL Scripts
Here you can find some SQL Scripts that I find useful in my everyday work and that may also be helpful to you. Enjoy!
Please note that for SQL Server 2005 scripts, all my objects are created in the sys2 schema. I use this schema to reference all my "system" objects.
If you want to do the same, you have to create the sys2 schema before. You can do it simply using the CREATE SCHEMA sys2 statements.

sys2.indexes
A UDF that shows all indexes present on a table. For any index shows of which column it's made.
Usage: SELECT * FROM sys2.indexes('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

sys2.indexes_size
A UDF that shows how much big your indexes are. For any index shows the size in kb and mb.
Usage: SELECT * FROM sys2.indexes_size('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

sys2.indexes_stats
A simple wrapper around dm_db_index_physical_stats that beside index fragmentation statistics also shows index names. Usage: SELECT * FROM sys2.indexes_stats('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

sys2.indexes_usage
This UDF shows how (and if) indexes are used by SQL Server.
Usage: SELECT * FROM sys2.indexes_usage('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

Source: Davide Mauri - SQL Server & .NET Specialist

Wednesday, February 14, 2007

Phidgets Inc. :: Unique and Easy to Use USB Interfaces

I used to work for a company that created fibreglass flagpoles using a few thousand lines of Basic code and a CNC lathe.

WPF, USB and Phidgets seem like they are going to make things a lot easier in the future.

What Are Phidgets?

Phidgets are an easy to use set of building blocks for low cost sensing and control from your PC. Using the Universal Serial Bus (USB) as the basis for all Phidgets, the complexity is managed behind an easy to use and robust Application Programming Interface (API). Applications can be developed quickly in Visual Basic, VBA (Microsoft Access and Excel), LabView, Java, Delphi, C and C++.

Source: Phidgets Inc. :: Unique and Easy to Use USB Interfaces

theWPFblog » Examples

 

In the couple of days that I’ve been experimenting with WPF/E, I have finally come to terms with how it all works and what you need to create a new project. Microsoft has released a template for Visual Studio but I think that it does more harm than good. Plus it only currently works in the full version of VS and not in the express editions. In this post I will explain the basic building blocks that make up a WPF/E application. I’ll use the typical Hello World scenario.

HelloWorld.xaml
Your XAML file is the heart of the WPF/E application. Much like in WPF, XAML defines all of the visual interface and animations. You can create the XAML code in Blend, Visual Studio, or in Notepad. Visual Studio gives you Intellisense code completion but since XAML is XML-based, any XML editor will speed things up. Using Blend is great for visually laying out your graphics, but it spits out WPF XAML not WPF/E XAML so you will have strip down the resulting code to make everything Canvas-based.

To create a compliant XAML file you need to use a Canvas as your root element with the two namespace declarations seen below. Then to add the Hello World text I’m simply adding a TextBlock control to the Canvas.

Source: theWPFblog » Examples

Bryant Likes's Blog : WPF/E Matrix Reloaded

 

WPF/E Matrix Reloaded

Last week Chad posted his Matrix style text animation which I thought was very cool. I wanted to experiment a little with keyboard events and using Glyphs. The result is the Matrix Reloaded:

Source: Bryant Likes's Blog : WPF/E Matrix Reloaded

Friday, February 09, 2007

Armadillo Systems: Books

These guys designed the British Library WPF application. 

Armadillo Systems is probably the foremost provider of interactive solutions for books, documents and manuscripts in the world.

We specialise in producing unique applications that provide access and interpretation for items that would otherwise remain under glass and we understand the tension that exists between the need for conservation and the requirement of access. We have been working with libraries and museums since 1997 and developed the award-winning Turning the Pages TM technology with the British Library.

We have developed a methodology of approaching bibliographic projects that encompasses thinking about the book in 5 ways:

  • the book as object
  • the book as content
  • the book as icon
  • the book as window into the past
  • the book as gateway to future learning

Source: Armadillo Systems: Books

Lance's Whiteboard : Troubleshooting Sql Reporting Services custom assemblies (extensions &amp; what not)

 

Troubleshooting Sql Reporting Services custom assemblies (extensions &amp; what not)

I dont have time for a lengthy post, so here is some link-love for some great articles that have helped me lately with some data extensions and other custom code I am writing for Sql Server 2005 Reporting Services:

Source: Lance's Whiteboard : Troubleshooting Sql Reporting Services custom assemblies (extensions & what not)

WINDOWS VISTA AVALON WPF WEB3D GAMES

A bit of background on Web 3D and where Microsoft is going with it. 

INTRODUCTION

IMPORTANT: We are using, at this tutorials serie, the beta version Dec CTP. Some sintax is different for the last WPF version!!!

Web3D has a 10 years history of big disasters. Since the days of the launch of VRML until the phaseouting of the ADOBE web3D product:"Atmosphere" at 2005 , nothing works.

It's important to say that Web3D IS NOT Internet3D. We are talking about a 3D application having a "webpage dependence", not about things like: ActiveWorlds, SecondLife or internet multiplayers games.

One of the last available products for web3D is Shokwave3D, part of Macromedia/ADOBE Director. But it's frozen! We (DMU) have a tutorials serie about Shockwave3D that you can read here. But who knows if the product will be phaseouted soon by ADOBE , like they did with Atmosphere...

Now Microsoft is launching its Web3D. It's part of the new Windows Presentation Foundation (codename:Avalon). It's in Beta phase now. It has a new architecture for the launching of the application: ClickOnce that can be "page dependent". We talk about how to publish WPF applications at our "WPF PROGRAMMING - BASICS" tutorials that you can find at our site (go to this link. ).

At this tutorials serie we will work creating generic applications but you can publish them using Visual Studio, any version.

Source: WINDOWS VISTA AVALON WPF WEB3D GAMES

Wednesday, February 07, 2007

Download details: Visual Studio Code Name "Orcas" January 2007 CTP

 

Microsoft Pre-release Software Visual Studio Code Name "Orcas" - January 2007 Community Technology Preview (CTP)

Source: Download details: Visual Studio Code Name "Orcas" January 2007 CTP

Package Configurations not so portable? - MSDN Forums

 

The best way we've found is to:

  1. Edit the package and create an XML configuration file with the properties you need.
  2. Save the file to a known location
  3. Create a system variable
  4. Populate the variable with the full path and file name of the above XML file
  5. Edit the package and remove the package configuration for the XML file.
  6. Create a new configuration of type Environment variable and type in the name of the system variable
  7. The new package configuration should show with "Indirect XML configuration file" once the above has been done.

This can obviously be tuned to take keeping packages in the dark into account etc. etc.

Source: Package Configurations not so portable? - MSDN Forums

 

 

Everyone seems to be trying to solve the issue of the XML file path being stored in the configuration organizer and that in turn requiring all file system environments having the same structure.  If you are storing the package in a file system as opposed to MSDB, there is a very easy solution  .... remove the path and leave just the XML file name.  The XML config file will then need to be in the same folder as the package, but it then becomes file structure independent.

Further, the tool (SSIS) should not dictate whether it is a good idea or not to maintain similar file structures across environments.  Whether it is a good idea (personally I believe it is) the tool should support a reasonable solution.

»Rape me, my friend...

Gates “dares anybody” to exploit Vista | The Apple Core | ZDNet.com

February 6th, 2007
Gates “dares anybody” to exploit Vista

Source: » Gates “dares anybody” to exploit Vista | The Apple Core | ZDNet.com

Getting Real

Want to build a successful web app? Then it's time to Get Real. Getting Real is a smaller, faster, better way to build software.

Getting Real

Here are the 16 chapters and 91 essays that make up the book.

Source: Getting Real

Friday, January 26, 2007

www.xmethods.net

 

Welcome to XMethods.

Emerging web services standards such as SOAP, WSDL and UDDI will enable system-to-system integration that is easier than ever before.  This site lists publicly available web services.

Source: www.xmethods.net

Thursday, January 25, 2007

Eli Robillard's World of Blog. : Eli's SharePoint Resources

 

Eli's SharePoint Resources

What's Here

Welcome to my new and improved list of SharePoint Resources! This is a hub for SharePoint Resources with two advantages: All resources are hand-picked and vetted for quality, and each topic contains a with hand-tuned search designed to return the latest content which you can then filter further to find what you need.

Source: Eli Robillard's World of Blog. : Eli's SharePoint Resources

Tuesday, January 23, 2007

HOW TO: Get all the tech news you need (in 20 minutes a day) - Valleywag

 

Optional time-saver: Instead of reading the sources below by going to the different sites, consider reading the RSS fees on Google Reader or Bloglines.

1. Skim the headlines of these top news sources:
Digg: Technology (Ranked by most popular, so you can quit halfway down)
Techmeme (Same as above, but also note the recent stories in the right column)
GigaOM (Industry/business news)
Engadget (Gadget news)

2. Skim these sources for commentary:
Paul Kedrosky's Infectious Greed
Valleywag (Really -- it's newsy now)
Techdirt

3. Read these mainstream sources:
CNET News
NY Times Technology
Financial Times

4. Once or twice a week, check in with these sources:
BusinessWeek
Wired News
Wired Magazine

5. Once a week, listen to this podcast during your commute: This Week in Tech

Source: HOW TO: Get all the tech news you need (in 20 minutes a day) - Valleywag

Sunday, December 17, 2006

Swivel - Youtube for Data?

Swivel Co-founders Dmitry Dimov and Brian Mulloy start off by describing their company as “YouTube for Data.” That’s a good start for someone trying to understand it, because the site allows users to upload data - any data - and display it to other users visually. The number of page views your website generates. Or a stock price over time. Weather data. Commodity prices. The number of Bald Eagles in Washington state. Whatever. Uploaded data can be rated, commented and bookmared by other users, helping to sort the interesting (and accurate) wheat from the chaff. And graphs of data can be embedded into websites. So it is in fact a bit like a YouTube for Data.

-- from TechCrunch

Friday, December 01, 2006

Best Open Source Project?

http://www.grupthink.com/topic/821

What is the absolute best that the open source model has to offer? The term is usually applied to software specifically, but I'd like to use it here to refer to any community developed project whose collaborative efforts are freely available to all to use and/or modify in any not-for-profit manner; This could include content, health, politics, religion, media, etc..

Guy Kawasaki interviewing Woz

http://www.tuaw.com/2006/11/29/guy-kawasaki-interviewing-woz/

Friday, November 03, 2006

Wednesday, October 25, 2006

Environment Variables - Path parser

Here's a quick way to display a Windows system search path. The %PATH% environment variable contains a list of folders. When a command is issued at the CMD prompt, the operating system will first look for an executable file in the current folder, if not found it will scan %PATH% to find it.
This script iterates over the path statement, splitting out the individual folders and presents each on a single line.

Use the PATH command to display or change the list of folders stored in the %PATH% environment variable.

To use this, create a new text document and paste the following code.  Save the file as viewpath.cmd and presto!, you can list the contents of your system by doubl-clicking the file:

::viewpath.cmd
@echo off
::echo the path one line at a time
for %%G in ("%path:;=" "%") do @echo %%G
pause

Tuesday, October 03, 2006

Microsoft Product Support throws a non-friendly exception

http://support.microsoft.com/oas/default.aspx?&c1=505&gprid=10274&f=25&MSPPError=-2147197912

Server Error in '/OAS' Application.
--------------------------------------------------------------------------------

Unhandled Execution Error
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException:

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[COMException (0x8004922f)]
Microsoft.Passport.RPS.Native.IRPSHttpAuth.AuthenticateRawHttp(String siteName, String httpVerb, String path, String QS, String httpVersion, Boolean bHTTPs, String httpHeaders, String httpBody, Object pAuthResultsBag) +0
Microsoft.Passport.RPS.RPSHttpAuth.Authenticate(String siteName, HttpRequest request, RPSPropBag propBag) +478
Microsoft.MSCOM.MemberServices.Passport.PassportTicket.GetTicket(HttpRequest request, String site, TicketState& ticketState, String& responseHeaders) +100

[PassportException: The PassportTicket.GetTicket() failed to initialize Passport Ticket. [(Error=0x8004922F) Passport 'f' error (f=25, MSPPError=0x80045C28) is encountered in the query string. Check Application event log for details.]]
Microsoft.MSCOM.MemberServices.Passport.PassportTicket.GetTicket(HttpRequest request, String site, TicketState& ticketState, String& responseHeaders) +407
Microsoft.MSCOM.MemberServices.Passport.PassportTicket.GetTicket(HttpRequest request, TicketState& ticketState, String& responseHeaders) +62
Microsoft.MSCOM.MemberServices.Passport.PassportAuthenticationModule.AuthenticateRequestHandler(Object sender, EventArgs e) +219

[PassportAuthenticationModuleException: The PassportTicket.GetTicket() failed to initialize Passport Ticket. [(Error=0x8004922F) Passport 'f' error (f=25, MSPPError=0x80045C28) is encountered in the query string. Check Application event log for details.]]
Microsoft.MSCOM.MemberServices.Passport.PassportAuthenticationModule.AuthenticateRequestHandler(Object sender, EventArgs e) +1004
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Thursday, May 25, 2006

Key columns and dimension attributes

add key to hierarchy & create duplicate key attribute -

When you need to see the key of a dimension within an attribute or hierarchy, add a duplicate attribute for the key, and use this duplicate attribute rather than the existing key attribute.  This will increase aggregations and (supposedly) improve query performance.

Thursday, April 20, 2006

Symmetry Corporate Solutions: Performance Reporting Framework, PROOF

Symmetry Corporate Solutions: Performance Reporting Framework, PROOF: " It's about time someone packaged BI Best practices into an OLAP model generator like PROOF. Symmetry's long experience in BI applications shows, and makes it much easier and faster for non-experts to build and, more importantly, maintain sophisticated multidimensional business models.”

Nigel Pendse, Author
The OLAP Report and The OLAP Survey

PROOF is like having an expert BI developer permanently on staff. PROOF makes the hard part of developing performance management applications easy. By managing metrics in a central repository, it guarantees a single version of the truth and significantly reduces development time, redundant programming, and maintenance.”

Claudia Imhoff,
President and Founder
Intelligent Solutions"

Monday, April 17, 2006

Reporting Services with Relational Data and MDX

One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.

What if you don't want to or can't?

Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.

Steps to lookup values from a reference table in SQL:

Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)

Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")

In the Code Properties window, try the following:

public dict as new System.Collections.Generics.Dictionary(Of System, System)

function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function

function getValue(value as object) as object
return dict(value)
end function

This is untested code and needs further work, but it should provide an idea of how to accomplish the task.

If you have properly bound a table to the first list control, you should be able to lookup results in the second table.

This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.

I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.

Project Management 101: Creating the Right Ripple

Simple Talk » Blog Archive » Project Management 101: "Joint Application Development

Essentially, I implemented a Joint Application Development (JAD) methodology. Everyone, from testers to junior developers and interns, participated in every meeting with the client. There was no endless communication string; everyone got their knowledge first hand. The entire development team, the client and anyone who had discrete business knowledge of the problem space, assembled in a room. The goal of these meetings was to:

* Define the problem/s that the solution is expected to solve
* Define each process –think use case – in the system in exact detail
* Define screen layouts
* Walk through each process with screen shots, roll play if necessary – people will often seeing missing steps when this is done repeatedly. If a new step is added start over again
* Define a complete and specific requirements document that the solution will be based on
* Get the client and the whole team to sign-off on the requirements document

By walking through each process and clarifying every detail with the client, and the entire team, we effectively killed scope creep. The client was confident that we understood their needs. In place of vague statements such as “we need a page to create customers”, we had we had sign-off on a clear, concise requirements document of the following form:

The system will maintain a list of customers. A customer record with be defined as:
# Customer ID – System Generated
# First Name
# Last Name
# Full Address (2 address fields, city state and postal code)
# Phone Number
# Email address

Each field with the exception of Customer ID will be fully editable. A drop-down list will be used to select a state. Validations include:
# First Name – required
# Last Name – required
# Postal Code- Required, must be 5 or 9 numbers only
# Phone Number – 9 numbers only
# Email Address – valid format, no anonymous accounts (Yahoo, Hotmail, GMail etc)

You will reach the customer edit page thought a search engine that allows you to search for customers on any combination of the following fields.
# First Name
# Last Name
# Postal Code

…And so on…

Sunday, April 16, 2006

Adding Relational Lookups to Reporting Services

Have not tested this in a production scenario yet, however this looks to be one way to use the results of 1 data set as a lookup for another data set.

1. Create a Reporting Services Report with 2 lists - 1 for lookups & 1 for results.
2. In the lookup list, add a textbox with this code. =Code.setValue(Fields!FirstName.Value, Fields!LastName.Value)
3. In the results list, add a textbox with this code = Code.getValue(Fields!FirstName.Value)

4. Add code:
public openWith as new System.Collections.Generic.Dictionary(Of String, String)

Function setValue(value as object, value2 as object) as object
openWith.Add(value, value2)
return 0
end function

Function getValue(value as object) as object
return openWith(value)
end function


The result should be that you can now lookup unique keys in the dictionary. This would be useful for scenarios involving relational data and MDX results. It could also be used for replacing system application values with their 'friendly-name' reporting equivalents.

Tuesday, April 11, 2006

MCITP: Business Intelligence Developer

MCITP: Business Intelligence Developer: "How to Earn Your MCITP: Business Intelligence Developer

MCITP candidates must first complete the requirements for the MCTS: SQL Server 2005 certification (one exam). After achieving this Technology Specialist certification, candidates earn their MCITP: Business Intelligence Developer by passing two required exams. The following table provides a complete list of exams and related training resources. For exam dates and specific areas of focus, see individual Exam Preparation Guides as the exams become available."

Thursday, March 30, 2006

Using Expression Variables in SSIS

Here's a way to use Expression Variables in SQL Server Integration Services to dynamically create a connection string:

1. Create a new variable "SS_DST_Server"
2. Create a new variable "SS_DST_Database"
3. Create a new variable "SS_DST_Conn"
4. Set 'Use as Expression' property on SS_DST_Conn to true.
5. Under the expression, type in the connection string and append " + @[User::SS_DST_SERVER] + ";" where the server goes, and " + @[User::SS_DST_Database] + ";" where the database goes. Note it should start with =" and end with "

for example:
"Provider=SQLNCLI;Server=" + @[User::SS_DST_SERVER] +";Database=" + @[User::SS_DST_DATABASE] + ";UID=sa;PWD=asdasd;"

Now, instead of having to configure the connection string in a config file, you can store it in the package, and pass in the database and server name as a parameter.

There are other useful things you can do with this procedure.

Create a variable called "DATE_FILTER" and an expression variable "SQL_LOAD" with the expression "Select * from MyTable where mydate = '" + @[User::DATE_FILTER] + "'"

Dynamic SQL in your SSIS package.

Note variables are case-sensitive, so upper case is good practice.

Here's some sample connection strings...

www.connectionstrings.com

Monday, March 27, 2006

Swapping partitions in SSAS 2005 - MSDN Forums

Swapping partitions in SSAS 2005 - MSDN Forums: "You should be able to use the new database synchronization feature of AS 2005:

http://msdn2.microsoft.com/en-us/library/ms174928(SQL.90).aspx

>>
Synchronizing Analysis Services Databases

The Synchronize Database Wizard synchronizes two Microsoft SQL Server 2005 Analysis Services (SSAS) databases by copying the data and metadata from a database on a source server to a database on a destination server. You can also use this wizard to deploy a database from a staging server onto a production server, or to synchronize a database on a production server with the changes made to the data and metadata in a database on a staging server.

While the wizard synchronizes the data between the two databases, users can continue to query the destination database. After synchronization finishes,, Analysis Services automatically switches the users to the newly copied data and metadata, and drops the old data from the destination database."

Friday, March 24, 2006

Integration Services - Powerful and painful

In my opinion, one of the more idiotic things about SQL Server 2005 is Integration Services. Not the product itself, which is actually quite powerful, and since I would not touch DTS with a ten-foot pole picking it up was relatively easy for me. It is fun to work with, didn't crash on me a whole lot (unlike Visual Studio 2005 which is like watching Houdini, since it disappears randomly) and was fairly intuitive once I got the basics down.

What stinks is the methods of execution of these packages in a production environment.

Two main problems for me are package security and remote execution.

1. Password Encryption: What's with all of the options for package security? User Keys, Certificates, Package Passwords. How about abstracting this stuff right out of the package itself and into another tool, like a code obfuscator. It's wonderful to have, but sometimes, like a door, you don't always need a lock on it. Why do I have to type in a package password for EVERY PACKAGE everytime I hit Build? That's like typing in a password for every file when I type DIR in DOS. Also, what's with passing the /DECRYPT password in every time I have to run from a command-line? Can't we stick this in an encrypted config file somewhere?

2. Remote Execution - This is where I got burned. SQL Agent is the recommended means of executing packages on the SQL Server from a remote PC. So how do you pass in passwords and other parameters to the command line? I ended up creating two command-line tasks with sqlcmd, one to edit the job task step sp_update_job_step and another to run the job sp_start_job. Of course, when you run sp_start_job it immediately comes back. This could cause a problem for jobs that have to wait for others to run before starting, so you need to grab another custom script that checks every 10 seconds to see if it's running.

Other things that bug me about SSIS - not knocking the developers who put tons of effort into this - but just stuff that bugs me.

- The 5-10 second delay executing packages from command line.
- Clicking the play button in Visual Studio doesn't play the package selected. It could do many things, depending on what mood it's in.
- The script editor. Sorta reminds me of Excel VBA but without the ability to tab back and forth between the package and the script.
- Passing parameters from a command line. %8 %9 %10 %11 just doesn't cut it for me.
- No encrypted parameters in config files.

Deployment question: SQLDB - SSIS on different boxes with SQL Agent for scheduling - MSDN Forums: "SQL Agent is my preferred scheduler, and maybe yours too, but many organisations have their own standards. Many houses use IBM/Tivoli for example, and SQLAgent is not required for SSIS. If you would prefer to use it, then yes you are stuck. I think buyng SQL Server for SQLAgent is an overkill, but if you look at that SQL Instance in the same way as you look at MSDE, then it is no big deal. There are plenty of proucts our there that install DB engines. I saw a report that said Informix (I think that was the one) was one of the most widely deployed DBs, and that is because CA shoved it under the covers of ArcServe backup, probably for the scheduling :) "

Yes, my client does prefer to use a Unix-based scheduler, and apparently this means we're stuck using SQL Agent, called from another scheduler.

Thursday, March 16, 2006

Part 1: Automating the XML Data Mapping Process in Excel 2003

Part 1: Automating the XML Data Mapping Process in Excel 2003: "Conclusion
The sample code in this article is generic and can be used for most scenarios requiring support of flexible XML mapping with little or no modification. Currently we use this code in a project called 'The Rhythm of the Business 2.0,' which supports a flexible and constantly changing Excel template used by more than 1,000 Microsoft employees to assess the entire company's performance on a monthly basis. In our project we also needed to harvest the unaltered data-point descriptions for use in a reporting application. We did this by adding meta tags, using square brackets for marking up the description positions. (This article does not include the code that retrieves the description values.) We encountered another reporting issue related to the Excel formulas. In a future article, we will describe how to take advantage of the grid structure to programmatically extract and store Excel formulas in a Microsoft SQL Server database and replicate the UI-side calculations using dynamic SQL."

Saturday, March 04, 2006

dotProject Demo :: dotproject - Open Source Software :: Open Source Project and Task Management Software

dotProject Demo :: dotproject - Open Source Software :: Open Source Project and Task Management Software: "Thank you for your interest in our web-based project management tool.

dotProject is built using free open-source applications and is produced and maintained by a small, but dedicated group of volunteers. dotProject is programmed in PHP, and utilises MySQL for a backend database (although other databases such as Postgres could also be used).
Our recommended server platform includes Apache 1.3.27, PHP 4.2+, and MySQL. In the spirit of free, peer-reviewed, open source application development, we would also encourage you to use an operating system such as Linux, FreeBSD, or OpenBSD. However, additional operating systems such as Windows, Mac, and other flavours of *nix are also supported. "

Tuesday, February 07, 2006

71-528 Study Group Transcript - 2005-11-02

71-528 Study Group Transcript - 2005-11-02

It's law to include on goverment sites things like ALT tags etc for accessibility I do believe

AndrewCoates says:

Certainly is in the US

AndrewCoates says:

There's an accessability checker in the IDE that tells you whether you've complied

AndrewCoates says:

the rules are different in the US from Europe

michaelmileos says:

thats cool

AndrewCoates says:

In the US it's called something like "Section 508"

tathamoddie says:

localization: http://www.dotnetjunkies.com/WebLog/anoras/archive/2005/02/13/54271.aspx

tathamoddie says:

Globalization is a process of identifying all the parts of your application that need to be different for respective languages and separate them from the core application.

tathamoddie says:

Localization

tathamoddie says:

Localization is process of creating and configuring your application for a specific language.

Study Group for 71-528. 31 October 2005

Study Group for 71-528. 31 October 2005

Deadeye (~dead_eye_@218.214.2.XXX) has joined the conversation.

mikemileos says:

hi dan

Deadeye says:

hey

Deadeye says:

what have i missed?

mikemileos says:

we were just wrapping it up

Deadeye says:

:(

mikemileos grins

mikemileos says:

nah, we just started

Sunday, February 05, 2006

I need more exams?!?

Vishal Joshi's Tangent: "MCDBA Upgrade to MCITP Database Administrator
In the new family to become MCITP Database Administrator it is required to achieve MCTS SQL Server 2005 Credential by taking Exam 70-431 and then by passing two PRO exams 70-443 and 70-444.
For folks who are already MCDBA this three exam path becomes two exam path...
An MCDBA will have to achieve MCTS SQL Server 2005 credential by passing Exam 70-431 and then can take & pass an upgrade exam 70-447... So basically instead of passing the two exam 70-443 and 70-444, MCDBAs will have to pass just one exam.

This was a quick snapshot of SQL Server 2005 credential upgrade... Now lets talk about Visual Studio 2005 credential upgrades..."

Configuration API Improvements in ASP.NET 2.0

Configuration API Improvements in ASP.NET 2.0: " AQA------------ FnvpHa1iy4Oww= "

Encrypting connection strings in the web.config

Exam 70-536 - Wikibooks, collection of open-content textbooks

Exam 70-536 - Wikibooks, collection of open-content textbooks