This blog is subject the DISCLAIMER below.

Wednesday, October 21, 2009

Wondering about Project Managers

I had a couple of issues lately with a PM (Project Manager) at work which made me ask myself a couple of questions & wonder what others might be thinking of it.. I'll give my humble opinion here to start a conversation, coz I badly wanna here how others would answer them.

  • Should PMs know/ask much about technical decisions?
    • IMHO, I thing the answer is NO!! PMs should be asking for less fine details like what's the estimations for doing that option or so.. But they shouldn't be asking why we are using X, or Y technology/approach to tackle a problem.

  • In an agile process, where should PMs stand? What exactly is their Role? Should there be PMs in agile in the first place? Or Should they be replaced by a PO (Product Owner), or -may be- a Scrum Master?
    • That's a question that really confuses me a lot.. I can't give a definite answer.. Should they replace POs.. Hmm. I guess not.. On one hand, a PO should be the one directing the project, knowing what the customer want.. But still, a PO -probably- should have some technical background.. So according to my answer to the previous question, PMs can't replace POs..
    • On the other hand, Replacing a Scrum Master, is a bit too far for PMs, Scrum Master are supposed to be in favor of the development team, ie a facilitator & protector for the dev team; & PMs are notorious of failing to do so.. :D
    • Another option -that just came to me right now- is PMs replacing customer in environments where it's difficult to involve the customer.

I'm desperately waiting for your answers to either of the above questions.. I'm really confused & don't have enough knowledge actually to answer them.
I might be adding other questions but for now that's what is on my mind..

.. more.

Sunday, October 18, 2009

Deploying Reports in Reporting Services Programmatically

May be it's my bad luck may be something else, but I didn't find a complete post how to deploy Reports, and Data Sources in Reporting Services in away like BIDS.

First it's a tool I developed to automate the deployment of BI solution (Creating Data warehouse, installing SSIS packages, creating Jobs, create Cubes and installing reports on Reporting Services Server).

If you don't have time to read and just need to download the tool, here you're
Source Code: http://cid-3e2288e7a8e55f56.skydrive.live.com/self.aspx/Public%20folder/Deploying%20Reports%20in%20Reporting%20Services%20Programmatically.zip

Herein, I'll talk about the last thing which is deploying reports.

P.S: It's my way in designing such task (Installing reports on Reporting Services Server) and it's not standard or anything else just (follow your heart :))



Let's begin, I assume you, me, or anybody else has these 3 XML files one for folders, one for data sources and one for reports.

[caption id="attachment_772" align="aligncenter" width="450" caption="Folders' XML Scheme"]Folders XML File Scheme[/caption]

Name: Folder name to be created on Reporting Services.
ParentFolder: '/' means on the top == no parent folder.

[caption id="attachment_773" align="aligncenter" width="450" caption="Data Sources' XML Scheme"]Data Sources' XML Scheme[/caption]

Name

Folder

Description

HideInListView

Enabled

ConnectionString

Extension

CredentialRetrieval

WindowsCredentials

ImpersonateUser

ImpersonateUserSpecified

Prompt

UserName

Password

EnabledSpecified

Name: Data Source name to be created on Reporting Services.
Folder: The folder in which Data Source should be in, if we use '/' means on the top == no parent folder.
Description: Data Source Description.
HideInListView: True to hide it in the Reporting Services, otherwise False.
Enabled: True to be enabled, otherwise not enabled.
ConnectionString: Data Source connection string.
Extension: Configured according to the provider for more details see below table...






































ProviderExtension
Microsoft SQL ServerSQL
OLE DBOLEDB
Microsoft SQL Server Analysis ServicesOLEDB-MD
OracleORACLE
ODBCODBC
XMLXML
SAP NetWeaver BISAPBW
Hyperion EssbaseESSBASE

CredentialRetrieval: How Data Source will retrieve the credential.
WindowsCredentials: True to use Windows credential otherwise it'd use the credential provided in this XML (Username, and Password).
ImpersonateUser: Indicates whether the report server attempts to impersonate a user by using stored credentials after a data processing extension has established an authenticated connection to a data source.
ImpersonateUserSpecified: Gets or sets a value that indicates whether the ImpersonateUser property is specified.
Prompt: Gets or sets the prompt that the report server displays to the user when prompting for credentials.
UserName: Gets or sets the user name that the report server uses to connect to a data source.
Password: Sets the password that the report server uses to connect to a data source. Write-only.
EnabledSpecified: Gets or sets a value that indicates whether the Enabled property is specified.

