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"

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…

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.

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."