More details on these properties http://msdn.microsoft.com/en-us/library/reportservice2005.datasourcedefinition_properties.aspx

[caption id="attachment_774" align="aligncenter" width="450" caption="Reports' XML Scheme"]Reports' XML Scheme[/caption]

Name: Report Name.
Path: .RDL file path.
Folder: The folder in which Report should be in, if we use '/' means on the top == no parent folder.
DataSource: Report's Data Source name of Reporting Services.

And these configuration keys

[caption id="attachment_777" align="aligncenter" width="450" caption="Configuration keys"]Configuration keys[/caption]

ReportsXMLFilePath: Reports' XML File Path
DataSourcesXMLFilePath: Data Sources' XML File Path
FoldersXMLFilePath: Folders' XML File Path
ReportingServerURL: URL of Reporting Services

Open visual studio and create a C# console application (we don't need any interaction with user everything configured in the application configuration file)

From the project main menu Add Web Reference or Add Service Reference then Advanced then Add Web Reference...

[caption id="attachment_778" align="aligncenter" width="450" caption="Add Web Reference"]Add Web Reference[/caption]

[caption id="attachment_779" align="aligncenter" width="450" caption="Add Reporting Services Reference"]Add Reporting Services Reference[/caption]

URL: http://{Server-Name}/reportserver/ReportService.asmx
Web reference name: Give it meaningful name..

What we did is adding web service by which we can talk to Reporting Services to ask it to do something like (create report, create data source, etc...).

Let's write some very simple C# code
We have method called DeployReports this method calls 3 other methods in order (CreateFolders, CreateDataSources, and CreateReports)
/// <summary>

/// Deploys Folders, DataSources, and Reports in Reporting Services by values configured in the application configuration file.

/// </summary>

private void DeployReports()

{

CreateFolders(

ConfigurationSettings.AppSettings["FoldersXMLFilePath"]);

CreateDataSources(

ConfigurationSettings.AppSettings["DataSourcesXMLFilePath"]);

CreateReports(Report.GetReports(

ConfigurationSettings.AppSettings["ReportsXMLFilePath"]));

}



/// <summary>

/// Creates Folder in Reporting Services.

/// </summary>

/// <param name="folderXMLFilePath">XML file path holds folder information.</param>

private void CreateFolders(string folderXMLFilePath)

{

ReportingService reportingServicesClient =

new ReportingService();

reportingServicesClient.Credentials = System.Net.CredentialCache.DefaultCredentials;

XDocument xmlDoc = XDocument.Load(folderXMLFilePath);

try

{

var result = from c in xmlDoc.Descendants("Folder")

select new

{

name = (string)c.Element("Name").Value,

parentFolder = (string)c.Element("ParentFolder").Value

};

foreach (var row in result)

{

reportingServicesClient.CreateFolder(row.name, row.parentFolder, null);

Logging.Log(string.Format("Folder {0} created successfully", row.name));

}

}

catch (Exception er)

{

Logging.Log(er.Message);

}

}

/// <summary>

/// Creates Data Sources in Reporting Services.

/// </summary>

/// <param name="datasourceXMLFilePath">XML file path holds Data Sources information.</param>

private void CreateDataSources(string datasourceXMLFilePath)

{

ReportingService reportingServicesClient =

new ReportingService();

reportingServicesClient.Credentials = System.Net.CredentialCache.DefaultCredentials;

DataSourceDefinition tempDataSource;

XDocument xmlDoc = XDocument.Load(datasourceXMLFilePath);

try

{

var result = from c in xmlDoc.Descendants("DataSource")

select new

{

name = (string)c.Element("Name").Value,

folder = (string)c.Element("Folder").Value,

description = (string)c.Element("Description").Value,

hideInListView = (string)c.Element("HideInListView").Value,

enabled = (string)c.Element("Enabled").Value,

connectionString = (string)c.Element("ConnectionString").Value,

extension = (string)c.Element("Extension").Value,

credentialRetrieval = (string)c.Element("CredentialRetrieval").Value,

windowsCredentials = (string)c.Element("WindowsCredentials").Value,

impersonateUser = (string)c.Element("ImpersonateUser").Value,

impersonateUserSpecified = (string)c.Element("ImpersonateUserSpecified").Value,

prompt = (string)c.Element("Prompt").Value,

userName = (string)c.Element("UserName").Value,

password = (string)c.Element("Password").Value,

enabledSpecified = (string)c.Element("EnabledSpecified").Value

};

foreach (var row in result)

{

CredentialRetrievalEnum credentialRetrieval;

EnumConverter ec =

new EnumConverter(typeof(CredentialRetrievalEnum));

credentialRetrieval = (CredentialRetrievalEnum)ec.ConvertFromString(row.credentialRetrieval);

tempDataSource = new DataSourceDefinition();

tempDataSource.CredentialRetrieval = credentialRetrieval;

tempDataSource.ConnectString = row.connectionString;

tempDataSource.Enabled = bool.Parse(row.enabled);

tempDataSource.EnabledSpecified = bool.Parse(row.enabledSpecified);

tempDataSource.Extension = row.extension;

tempDataSource.ImpersonateUserSpecified = bool.Parse(row.impersonateUserSpecified);

tempDataSource.ImpersonateUser = bool.Parse(row.impersonateUser);

tempDataSource.Prompt = row.prompt;

tempDataSource.WindowsCredentials = bool.Parse(row.windowsCredentials);

//tempDataSource.UserName = row.userName;

//tempDataSource.Password = row.password;

try

{

reportingServicesClient.CreateDataSource(row.name, row.folder, true, tempDataSource,

null);

Logging.Log(string.Format("Data Source {0} has created successfully", row.name));

}

catch (SoapException e)

{

Logging.Log(e.Detail.InnerXml.ToString());

}

}

}

catch (Exception er)

{

Logging.Log(er.Message);

}

}

/// <summary>

/// Creates Reports in Reporting Services.

/// </summary>

/// <param name="reports">XML file path holds Reports information.</param>

private void CreateReports(Report[] reports)

{

ReportingService rsc =

new ReportingService();

rsc.Credentials = System.Net.CredentialCache.DefaultCredentials;

foreach (Report aReport in reports)

{

Byte[] definition = null;

Warning[] warnings = null;

try

{

FileStream stream = File.OpenRead(aReport.Path);

definition = new Byte[stream.Length];

stream.Read(definition, 0, (int)stream.Length);

stream.Close();

}

catch (IOException e)

{

Logging.Log(e.Message);

}

try

{

rsc.CreateReport(aReport.Name, aReport.Folder, true, definition, null);

#region Setting Report Data Source

DataSourceReference reference = new DataSourceReference();

reference.Reference = aReport.DataSource;

DataSource[] dataSources = new DataSource[1];

DataSource ds = new DataSource();

ds.Item = (DataSourceDefinitionOrReference)reference;

ds.Name = aReport.DataSource.Split('/').Last();

dataSources[0] = ds;

rsc.SetReportDataSources(aReport.Folder + "/" + aReport.Name, dataSources);

#endregion

if (warnings != null)

{

foreach (Warning warning in warnings)

{

Logging.Log(string.Format("Report: {0} has warnings", warning.Message));

}

}

else

Logging.Log(string.Format("Report: {0} created successfully with no warnings", aReport.Name));

}

catch (SoapException e)

{

Logging.Log(e.Detail.InnerXml.ToString());

}

}

}


Report\Logger Class will be attached in the Source code...

I don't see any tough code to explain any developer familiar with C# will understand it very well, but if you have any question please feel free to ask me.


.. more.

Sunday, October 11, 2009

Table-Value and Temp Tables

Table-Value Parameters
Table-value parameters offer more flexibility and better performance than temporary tables to pass a list of parameters. Table-value parameters do not acquire locks for the initial population of data from a client and do not cause a statement to recompile.

Table-value parameters offer the following benefits:
·Provide a simple programming model.
·Enable inclusion of complex business logic in a single routine.
·Reduce round trips to the server.
·Include a table structure of different cardinality.
·Enable strongly typed and set-oriented queries.
·Enable the client to specify sort order and unique keys.

Table-value parameters have the following restrictions:
·Statistics are not maintained on columns of table-value parameters.
·Table-value parameters must be passed as input READONLY parameters to Transact-SQL routines.
·DML operations such as UPDATE, DELETE, or INSERT cannot be performed on a table-value parameter in the body of a routine.
·Table-value parameters cannot be used as the target of a SELECT INTO or INSERT EXEC statement. Table-value parameters can be in the FROM clause of SELECT INTO, or in the INSERT EXEC string or stored procedure.

Source: http://bit.ly/3rmMLK

.. more.