Best way to copy database: report

Showing posts with label report. Show all posts

Tuesday, March 27, 2012

Binding Datatable to Report

Hi,

I have created a datatable which is filled by the result of stored procedure. So I need to know like how to bind this datatable with the report using report viewer.

Apoorva

First you have to make sure you have a dataset. Also that the data set is connecting to this table.

If this is your first time creating a report I would suggest finding a walk through on a simple report creation

Here is a pretty good wak through

http://www.codeproject.com/dotnet/HowToReport.asp

good luck

Binding dataset to a crystal report

Hi all,

The code I used is as follows.

dim dbconnection
as new oledb.oledbconnection(....conn string...)

Dim SQL As String

SQL = "SELECT * FROM <TABLENAME> "

dbConnection.Open()
Dim objAdapter As New OleDb.OleDbDataAdapter(SQL, dbConnection)
Dim objDataSet As New DataSet
objAdapter.Fill(objDataSet)
Dim oReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim sRptPath As String = Server.MapPath("CRlMT.rpt")
oReport.Load(sRptPath)
oReport.SetDataSource(objDataSet)
CrystalReportViewer1.ReportSource = oReport

when i ran it I got the following error.

Logon failed. Details: ADO Error Code: 0x Source: Provider Description: Authentication failed. Native Error: Error in File <path>.rpt: Unable to connect: incorrect log on parameters.

what I am doing wrong here. Help is greatly appreciated.

note: asp.net web app.Make sure the file exists at the application path. Also make sure if you have permission to access the databasesql

Bind object to RDLC Report

Hi,all
I hava a question about Bind object to RDLC Report,the RDLC like
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DummyDataSource">
<ConnectionProperties>
<ConnectString />
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>47739726-cc8c-4719-b061-c392c2cceb68</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>WindowsApplication1_MedcialCase</DataSetName>
<Top>0.25cm</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ID">
<rd:DefaultName>ID</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Name">
<rd:DefaultName>Name</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PatientOfMedcialCase.Value.Name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Name_1">
<rd:DefaultName>Name_1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="PatientOfMedcialCase">
<rd:DefaultName>PatientOfMedcialCase</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PatientOfMedcialCase.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Patient Of Medcial Case</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>8cm</Width>
</TableColumn>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
</TableColumns>
<Height>1.90476cm</Height>
</Table>
</ReportItems>
<Height>5cm</Height>
</Body>
<rd:ReportID>a69b2d8e-258a-4212-8d4a-c67c96055732</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="WindowsApplication1_MedcialCase">
<rd:DataSetInfo>
<rd:DataSetName>WindowsApplication1</rd:DataSetName>
<rd:ObjectDataSourceType>WindowsApplication1.MedcialCase, WindowsApplication1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</rd:ObjectDataSourceType>
<rd:TableName>MedcialCase</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText />
<DataSourceName>DummyDataSource</DataSourceName>
</Query>
<Fields>
<Field Name="ID">
<rd:TypeName>System.Guid</rd:TypeName>
<DataField>ID</DataField>
</Field>
<Field Name="PatientOfMedcialCase">
<rd:TypeName>WindowsApplication1.Patient</rd:TypeName>
<DataField>PatientOfMedcialCase</DataField>
</Field>
<Field Name="Name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Name</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>24cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

the object I bind to the Report like

public class MedcialCase
{
private Guid iD;

public Guid ID
{
get { return iD; }
set { iD = value; }
}

private Patient patient;

public Patient PatientOfMedcialCase
{
get { return patient; }
set { patient = value; }
}

}

public class Patient
{
private string name;

public string Name
{
get { return name; }
set { name = value; }
}

}

and the code bind object to report like

MedcialCase medcialCase = new MedcialCase();
medcialCase.ID = Guid.NewGuid();
Patient patient = new Patient();
patient.Name = "Michael";
medcialCase.PatientOfMedcialCase = patient;
this.MedcialCaseBindingSource.DataSource = medcialCase;
this.reportViewer1.RefreshReport();

I choose the MedcialCase.ID and MedcialCase.PatientOfMedcialCase.Name to show, but when the report was run ,,just MedcialCase.ID has been shown,
the text of MedcialCase.PatientOfMedcialCase.Name is "=Fields!Name.Value"

I modify it to "=Fields!PatientOfMedcialCase.Value.Name.Value" and run it ,the "#Error" show in the text of Name

anyone can tell me why?and how to bind object like MedcialCase to the Report?

thanks!

This is snippet from

http://www.gotreportviewer.com/objectdatasources/index.html

Nested objects

Only the first level of public properties can be dragged and dropped to a report. If the type of a property is a custom class with its own properties then you can manually enter an expression to access the values of second level properties. For example if the Employee class has a HomeAddress property of type Address then you can enter the expression =Fields!HomeAddress.Value.Zip to access the value of Zip property.

If the value of a property is a collection then you can use a subreport to display the collection. See this example.

In your case, this translates to "=Fields!PatientOfMedcialCase.Value.Patient.Name"

Hope that helps,

Tudor Trufinescu

|||

hi,Tudor

Thanks for your advice,

when I try to do follow your advice,I Modify the class like:

public class Book
{
private string m_title;
private string m_publisher;
private float m_listPrice;

public Book(string title, string publisher, float listPrice)
{
m_title = title;
m_publisher = publisher;
m_listPrice = listPrice;
}

public string Title
{
get { return m_title; }
set { m_title = value; }
}

public string Publisher
{
get { return m_publisher; }
set { m_publisher = value; }
}

public float ListPrice
{
get { return m_listPrice; }
set { m_listPrice = value; }
}
}

class Author
{
private int m_id;
private string m_name;
private Book book;

public Author(int id)
{
m_id = id;
}

public Author(string name, int id, Book books)
{
m_name = name;
m_id = id;
book = books;
}

public int Id
{
get { return m_id; }
set { m_id = value; }
}

public string Name
{
get { return m_name; }
set { m_name = value; }
}

public Book Books
{
get { return this.book; }
set { this.book = value; }
}
}

and report text like "=Fields!Books.Value.ListPrice" as you say,

when the report run ,the text still display the "#error"

How shall I do?

|||

I have changed the object data source sample to illustrate the use of nested objects

See this post for more details.

http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx

Thanks,

Tudor

|||that's helpful,i have solved the problem,thanks!|||Just wondering why I get the following error when I use GenericsCollection:

"The Value expression for the textbox ‘AddressLine1’ refers to the field ‘Contacts’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. "

for example I have these attributes of a class Company:

public Address PrivateAddress
{
get { return _privateAddress; }
set
{
_privateAddress = value;
}
}

public GenericCollection<Address> Contacts
{
get { return _contacts; }
set
{
_contacts = value;
}
}

I have no problems using the PrivateAddress object, but the Contacts gives me the above error message.

Has anyone implemented Generics objects? Any help would be appreciated.
Thanks

Bind object to RDLC Report

the object I bind to the Report like

public class MedcialCase
{
private Guid iD;

public Guid ID
{
get { return iD; }
set { iD = value; }
}

private Patient patient;

public Patient PatientOfMedcialCase
{
get { return patient; }
set { patient = value; }
}

}

public class Patient
{
private string name;

public string Name
{
get { return name; }
set { name = value; }
}

}

and the code bind object to report like

I modify it to "=Fields!PatientOfMedcialCase.Value.Name.Value" and run it ,the "#Error" show in the text of Name

anyone can tell me why?and how to bind object like MedcialCase to the Report?

thanks!

This is snippet from

http://www.gotreportviewer.com/objectdatasources/index.html

Nested objects

If the value of a property is a collection then you can use a subreport to display the collection. See this example.

In your case, this translates to "=Fields!PatientOfMedcialCase.Value.Patient.Name"

Hope that helps,

Tudor Trufinescu

|||

hi,Tudor

Thanks for your advice,

when I try to do follow your advice,I Modify the class like:

public class Book
{
private string m_title;
private string m_publisher;
private float m_listPrice;

public Book(string title, string publisher, float listPrice)
{
m_title = title;
m_publisher = publisher;
m_listPrice = listPrice;
}

public string Title
{
get { return m_title; }
set { m_title = value; }
}

public string Publisher
{
get { return m_publisher; }
set { m_publisher = value; }
}

public float ListPrice
{
get { return m_listPrice; }
set { m_listPrice = value; }
}
}

class Author
{
private int m_id;
private string m_name;
private Book book;

public Author(int id)
{
m_id = id;
}

public Author(string name, int id, Book books)
{
m_name = name;
m_id = id;
book = books;
}

public int Id
{
get { return m_id; }
set { m_id = value; }
}

public string Name
{
get { return m_name; }
set { m_name = value; }
}

public Book Books
{
get { return this.book; }
set { this.book = value; }
}
}

and report text like "=Fields!Books.Value.ListPrice" as you say,

when the report run ,the text still display the "#error"

How shall I do?

|||

I have changed the object data source sample to illustrate the use of nested objects

See this post for more details.

http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx

Thanks,

Tudor

|||that's helpful,i have solved the problem,thanks!|||Just wondering why I get the following error when I use GenericsCollection:

for example I have these attributes of a class Company:

public Address PrivateAddress
{
get { return _privateAddress; }
set
{
_privateAddress = value;
}
}

public GenericCollection<Address> Contacts
{
get { return _contacts; }
set
{
_contacts = value;
}
}

I have no problems using the PrivateAddress object, but the Contacts gives me the above error message.

Has anyone implemented Generics objects? Any help would be appreciated.
Thanks
sql

Sunday, March 25, 2012

bind matrix or table to image or map

Hi
I want to create a report where data is fixed to certain areas on a map (eg
I have an image of Switzerland and regional data. The data should be
displayed ontop of certain regions of the map). When I deploy the report and
open it in Preview or with a browser, everything is moved: eg, if I scroll up
or down, the data moves but the map stays fix.
Is there a way to tie the dataobjects to the image so the data will always
stay ontop of the same point?
Thanks for any help,
SalvatoreYou can approximate this by placing the data regions in rectangles and
placing rectangles in specific areas you'd like.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Salvatore" <Salvatore@.discussions.microsoft.com> wrote in message
news:B2DE92AB-4D79-4E6F-AB4F-A53FEA25CC9B@.microsoft.com...
> Hi
> I want to create a report where data is fixed to certain areas on a map
(eg
> I have an image of Switzerland and regional data. The data should be
> displayed ontop of certain regions of the map). When I deploy the report
and
> open it in Preview or with a browser, everything is moved: eg, if I scroll
up
> or down, the data moves but the map stays fix.
> Is there a way to tie the dataobjects to the image so the data will always
> stay ontop of the same point?
> Thanks for any help,
> Salvatore|||Hi Ravi
I have try by placing the data regions in rectangles or placing Image in
rectangles but without success
Thanks for any help,
Salvatore
"Ravi Mumulla (Microsoft)" wrote:
> You can approximate this by placing the data regions in rectangles and
> placing rectangles in specific areas you'd like.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Salvatore" <Salvatore@.discussions.microsoft.com> wrote in message
> news:B2DE92AB-4D79-4E6F-AB4F-A53FEA25CC9B@.microsoft.com...
> > Hi
> > I want to create a report where data is fixed to certain areas on a map
> (eg
> > I have an image of Switzerland and regional data. The data should be
> > displayed ontop of certain regions of the map). When I deploy the report
> and
> > open it in Preview or with a browser, everything is moved: eg, if I scroll
> up
> > or down, the data moves but the map stays fix.
> >
> > Is there a way to tie the dataobjects to the image so the data will always
> > stay ontop of the same point?
> >
> > Thanks for any help,
> > Salvatore
>
>

Bind many tables from a single sp on many tables on a single repor

Hi,
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?

Bind a Table to a StoredProcedure

Hi All
I am an RS Newbie - Please be gentle :-))
I have a table on an RS report
When the table is bound to a simple Select statement EG select * from
Customers
I can then further bind each control on the report to a field in the
recordset.
EG I select the relevant text box - then from the property sheet I select
from the list of available fields
in the 'Value' combo box on the property sheet
EG txtCustomer value in the property sheet = Fields!CustomerName.Value
Cool
But when I change the recordset for the table to a Stored Proc
I see no available fields when I select a Text box and try to bind it to a
value in the propertyy sheet
Infact rather than a list of available fields, all I can see is <Expression>
Don't know where to go from here - any suggestions appreciated
Many thanks
DenzilSometimes when you switch to a stored procedure it does not detect the field
list. Try the following. Hopefully one of these two will do the trick.
1. in the dataset view click on the refresh fields button (it is to the
right of the ... and looks like the refresh button from IE
2. Make sure the command type is stored procedure and just put in the name
of the stored procedure like this: MyStoredProcName
Not like this: Exec MyStoredProcName
After doing this then try #1 again.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c3160a2829f0@.uwe...
> Hi All
> I am an RS Newbie - Please be gentle :-))
> I have a table on an RS report
> When the table is bound to a simple Select statement EG select * from
> Customers
> I can then further bind each control on the report to a field in the
> recordset.
> EG I select the relevant text box - then from the property sheet I select
> from the list of available fields
> in the 'Value' combo box on the property sheet
> EG txtCustomer value in the property sheet = Fields!CustomerName.Value
> Cool
> But when I change the recordset for the table to a Stored Proc
> I see no available fields when I select a Text box and try to bind it to a
> value in the propertyy sheet
> Infact rather than a list of available fields, all I can see is
> <Expression>
> Don't know where to go from here - any suggestions appreciated
> Many thanks
> Denzil|||Hi Bruce
Many thanks for the swift reply
When I read the bit about changing the command type I thought that would be
it
But...sorry none of this worked <sigh>
When I change the Command Type to StoredProc and remove the 'exec' at the
front of the
query string then try and run it - it errors out and does not give me the
expected popup box where I would manually enter any parameters
The error is
"An error occurred whilst trying to retrieve the parameters in the query
rsGetAccountList @.ActiveStatus does not exist"
before, I used to have "exec rsGetAccountList @.ActiveStatus" in the dataset
view and had the command type as Text. With that setup I could run the query
in DataSet view and get my parameter popup box
Any more suggestions? :-))
Many thanks
Denzil
Bruce L-C [MVP] wrote:
>Sometimes when you switch to a stored procedure it does not detect the field
>list. Try the following. Hopefully one of these two will do the trick.
>1. in the dataset view click on the refresh fields button (it is to the
>right of the ... and looks like the refresh button from IE
>2. Make sure the command type is stored procedure and just put in the name
>of the stored procedure like this: MyStoredProcName
>Not like this: Exec MyStoredProcName
>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>[quoted text clipped - 23 lines]
>> Denzil|||Did you put this? rsGetAccountList @.ActiveStatus
If so, remove th @.ActiveStatus. Just put the name of the stored procedure.
RS automatically retrieves the parameter list from the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c319c74a8f20@.uwe...
> Hi Bruce
> Many thanks for the swift reply
> When I read the bit about changing the command type I thought that would
> be
> it
> But...sorry none of this worked <sigh>
> When I change the Command Type to StoredProc and remove the 'exec' at the
> front of the
> query string then try and run it - it errors out and does not give me the
> expected popup box where I would manually enter any parameters
> The error is
> "An error occurred whilst trying to retrieve the parameters in the query
> rsGetAccountList @.ActiveStatus does not exist"
> before, I used to have "exec rsGetAccountList @.ActiveStatus" in the
> dataset
> view and had the command type as Text. With that setup I could run the
> query
> in DataSet view and get my parameter popup box
> Any more suggestions? :-))
> Many thanks
> Denzil
>
> Bruce L-C [MVP] wrote:
>>Sometimes when you switch to a stored procedure it does not detect the
>>field
>>list. Try the following. Hopefully one of these two will do the trick.
>>1. in the dataset view click on the refresh fields button (it is to the
>>right of the ... and looks like the refresh button from IE
>>2. Make sure the command type is stored procedure and just put in the name
>>of the stored procedure like this: MyStoredProcName
>>Not like this: Exec MyStoredProcName
>>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>>[quoted text clipped - 23 lines]
>> Denzil|||Thanks Bruce
yes that was the answer
Many many thanks for your assistance
I really appreciate it
have agreat day
Darren
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1|||No problem. Support is strong for stored procedures but some of what you
need to do is not intuitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil via SQLMonster.com" <u18940@.uwe> wrote in message
news:5c4128a5414d2@.uwe...
> Thanks Bruce
> yes that was the answer
> Many many thanks for your assistance
> I really appreciate it
> have agreat day
> Darren
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1sql

Bind a stored procedure within dataview

As I would like to execute a stored procedure within the Report Creation
Wizard. But only SQL String are allowed.
How can I "put" my stored procedure in this data view ? (I try EXEC
my_stored_proc in the SQL String area but not working...)
I would like to do it because I have almost 60 fields to add one by one into
a table manualy
If one of you have tips or can help me, I will appreciate :)
Have a good day !Are you getting any particular Errors? I've found that some sproc can be
called using exec sprocname @.Param1,@.Parm2 etc., while some do not. I've
resorted to the follwowing:
Putting a select * from table1
Once you get to the data tab, click on the ... and change the Command Type
to stored procedure.
You can then enter you sproc name in the query string box (don't enter the
exec).
If the sproc has parameters you'l need to set those up as well, to feed the
sproc.
"JahPil" wrote:
> As I would like to execute a stored procedure within the Report Creation
> Wizard. But only SQL String are allowed.
> How can I "put" my stored procedure in this data view ? (I try EXEC
> my_stored_proc in the SQL String area but not working...)
> I would like to do it because I have almost 60 fields to add one by one into
> a table manualy
> If one of you have tips or can help me, I will appreciate :)
> Have a good day !

BINARY_INTEGER in CRXI

Hi...
could anyone clarify my doubts...

I am using BINARY_FLOAT columns in my oracle tables.
In crystal report XI, when you connect to oracle and see the list of tables and columns...the table does not show the columns which are of BINARY_FLOAT type.

What could be the reason??
CRXI doesnt support this???

Help me pls

rgds
salaiSee if you find answer here
http://support.businessobjects.com/

Tuesday, March 20, 2012

Big picture question about Reporting Services

I am an beginner who has completed one report under RS. I'm wondering if my
memory is playing tricks on me. This report has been rather easy to do.
I've hardly had to look at the documentation. (I should point out that I'm
temporarily using the SQL Express version because Someone misplaced the disks
for the full version.)
My question is this: I was under the impression that Reporting Services
involved actual Dot Net programming. At least that was the impression I got
from the early demos I saw, but it has been a while. Is there more to RS
than just dragging fields on a designer?On May 15, 8:51 am, B. Chernick <BChern...@.discussions.microsoft.com>
wrote:
> I am an beginner who has completed one report under RS. I'm wondering if my
> memory is playing tricks on me. This report has been rather easy to do.
> I've hardly had to look at the documentation. (I should point out that I'm
> temporarily using the SQL Express version because Someone misplaced the disks
> for the full version.)
> My question is this: I was under the impression that Reporting Services
> involved actual Dot Net programming. At least that was the impression I got
> from the early demos I saw, but it has been a while. Is there more to RS
> than just dragging fields on a designer?
There does not have to be any actual programming, even though you are
in Visual Studio. Demos or classes may have examples where you could
code an assembly and then call it from the report.|||You can write expressions in reporting services using visual basic.
No .net programming is required to create reports.
However, because the report definiton files (RDL) are basically xml files
then the system is wide open for customisation.
For example, I have a SQL stored procedure that generates hundreds of
reports automatically for me.
Now that is real power.sql

Monday, March 19, 2012

BIDS bug with SSIS Annotations

I would like to report what appears to me to be a bug. I found it while researching an answer for another thread. http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=554926&SiteID=17

Since that thread has not received any further replies I thought I would start a new one to see what answer we can get from MS as well as if anyone else would care to reproduce.

The bug is not a major one and the work around is easy, but it can be annoying.

The bug is this: if I open an SSIS package in BIDS (RTM or SP1) and the only change I make is adding or modifying an annotation and then click on Save and then close BIDS does not save the new annotation or changes made to existing annotations. If I change something other than an annotation then BIDS does save the annotations with the package.

A careful observer will notice that when opening a package and then modifying or adding an annotation that BIDS does not even register that the package has been changed. This is evident by the lack of the trailing asterisk in the title bar after the package name.

In summary if I go into a package for the sole purpose of adding and/or modifying annotations they will not get saved. Workaround: modify something else in the package and then save.

The best thing to do with bugs - report them at betaplace. Forum is good for discussing things, but for reporting bugs the betaplace is better, and the customer feedback it is major stimul for bug to get attention and considered for fixing in SP.

BIDS - How to turn-off auto naming of fields

Hi:

When I create a report, the wizard auto-renames fields.

E.g. if Table_COLUMN_Name=CustomerName, the list column header in the report will change to Customer Name. The wizard figures out Friendly names based on capitalization, and underscores and so on.

Similar behavior occurs even in SSAS projects Dimensions Wizard.

Is there a way to turn of this extra cuteness?

TIA

Kar

Hi:

Request help once again on this.

TIA

Kar

Sunday, March 11, 2012

BIDS - How to turn-off auto naming of fields

Hi:

When I create a report, the wizard auto-renames fields.

E.g. if Table_COLUMN_Name=CustomerName, the list column header in the report will change to Customer Name. The wizard figures out Friendly names based on capitalization, and underscores and so on.

Similar behavior occurs even in SSAS projects Dimensions Wizard.

Is there a way to turn of this extra cuteness?

TIA

Kar

Hi:

Request help once again on this.

TIA

Kar

Thursday, March 8, 2012

BI on Windows XP

I want to create report through Microsoft Visual Studio 2005 (Business
Intelligence Project (Report Server Project) in Windows XP.
Which SQL Edition I can install in windows XP that gives me facility to
create report via Business Intelligence.
ThanksFirst, keep in mind that you are installing the BI Tools, you are not
installing SQL Server, you are not installing Reporting Services. Those are
server based and while they can be installed on XP (I know developers
edition, not sure what other editions) they are not needed for development.
The BI Tools are the same as far as I know for all editions. You can preview
the report, design the report. It is not until you are ready to deploy the
report to a server that RS even has to be installed somewhere.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23M9tGuWFIHA.3360@.TK2MSFTNGP04.phx.gbl...
>I want to create report through Microsoft Visual Studio 2005 (Business
>Intelligence Project (Report Server Project) in Windows XP.
> Which SQL Edition I can install in windows XP that gives me facility to
> create report via Business Intelligence.
> Thanks
>|||Alright, that means when I installed SQL Server 2005 Developer Edition on
XP, the BI will also be installed?
Thanks
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u4wyxqXFIHA.3980@.TK2MSFTNGP03.phx.gbl...
> First, keep in mind that you are installing the BI Tools, you are not
> installing SQL Server, you are not installing Reporting Services. Those
> are server based and while they can be installed on XP (I know developers
> edition, not sure what other editions) they are not needed for
> development. The BI Tools are the same as far as I know for all editions.
> You can preview the report, design the report. It is not until you are
> ready to deploy the report to a server that RS even has to be installed
> somewhere.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:%23M9tGuWFIHA.3360@.TK2MSFTNGP04.phx.gbl...
>>I want to create report through Microsoft Visual Studio 2005 (Business
>>Intelligence Project (Report Server Project) in Windows XP.
>> Which SQL Edition I can install in windows XP that gives me facility to
>> create report via Business Intelligence.
>> Thanks
>>
>|||No, the install is a two step process. There is server side install which
includes reporting services (if you selected to install it) and then there
is installing client side tools. You have to do separate installs. Normally
the BI tools would not be on the server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rogers" <naissani@.hotmail.com> wrote in message
news:ugJQ2LKHIHA.6068@.TK2MSFTNGP05.phx.gbl...
> Alright, that means when I installed SQL Server 2005 Developer Edition on
> XP, the BI will also be installed?
> Thanks
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u4wyxqXFIHA.3980@.TK2MSFTNGP03.phx.gbl...
>> First, keep in mind that you are installing the BI Tools, you are not
>> installing SQL Server, you are not installing Reporting Services. Those
>> are server based and while they can be installed on XP (I know developers
>> edition, not sure what other editions) they are not needed for
>> development. The BI Tools are the same as far as I know for all editions.
>> You can preview the report, design the report. It is not until you are
>> ready to deploy the report to a server that RS even has to be installed
>> somewhere.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Rogers" <naissani@.hotmail.com> wrote in message
>> news:%23M9tGuWFIHA.3360@.TK2MSFTNGP04.phx.gbl...
>>I want to create report through Microsoft Visual Studio 2005 (Business
>>Intelligence Project (Report Server Project) in Windows XP.
>> Which SQL Edition I can install in windows XP that gives me facility to
>> create report via Business Intelligence.
>> Thanks
>>
>>
>

BI Development Studio Licensing and VS Express Edition

If we have a fully functional and Licensed Report Server 2005, can we allow
people using Visual Studio Express to develop reports?
I our shop we have two types of report developers, The first type is the
full blast developer that has the full Visual Studio product. The second
type does scripting and some light programming using the express products.
ThanksRS 2000 and RS 2005 are totally different in this respect. RS 2000 required
VS to install the designer into. RS 2005 will use VS if it is there but it
is not required. It will install its own copy if there is no VS. What this
means is that there is no dependency from either a physical or a licensing
perspective. VS is not needed for RS 2005 designer.
My guess is if VS Express is there it will install with it but that is just
a guess. As far as allowing, from a licensing perspecitve there is no
problem. Will it work, my guess is yes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hpux9@.nospam.nospam> wrote in message
news:26671BEE-78BA-4540-9B52-661A86BC65A6@.microsoft.com...
> If we have a fully functional and Licensed Report Server 2005, can we
> allow
> people using Visual Studio Express to develop reports?
> I our shop we have two types of report developers, The first type is the
> full blast developer that has the full Visual Studio product. The second
> type does scripting and some light programming using the express products.
> Thanks|||Bruce,
Thanks for the reply. I have installed RS 2005 designer on 2 workstations.
Workstation 1 has a licensed copy of VS2005, the install integrated with VS
2005 as expected.
The second PC had a copy of C# Express. On this box the install dropped
down a copy of VS that only had the BI project types (which is good!).
The only issue I see is that I had to enter the SQL 2005 license key (in
both cases) to get to the screen where I selected RS 2005 designer. If the
designer is trully "free" how am I going to get the software to the
developers?
Thanks!
"Bruce L-C [MVP]" wrote:
> RS 2000 and RS 2005 are totally different in this respect. RS 2000 required
> VS to install the designer into. RS 2005 will use VS if it is there but it
> is not required. It will install its own copy if there is no VS. What this
> means is that there is no dependency from either a physical or a licensing
> perspective. VS is not needed for RS 2005 designer.
> My guess is if VS Express is there it will install with it but that is just
> a guess. As far as allowing, from a licensing perspecitve there is no
> problem. Will it work, my guess is yes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <hpux9@.nospam.nospam> wrote in message
> news:26671BEE-78BA-4540-9B52-661A86BC65A6@.microsoft.com...
> > If we have a fully functional and Licensed Report Server 2005, can we
> > allow
> > people using Visual Studio Express to develop reports?
> >
> > I our shop we have two types of report developers, The first type is the
> > full blast developer that has the full Visual Studio product. The second
> > type does scripting and some light programming using the express products.
> >
> > Thanks
>
>|||The install comes via the SQL Server 2005 CD, which requires a license key.
I don't see any way around this. You don't need additional server licenses
to install the design tool but you do need to be able to run the setup from
the CD.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hpux9@.nospam.nospam> wrote in message
news:537D168E-4133-4D41-873A-204E89902F83@.microsoft.com...
> Bruce,
> Thanks for the reply. I have installed RS 2005 designer on 2
> workstations.
> Workstation 1 has a licensed copy of VS2005, the install integrated with
> VS
> 2005 as expected.
> The second PC had a copy of C# Express. On this box the install dropped
> down a copy of VS that only had the BI project types (which is good!).
> The only issue I see is that I had to enter the SQL 2005 license key (in
> both cases) to get to the screen where I selected RS 2005 designer. If
> the
> designer is trully "free" how am I going to get the software to the
> developers?
> Thanks!
> "Bruce L-C [MVP]" wrote:
>> RS 2000 and RS 2005 are totally different in this respect. RS 2000
>> required
>> VS to install the designer into. RS 2005 will use VS if it is there but
>> it
>> is not required. It will install its own copy if there is no VS. What
>> this
>> means is that there is no dependency from either a physical or a
>> licensing
>> perspective. VS is not needed for RS 2005 designer.
>> My guess is if VS Express is there it will install with it but that is
>> just
>> a guess. As far as allowing, from a licensing perspecitve there is no
>> problem. Will it work, my guess is yes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <hpux9@.nospam.nospam> wrote in message
>> news:26671BEE-78BA-4540-9B52-661A86BC65A6@.microsoft.com...
>> > If we have a fully functional and Licensed Report Server 2005, can we
>> > allow
>> > people using Visual Studio Express to develop reports?
>> >
>> > I our shop we have two types of report developers, The first type is
>> > the
>> > full blast developer that has the full Visual Studio product. The
>> > second
>> > type does scripting and some light programming using the express
>> > products.
>> >
>> > Thanks
>>|||Bruce,
There are some features that you will not be able to use if you only
develop using BIDS though from what I have heard. What about custom code?
Custom assemblies? Is BIDS a fully functional replacement for developing
robust reports that need major customization (functions, code, assemblies)?
I need to know this. As far as I can see ... it will only install a BI
project type so where do you test custom code for example? I cant create a
custom assembly that is shared across all reports using BIDS alone. If I am
misunderstanding ... great. If I am not ... then I think it is important to
make sure people know that they will be losing function if they just go with
BIDS.
"Bruce L-C [MVP]" wrote:
> RS 2000 and RS 2005 are totally different in this respect. RS 2000 required
> VS to install the designer into. RS 2005 will use VS if it is there but it
> is not required. It will install its own copy if there is no VS. What this
> means is that there is no dependency from either a physical or a licensing
> perspective. VS is not needed for RS 2005 designer.
> My guess is if VS Express is there it will install with it but that is just
> a guess. As far as allowing, from a licensing perspecitve there is no
> problem. Will it work, my guess is yes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <hpux9@.nospam.nospam> wrote in message
> news:26671BEE-78BA-4540-9B52-661A86BC65A6@.microsoft.com...
> > If we have a fully functional and Licensed Report Server 2005, can we
> > allow
> > people using Visual Studio Express to develop reports?
> >
> > I our shop we have two types of report developers, The first type is the
> > full blast developer that has the full Visual Studio product. The second
> > type does scripting and some light programming using the express products.
> >
> > Thanks
>
>|||BIDS is for designing reports. You can do code behind reports but you cannot
do custom assemblies. If you do that you need VB.Net (or C# etc). A whole
lot of people are desinging reports only. Previously it was a big hassle,
they would be installing VS just for the report designer. If they are doing
that then they would not be doing anything you are talking about.
I disagree they are losing functionality. RS 2000 had to have some product
installed that had VS to get the designer installed. The designer previously
did not give you the tools to do anything that you mentioned (create custom
assemblies for example). Obviously if you are creating your own web pages
then you need a designer to do so. If you are creating your own custom
assemblies ditto. However, most people are not doing that (creating custom
assemblies).
There are two markets. People who are tightly integrating into their own app
or are integrating in their own data extensions or authentication. I'd say
the vast majority are not doing that. The others are using Report Manager
portal as is.
Obviously you are not doing that. Your type of development is why VS 2005
has the new controls. Note these controls do not come with RS 2005, you have
to purchase VS 2005 to get these (or VB.Net 2005 etc). Just for the controls
anyone integrating with their own app should get VS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:94B2AA1F-AC25-4B6C-A29C-FD268445CA99@.microsoft.com...
> Bruce,
> There are some features that you will not be able to use if you only
> develop using BIDS though from what I have heard. What about custom code?
> Custom assemblies? Is BIDS a fully functional replacement for developing
> robust reports that need major customization (functions, code,
> assemblies)?
> I need to know this. As far as I can see ... it will only install a BI
> project type so where do you test custom code for example? I cant create
> a
> custom assembly that is shared across all reports using BIDS alone. If I
> am
> misunderstanding ... great. If I am not ... then I think it is important
> to
> make sure people know that they will be losing function if they just go
> with
> BIDS.
> "Bruce L-C [MVP]" wrote:
>> RS 2000 and RS 2005 are totally different in this respect. RS 2000
>> required
>> VS to install the designer into. RS 2005 will use VS if it is there but
>> it
>> is not required. It will install its own copy if there is no VS. What
>> this
>> means is that there is no dependency from either a physical or a
>> licensing
>> perspective. VS is not needed for RS 2005 designer.
>> My guess is if VS Express is there it will install with it but that is
>> just
>> a guess. As far as allowing, from a licensing perspecitve there is no
>> problem. Will it work, my guess is yes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <hpux9@.nospam.nospam> wrote in message
>> news:26671BEE-78BA-4540-9B52-661A86BC65A6@.microsoft.com...
>> > If we have a fully functional and Licensed Report Server 2005, can we
>> > allow
>> > people using Visual Studio Express to develop reports?
>> >
>> > I our shop we have two types of report developers, The first type is
>> > the
>> > full blast developer that has the full Visual Studio product. The
>> > second
>> > type does scripting and some light programming using the express
>> > products.
>> >
>> > Thanks
>>

BI- Collaboration and reports

I would like to get ideas on how how to enable report users not only
just view reports but also take action after viewing a report by
collaborating with collegues. Here's a use case-
- A project mangager (PM) logs into a portal and view a projects
status report listing current status of all projects managed by him.
- He finds that one of the projects is falling behind due to a pending
task that Peter is working on.
******Collaboration piece******
- In the portal itself the PM enter in a comment for Peter to clarify
the task's status.
- Peter logs into the portal and sees the PMs' comments. In response
he adds a comment that his task completion is dependent on the the
CEO approving a pending PO.
- PM logs into the portal opens the same report and sees Peters
comments. He updates his weekly project issues report to highlight
this dependency.
I have seem Microsoft Performance Point demos that exibit similar
collaboration features. I am not sure if the collaboration piece
requires Performance Point or is it a Sharepoint feature. Any ideas on
how to implement, what technologies to use would be appreciated.
Regards,
KenIt is as you say a feature of PerformancePoint Server.
You could also check out Analyzer by StrategyCompanion.
"Ken" <raidken@.yahoo.com> wrote in message
news:3dfe603c-e258-40c4-939a-8405951dbcd7@.p73g2000hsd.googlegroups.com...
>I would like to get ideas on how how to enable report users not only
> just view reports but also take action after viewing a report by
> collaborating with collegues. Here's a use case-
> - A project mangager (PM) logs into a portal and view a projects
> status report listing current status of all projects managed by him.
> - He finds that one of the projects is falling behind due to a pending
> task that Peter is working on.
> ******Collaboration piece******
> - In the portal itself the PM enter in a comment for Peter to clarify
> the task's status.
> - Peter logs into the portal and sees the PMs' comments. In response
> he adds a comment that his task completion is dependent on the the
> CEO approving a pending PO.
> - PM logs into the portal opens the same report and sees Peters
> comments. He updates his weekly project issues report to highlight
> this dependency.
> I have seem Microsoft Performance Point demos that exibit similar
> collaboration features. I am not sure if the collaboration piece
> requires Performance Point or is it a Sharepoint feature. Any ideas on
> how to implement, what technologies to use would be appreciated.
> Regards,
> Ken|||Ken,
If this already exists in your portal you can add hyper links to you report.
AK Code Rat.
"Ken" wrote:
> I would like to get ideas on how how to enable report users not only
> just view reports but also take action after viewing a report by
> collaborating with collegues. Here's a use case-
> - A project mangager (PM) logs into a portal and view a projects
> status report listing current status of all projects managed by him.
> - He finds that one of the projects is falling behind due to a pending
> task that Peter is working on.
> ******Collaboration piece******
> - In the portal itself the PM enter in a comment for Peter to clarify
> the task's status.
> - Peter logs into the portal and sees the PMs' comments. In response
> he adds a comment that his task completion is dependent on the the
> CEO approving a pending PO.
> - PM logs into the portal opens the same report and sees Peters
> comments. He updates his weekly project issues report to highlight
> this dependency.
> I have seem Microsoft Performance Point demos that exibit similar
> collaboration features. I am not sure if the collaboration piece
> requires Performance Point or is it a Sharepoint feature. Any ideas on
> how to implement, what technologies to use would be appreciated.
> Regards,
> Ken
>

Wednesday, March 7, 2012

BI Accelerator + Applications + Report Services

Hi All,
this seems like the best place for this question.
I've had cause to review the MSFT position/tools in the BI area. I'm
surprised!!! I feel like MSFT have made MUCH more progress than generally
talked about in newsgroups and customers I work with. All I ever hear from
MSFT with respect to DW/BI is 'the next release is going to be great.' Not,
'Hey, take a look at BIA/Reporting Services'...?
Q1. In BI accelerator I installed and tested out the shopfloor application
(manufacturing) and I generated the shopfloor database from the XL Sheet.
But I can't see where it got the table definitions for the staging table and
the underlying dimensional database from. For example, where are the
table/column/datatype definitions of the staging area and the dimensional
database in the XL sheets? I'm sure I looked at all the sheets in the
spreadsheet AnalyticsBuilderWB_ShopFloorPerformance.v.0.2.xls. (But maybe
I'm going crazy and didn't). To me it only seems like the analytical model
is in the spreadsheet.
Q2. I see it generates a ton of .dts files. And when I open them up they
seem to me (a zero level skilled DTS person) to be very complex. The manual
says 'don't change the packages' which I gather means don't look, don't
worry, just run it and it will all be ok....again, I gather that the DTS
packages are somehow generated by BIA into their binary format and I believe
the source to target mappings are defined by the 'mappings' spreadsheet. But
in this spreadsheet I don't seem to find enough columns for all the columns
in the dimensional model. For example I can't find mappings for dim_emp_std
in the spreadsheet but I would have thought it should be there. (Or am I
missing something)?
Q3. In BIA I see lots about sharepoint portal and office objects inside web
pages and all that as a presentation layer. But I just took a look at
Reporting Services. Reporting Services looks absolutely fantastic for what
it is trying to do and it as very obvious how easy it will be to extend
reporting services to do MUCH, MUCH more very, very easily. RDL (Report
Definition Language) is an idea long overdue and I do believe MSFT are first
with that one. I have not heard any other vendor talk about an RDL yet. I
was amazed that a report can just be exposed as a web service and you can
call it from anywhere with anything. Now THAT is a useful thing to have.
So the question is, how come BIA seems to completely ignore Reporting
Services? I would have thought they would be very closely 'joined'. For
example, why not do the front ends for BIA apps in reporting services? I am
assuming this is possible. I'm assuming reporting services can get data out
of Analysis Server because it can get data out of any ado.net server.
(I must say I thought MSF had hidden how good Report Services is very
well...unless I'm greatly mistaken, I've only had a few hours to look at it,
it looks like a really, really useful product!!!)
Anyway, thanks in advance if anyone can let me know what I am missing in
BIA.
Best Regards
Peter Nolan
www.peternolan.com
Wow. There is a lot of info here. A couple of comments:
Q1) datatypes using in BIA -- well, we make several guesses. First we know
that all measures are numeric (that is a requirement of Analysis Services).
Second we know that fields that we generate have specific uses -- and from
that we know their datatypes. For example, the surrogate keys are integers
(as you would expect because they are identity). For those fields which are
user supplies, e.g. member names -- we know just treat them as "names", i.e.
varchars. If you have something different, e.g. names which are really
integers, they will have to re-do things by-hand. For those things which
could be any datatype, e.g. member properties, you can choice the
appropriate datatypes.
If there are specific tables and columns you are wondering about just tell
me and I'd be glad to explain why we did something one way or the other.
What you are seeing the whole point of the BI Accelerator. You layout the
logical multidimensional design and we auto-generate a relational staging
area; final data mart, OLAP structures, and DTS packages to move the data
through it. Logical data model to final app in one click :-)
And that final app should have all of the tips and tricks that you would see
in a production system; not just a rough proof-of-concept system.
Q2) DTS packages in BIA -- thank you. I wrote the generator code and I am
quite proud of them.
It generates a ton of packages because of the type of schema we choose to
implement. Since we generate a snowflake schema, there are tables for every
dimension and level in the dimensional structure.
We had several goals with the DTS packages. First, we wanted them to be
data-driven because we don't expect everyone to be an expert on DTS. Thus
rather than having to make changes to them, most of them have variables you
can change value and have the package do something different. All of this is
documented in the PAG (online doc set). One of the challenges of this was
that DTS with SQL Server 2000 doesn't have the control flow tasks needed to
make this declaritive -- with 2005, we put that all in natively and similar
packages with 2005 would be quite a bit less complex.
Second, we wanted the packages to be visible and extensiveable by
knowledgeable users. Nothing is hidden -- it is all up front and in your
face. Yes, a novice will look at them and dispare -- but don't give up!
There is documentation in the PAG provided for all of them! Lastly you might
be interested in this white paper which talks about the DTS packages and
provides various tips and tricks beyond what the PAG has in it.
http://msdn.microsoft.com/library/de..._dts_ssabi.asp
If there is a specific step you have questions about, I'd be glad to help
also.
Q3) I am glad you like Reporting Services. I agree it is a fantastic tool.
However, it is a totally different product. BI Accelerator is a BI
application generator; not a report generator. It has a client component
only for customization. The idea was that suppose you had a report called a
"template" which was exactly the same from client to client, but one
customer called products "items" and another called products "books".
Wouldn't it be nice to ship a template along with the multidimensional
design and have the system automatically rename products to items or
products to books. That is what the client generator does with the BI
Accelerator. It ships with a Proclarity component which knows how to go
inside a Proclarity briefing book and replace one tag for another tag. A
similar facility is available from Panorama and there is an API available to
other vendors if they would like to plug into the BI Accelerator client
generator.
Hope this helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:uCEWHDlBFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> this seems like the best place for this question.
> I've had cause to review the MSFT position/tools in the BI area. I'm
> surprised!!! I feel like MSFT have made MUCH more progress than generally
> talked about in newsgroups and customers I work with. All I ever hear
from
> MSFT with respect to DW/BI is 'the next release is going to be great.'
Not,
> 'Hey, take a look at BIA/Reporting Services'...?
> Q1. In BI accelerator I installed and tested out the shopfloor application
> (manufacturing) and I generated the shopfloor database from the XL Sheet.
> But I can't see where it got the table definitions for the staging table
and
> the underlying dimensional database from. For example, where are the
> table/column/datatype definitions of the staging area and the dimensional
> database in the XL sheets? I'm sure I looked at all the sheets in the
> spreadsheet AnalyticsBuilderWB_ShopFloorPerformance.v.0.2.xls. (But maybe
> I'm going crazy and didn't). To me it only seems like the analytical
model
> is in the spreadsheet.
>
> Q2. I see it generates a ton of .dts files. And when I open them up they
> seem to me (a zero level skilled DTS person) to be very complex. The
manual
> says 'don't change the packages' which I gather means don't look, don't
> worry, just run it and it will all be ok....again, I gather that the DTS
> packages are somehow generated by BIA into their binary format and I
believe
> the source to target mappings are defined by the 'mappings' spreadsheet.
But
> in this spreadsheet I don't seem to find enough columns for all the
columns
> in the dimensional model. For example I can't find mappings for
dim_emp_std
> in the spreadsheet but I would have thought it should be there. (Or am I
> missing something)?
>
> Q3. In BIA I see lots about sharepoint portal and office objects inside
web
> pages and all that as a presentation layer. But I just took a look at
> Reporting Services. Reporting Services looks absolutely fantastic for
what
> it is trying to do and it as very obvious how easy it will be to extend
> reporting services to do MUCH, MUCH more very, very easily. RDL (Report
> Definition Language) is an idea long overdue and I do believe MSFT are
first
> with that one. I have not heard any other vendor talk about an RDL yet.
I
> was amazed that a report can just be exposed as a web service and you can
> call it from anywhere with anything. Now THAT is a useful thing to have.
> So the question is, how come BIA seems to completely ignore Reporting
> Services? I would have thought they would be very closely 'joined'. For
> example, why not do the front ends for BIA apps in reporting services? I
am
> assuming this is possible. I'm assuming reporting services can get data
out
> of Analysis Server because it can get data out of any ado.net server.
> (I must say I thought MSF had hidden how good Report Services is very
> well...unless I'm greatly mistaken, I've only had a few hours to look at
it,
> it looks like a really, really useful product!!!)
> Anyway, thanks in advance if anyone can let me know what I am missing in
> BIA.
> Best Regards
> Peter Nolan
> www.peternolan.com
>
|||Hi Dave,
wow, nice to see the guy who 'wrote the code' is watching the forum.
Thanks for your feedback. Makes sense and I can see where BIA could
head in the next release.
You'll be pleased to hear this...the reason for my interest is some
colleagues and I are bringing to market a product that will be based
solely on MSFT BI technologies and part of that is we are researching
everything we can find about MSFT BI and the way forward with MSFT
BI.....I haven't seen any kind of comprehensive suite of slides that
is public in this matter...if you hapeen to know where one can be
downloaded from that would be great.
I stall have to figure out if BIA can fit into what we are doing. We
shall see.
My other comment...
"Q2) DTS packages in BIA -- thank you. I wrote the generator =ADcode and
I am
quite proud of them."
Let's just say I recognise a good idea and a smart guy when I see
one...;-)
One of my specialities is ETL and reducing the cost of writing ETL for
my clients. I've been doing DW for 14 years now and ETL consumes so
much of the money it's the obvious thing to keep working on to reduce.
About 2.5 years ago I investigated the possibility of writing a
generator for ETL jobs in Informatica and DataStage. I found it was
certainly possible to do so. The 'problem' was that it would be
impossible for an 'independent' to make any money out of it. If the
idea did well it would be trivial for the vendor to write the same
functionality and release it. My generator was planned to produce XML
as both INFA/DS can export/import jobs in XML format.
So I was impressed that you have come up with the same idea and from
looking at the time frames you must have had that idea before me...well
done..;-)...And that you are able to produce the DTS jobs in binary.
(Though I'm sure being MSFT you can see the source code/structures for
DTS.)
Me, I changed course after my evaluation and wrote my own ETL tool. It
is very productive. Rather than map fields at field level it maps at
table levels and moves fields within tables on matching column names.
So it is not actually necessary to ever define source to target
mappings anywhere. It discovers the column names at run time so that
when more columns are added there is no code to regenerate or change
and it is the changing of ETL code that is expensive in DW maintenance.
This is another reason why I was impressed with what you have
done....yours is the first example I have seen where it might be
possible to get away with ETL changes without re-testing in the
non-production environment while still using a 'real' ETL tool.
We have also settled on a spreadsheet as the way to record all
mappings. But we had not automated the generation of jobs/code because
even on very large projects the amount of time taken is very small. (I
recently built the ETL for a staging area+DW with 100 tables and 3,000
fields in 2 weeks so there didn't seem to be a need to speed it up even
further.) So your idea of putting a 'generate' button in the
spreadsheet and a whole set of ticks on what to generate out of the
spreadsheet also intrigued me. It looks like a really 'neat trick'. I
am an 'XL-dummy' so that thought had not crossed my mind before. I must
ask one of my colleagues how that was done......The thought has
crossed my mind that our spreadsheet could be extended to record other
information required for ETL generation and we could cut even the 2
weeks work we do now out by generating what we need to generate...
So, like I said, I just recognised a good idea and a smart guy...;-)
All the best...I'll be investigating BIA some more and look forward to
seeing what happens next...
Best Regards
Peter Nolan
www.peternolan.com
|||Actually the DTS generator uses the normal SQL Server APIs to create its
packages. All I did was to create the packages that I wanted; then saved the
package as VB. Then I reversed engineered the VB back into production
quality code. We never had to look at the source code for DTS at all.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1107346392.279399.284200@.f14g2000cwb.googlegr oups.com...
Hi Dave,
wow, nice to see the guy who 'wrote the code' is watching the forum.
Thanks for your feedback. Makes sense and I can see where BIA could
head in the next release.
You'll be pleased to hear this...the reason for my interest is some
colleagues and I are bringing to market a product that will be based
solely on MSFT BI technologies and part of that is we are researching
everything we can find about MSFT BI and the way forward with MSFT
BI.....I haven't seen any kind of comprehensive suite of slides that
is public in this matter...if you hapeen to know where one can be
downloaded from that would be great.
I stall have to figure out if BIA can fit into what we are doing. We
shall see.
My other comment...
"Q2) DTS packages in BIA -- thank you. I wrote the generator Xcode and
I am
quite proud of them."
Let's just say I recognise a good idea and a smart guy when I see
one...;-)
One of my specialities is ETL and reducing the cost of writing ETL for
my clients. I've been doing DW for 14 years now and ETL consumes so
much of the money it's the obvious thing to keep working on to reduce.
About 2.5 years ago I investigated the possibility of writing a
generator for ETL jobs in Informatica and DataStage. I found it was
certainly possible to do so. The 'problem' was that it would be
impossible for an 'independent' to make any money out of it. If the
idea did well it would be trivial for the vendor to write the same
functionality and release it. My generator was planned to produce XML
as both INFA/DS can export/import jobs in XML format.
So I was impressed that you have come up with the same idea and from
looking at the time frames you must have had that idea before me...well
done..;-)...And that you are able to produce the DTS jobs in binary.
(Though I'm sure being MSFT you can see the source code/structures for
DTS.)
Me, I changed course after my evaluation and wrote my own ETL tool. It
is very productive. Rather than map fields at field level it maps at
table levels and moves fields within tables on matching column names.
So it is not actually necessary to ever define source to target
mappings anywhere. It discovers the column names at run time so that
when more columns are added there is no code to regenerate or change
and it is the changing of ETL code that is expensive in DW maintenance.
This is another reason why I was impressed with what you have
done....yours is the first example I have seen where it might be
possible to get away with ETL changes without re-testing in the
non-production environment while still using a 'real' ETL tool.
We have also settled on a spreadsheet as the way to record all
mappings. But we had not automated the generation of jobs/code because
even on very large projects the amount of time taken is very small. (I
recently built the ETL for a staging area+DW with 100 tables and 3,000
fields in 2 weeks so there didn't seem to be a need to speed it up even
further.) So your idea of putting a 'generate' button in the
spreadsheet and a whole set of ticks on what to generate out of the
spreadsheet also intrigued me. It looks like a really 'neat trick'. I
am an 'XL-dummy' so that thought had not crossed my mind before. I must
ask one of my colleagues how that was done......The thought has
crossed my mind that our spreadsheet could be extended to record other
information required for ETL generation and we could cut even the 2
weeks work we do now out by generating what we need to generate...
So, like I said, I just recognised a good idea and a smart guy...;-)
All the best...I'll be investigating BIA some more and look forward to
seeing what happens next...
Best Regards
Peter Nolan
www.peternolan.com
|||Hi Dave,
interesting...I am not up on DTS and I had never heard that the
packages could be saved to VB...I must look into DTS when the next
release comes out.....
I've ask one of my XL knowledgable guys how to put something like a
'generate' button into the spreadsheets we use to write our mappings
and to call some C++ code we have......In fact, we type the
definitions into a spreadsheet and then drop them into the database to
run the code to generate the tables/views!!! (LOL) This is when we
aren't using a data modelling tool. Most places have a standard tool
that must be used for table definitions...
Best Regards
Peter Nolan

BI Accelerator + Applications + Report Services

Hi All,
this seems like the best place for this question.
I've had cause to review the MSFT position/tools in the BI area. I'm
surprised!!! I feel like MSFT have made MUCH more progress than generally
talked about in newsgroups and customers I work with. All I ever hear from
MSFT with respect to DW/BI is 'the next release is going to be great.' Not,
'Hey, take a look at BIA/Reporting Services'...'
Q1. In BI accelerator I installed and tested out the shopfloor application
(manufacturing) and I generated the shopfloor database from the XL Sheet.
But I can't see where it got the table definitions for the staging table and
the underlying dimensional database from. For example, where are the
table/column/datatype definitions of the staging area and the dimensional
database in the XL sheets? I'm sure I looked at all the sheets in the
spreadsheet AnalyticsBuilderWB_ShopFloorPerformance.v.0.2.xls. (But maybe
I'm going crazy and didn't). To me it only seems like the analytical model
is in the spreadsheet.
Q2. I see it generates a ton of .dts files. And when I open them up they
seem to me (a zero level skilled DTS person) to be very complex. The manual
says 'don't change the packages' which I gather means don't look, don't
worry, just run it and it will all be ok....again, I gather that the DTS
packages are somehow generated by BIA into their binary format and I believe
the source to target mappings are defined by the 'mappings' spreadsheet. But
in this spreadsheet I don't seem to find enough columns for all the columns
in the dimensional model. For example I can't find mappings for dim_emp_std
in the spreadsheet but I would have thought it should be there. (Or am I
missing something)'
Q3. In BIA I see lots about sharepoint portal and office objects inside web
pages and all that as a presentation layer. But I just took a look at
Reporting Services. Reporting Services looks absolutely fantastic for what
it is trying to do and it as very obvious how easy it will be to extend
reporting services to do MUCH, MUCH more very, very easily. RDL (Report
Definition Language) is an idea long overdue and I do believe MSFT are first
with that one. I have not heard any other vendor talk about an RDL yet. I
was amazed that a report can just be exposed as a web service and you can
call it from anywhere with anything. Now THAT is a useful thing to have.
So the question is, how come BIA seems to completely ignore Reporting
Services? I would have thought they would be very closely 'joined'. For
example, why not do the front ends for BIA apps in reporting services? I am
assuming this is possible. I'm assuming reporting services can get data out
of Analysis Server because it can get data out of any ado.net server.
(I must say I thought MSF had hidden how good Report Services is very
well...unless I'm greatly mistaken, I've only had a few hours to look at it,
it looks like a really, really useful product!!!)
Anyway, thanks in advance if anyone can let me know what I am missing in
BIA.
Best Regards
Peter Nolan
www.peternolan.comWow. There is a lot of info here. A couple of comments:
Q1) datatypes using in BIA -- well, we make several guesses. First we know
that all measures are numeric (that is a requirement of Analysis Services).
Second we know that fields that we generate have specific uses -- and from
that we know their datatypes. For example, the surrogate keys are integers
(as you would expect because they are identity). For those fields which are
user supplies, e.g. member names -- we know just treat them as "names", i.e.
varchars. If you have something different, e.g. names which are really
integers, they will have to re-do things by-hand. For those things which
could be any datatype, e.g. member properties, you can choice the
appropriate datatypes.
If there are specific tables and columns you are wondering about just tell
me and I'd be glad to explain why we did something one way or the other.
What you are seeing the whole point of the BI Accelerator. You layout the
logical multidimensional design and we auto-generate a relational staging
area; final data mart, OLAP structures, and DTS packages to move the data
through it. Logical data model to final app in one click :-)
And that final app should have all of the tips and tricks that you would see
in a production system; not just a rough proof-of-concept system.
Q2) DTS packages in BIA -- thank you. I wrote the generator code and I am
quite proud of them.
It generates a ton of packages because of the type of schema we choose to
implement. Since we generate a snowflake schema, there are tables for every
dimension and level in the dimensional structure.
We had several goals with the DTS packages. First, we wanted them to be
data-driven because we don't expect everyone to be an expert on DTS. Thus
rather than having to make changes to them, most of them have variables you
can change value and have the package do something different. All of this is
documented in the PAG (online doc set). One of the challenges of this was
that DTS with SQL Server 2000 doesn't have the control flow tasks needed to
make this declaritive -- with 2005, we put that all in natively and similar
packages with 2005 would be quite a bit less complex.
Second, we wanted the packages to be visible and extensiveable by
knowledgeable users. Nothing is hidden -- it is all up front and in your
face. Yes, a novice will look at them and dispare -- but don't give up!
There is documentation in the PAG provided for all of them! Lastly you might
be interested in this white paper which talks about the DTS packages and
provides various tips and tricks beyond what the PAG has in it.
http://msdn.microsoft.com/library/d...
dts_ssabi.asp
If there is a specific step you have questions about, I'd be glad to help
also.
Q3) I am glad you like Reporting Services. I agree it is a fantastic tool.
However, it is a totally different product. BI Accelerator is a BI
application generator; not a report generator. It has a client component
only for customization. The idea was that suppose you had a report called a
"template" which was exactly the same from client to client, but one
customer called products "items" and another called products "books".
Wouldn't it be nice to ship a template along with the multidimensional
design and have the system automatically rename products to items or
products to books. That is what the client generator does with the BI
Accelerator. It ships with a Proclarity component which knows how to go
inside a Proclarity briefing book and replace one tag for another tag. A
similar facility is available from Panorama and there is an API available to
other vendors if they would like to plug into the BI Accelerator client
generator.
Hope this helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:uCEWHDlBFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> this seems like the best place for this question.
> I've had cause to review the MSFT position/tools in the BI area. I'm
> surprised!!! I feel like MSFT have made MUCH more progress than generally
> talked about in newsgroups and customers I work with. All I ever hear
from
> MSFT with respect to DW/BI is 'the next release is going to be great.'
Not,
> 'Hey, take a look at BIA/Reporting Services'...'
> Q1. In BI accelerator I installed and tested out the shopfloor application
> (manufacturing) and I generated the shopfloor database from the XL Sheet.
> But I can't see where it got the table definitions for the staging table
and
> the underlying dimensional database from. For example, where are the
> table/column/datatype definitions of the staging area and the dimensional
> database in the XL sheets? I'm sure I looked at all the sheets in the
> spreadsheet AnalyticsBuilderWB_ShopFloorPerformance.v.0.2.xls. (But maybe
> I'm going crazy and didn't). To me it only seems like the analytical
model
> is in the spreadsheet.
>
> Q2. I see it generates a ton of .dts files. And when I open them up they
> seem to me (a zero level skilled DTS person) to be very complex. The
manual
> says 'don't change the packages' which I gather means don't look, don't
> worry, just run it and it will all be ok....again, I gather that the DTS
> packages are somehow generated by BIA into their binary format and I
believe
> the source to target mappings are defined by the 'mappings' spreadsheet.
But
> in this spreadsheet I don't seem to find enough columns for all the
columns
> in the dimensional model. For example I can't find mappings for
dim_emp_std
> in the spreadsheet but I would have thought it should be there. (Or am I
> missing something)'
>
> Q3. In BIA I see lots about sharepoint portal and office objects inside
web
> pages and all that as a presentation layer. But I just took a look at
> Reporting Services. Reporting Services looks absolutely fantastic for
what
> it is trying to do and it as very obvious how easy it will be to extend
> reporting services to do MUCH, MUCH more very, very easily. RDL (Report
> Definition Language) is an idea long overdue and I do believe MSFT are
first
> with that one. I have not heard any other vendor talk about an RDL yet.
I
> was amazed that a report can just be exposed as a web service and you can
> call it from anywhere with anything. Now THAT is a useful thing to have.
> So the question is, how come BIA seems to completely ignore Reporting
> Services? I would have thought they would be very closely 'joined'. For
> example, why not do the front ends for BIA apps in reporting services? I
am
> assuming this is possible. I'm assuming reporting services can get data
out
> of Analysis Server because it can get data out of any ado.net server.
> (I must say I thought MSF had hidden how good Report Services is very
> well...unless I'm greatly mistaken, I've only had a few hours to look at
it,
> it looks like a really, really useful product!!!)
> Anyway, thanks in advance if anyone can let me know what I am missing in
> BIA.
> Best Regards
> Peter Nolan
> www.peternolan.com
>|||Hi Dave,
wow, nice to see the guy who 'wrote the code' is watching the forum.
Thanks for your feedback. Makes sense and I can see where BIA could
head in the next release.
You'll be pleased to hear this...the reason for my interest is some
colleagues and I are bringing to market a product that will be based
solely on MSFT BI technologies and part of that is we are researching
everything we can find about MSFT BI and the way forward with MSFT
BI.....I haven't seen any kind of comprehensive suite of slides that
is public in this matter...if you hapeen to know where one can be
downloaded from that would be great.
I stall have to figure out if BIA can fit into what we are doing. We
shall see.
My other comment...
"Q2) DTS packages in BIA -- thank you. I wrote the generator =ADcode and
I am
quite proud of them."
Let's just say I recognise a good idea and a smart guy when I see
one...;-)
One of my specialities is ETL and reducing the cost of writing ETL for
my clients. I've been doing DW for 14 years now and ETL consumes so
much of the money it's the obvious thing to keep working on to reduce.
About 2.5 years ago I investigated the possibility of writing a
generator for ETL jobs in Informatica and DataStage. I found it was
certainly possible to do so. The 'problem' was that it would be
impossible for an 'independent' to make any money out of it. If the
idea did well it would be trivial for the vendor to write the same
functionality and release it. My generator was planned to produce XML
as both INFA/DS can export/import jobs in XML format.
So I was impressed that you have come up with the same idea and from
looking at the time frames you must have had that idea before me...well
done..;-)...And that you are able to produce the DTS jobs in binary.
(Though I'm sure being MSFT you can see the source code/structures for
DTS.)
Me, I changed course after my evaluation and wrote my own ETL tool. It
is very productive. Rather than map fields at field level it maps at
table levels and moves fields within tables on matching column names.
So it is not actually necessary to ever define source to target
mappings anywhere. It discovers the column names at run time so that
when more columns are added there is no code to regenerate or change
and it is the changing of ETL code that is expensive in DW maintenance.
This is another reason why I was impressed with what you have
done....yours is the first example I have seen where it might be
possible to get away with ETL changes without re-testing in the
non-production environment while still using a 'real' ETL tool.
We have also settled on a spreadsheet as the way to record all
mappings. But we had not automated the generation of jobs/code because
even on very large projects the amount of time taken is very small. (I
recently built the ETL for a staging area+DW with 100 tables and 3,000
fields in 2 weeks so there didn't seem to be a need to speed it up even
further.) So your idea of putting a 'generate' button in the
spreadsheet and a whole set of ticks on what to generate out of the
spreadsheet also intrigued me. It looks like a really 'neat trick'. I
am an 'XL-dummy' so that thought had not crossed my mind before. I must
ask one of my colleagues how that was done......The thought has
crossed my mind that our spreadsheet could be extended to record other
information required for ETL generation and we could cut even the 2
weeks work we do now out by generating what we need to generate...
So, like I said, I just recognised a good idea and a smart guy...;-)
All the best...I'll be investigating BIA some more and look forward to
seeing what happens next...
Best Regards
Peter Nolan
www.peternolan.com|||Actually the DTS generator uses the normal SQL Server APIs to create its
packages. All I did was to create the packages that I wanted; then saved the
package as VB. Then I reversed engineered the VB back into production
quality code. We never had to look at the source code for DTS at all.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1107346392.279399.284200@.f14g2000cwb.googlegroups.com...
Hi Dave,
wow, nice to see the guy who 'wrote the code' is watching the forum.
Thanks for your feedback. Makes sense and I can see where BIA could
head in the next release.
You'll be pleased to hear this...the reason for my interest is some
colleagues and I are bringing to market a product that will be based
solely on MSFT BI technologies and part of that is we are researching
everything we can find about MSFT BI and the way forward with MSFT
BI.....I haven't seen any kind of comprehensive suite of slides that
is public in this matter...if you hapeen to know where one can be
downloaded from that would be great.
I stall have to figure out if BIA can fit into what we are doing. We
shall see.
My other comment...
"Q2) DTS packages in BIA -- thank you. I wrote the generator _code and
I am
quite proud of them."
Let's just say I recognise a good idea and a smart guy when I see
one...;-)
One of my specialities is ETL and reducing the cost of writing ETL for
my clients. I've been doing DW for 14 years now and ETL consumes so
much of the money it's the obvious thing to keep working on to reduce.
About 2.5 years ago I investigated the possibility of writing a
generator for ETL jobs in Informatica and DataStage. I found it was
certainly possible to do so. The 'problem' was that it would be
impossible for an 'independent' to make any money out of it. If the
idea did well it would be trivial for the vendor to write the same
functionality and release it. My generator was planned to produce XML
as both INFA/DS can export/import jobs in XML format.
So I was impressed that you have come up with the same idea and from
looking at the time frames you must have had that idea before me...well
done..;-)...And that you are able to produce the DTS jobs in binary.
(Though I'm sure being MSFT you can see the source code/structures for
DTS.)
Me, I changed course after my evaluation and wrote my own ETL tool. It
is very productive. Rather than map fields at field level it maps at
table levels and moves fields within tables on matching column names.
So it is not actually necessary to ever define source to target
mappings anywhere. It discovers the column names at run time so that
when more columns are added there is no code to regenerate or change
and it is the changing of ETL code that is expensive in DW maintenance.
This is another reason why I was impressed with what you have
done....yours is the first example I have seen where it might be
possible to get away with ETL changes without re-testing in the
non-production environment while still using a 'real' ETL tool.
We have also settled on a spreadsheet as the way to record all
mappings. But we had not automated the generation of jobs/code because
even on very large projects the amount of time taken is very small. (I
recently built the ETL for a staging area+DW with 100 tables and 3,000
fields in 2 weeks so there didn't seem to be a need to speed it up even
further.) So your idea of putting a 'generate' button in the
spreadsheet and a whole set of ticks on what to generate out of the
spreadsheet also intrigued me. It looks like a really 'neat trick'. I
am an 'XL-dummy' so that thought had not crossed my mind before. I must
ask one of my colleagues how that was done......The thought has
crossed my mind that our spreadsheet could be extended to record other
information required for ETL generation and we could cut even the 2
weeks work we do now out by generating what we need to generate...
So, like I said, I just recognised a good idea and a smart guy...;-)
All the best...I'll be investigating BIA some more and look forward to
seeing what happens next...
Best Regards
Peter Nolan
www.peternolan.com|||Hi Dave,
interesting...I am not up on DTS and I had never heard that the
packages could be saved to VB...I must look into DTS when the next
release comes out.....
I've ask one of my XL knowledgable guys how to put something like a
'generate' button into the spreadsheets we use to write our mappings
and to call some C++ code we have......In fact, we type the
definitions into a spreadsheet and then drop them into the database to
run the code to generate the tables/views!!! (LOL) This is when we
aren't using a data modelling tool. Most places have a standard tool
that must be used for table definitions...
Best Regards
Peter Nolan

Tuesday, March 27, 2012

Binding Datatable to Report

Binding dataset to a crystal report

Bind object to RDLC Report

Nested objects

Bind object to RDLC Report

Nested objects

Sunday, March 25, 2012

bind matrix or table to image or map

Bind many tables from a single sp on many tables on a single repor

Bind a Table to a StoredProcedure

Bind a stored procedure within dataview

BINARY_INTEGER in CRXI

Tuesday, March 20, 2012

Big picture question about Reporting Services

Monday, March 19, 2012

BIDS bug with SSIS Annotations

BIDS - How to turn-off auto naming of fields

Sunday, March 11, 2012

BIDS - How to turn-off auto naming of fields

Thursday, March 8, 2012

BI on Windows XP

BI Development Studio Licensing and VS Express Edition

BI- Collaboration and reports

Wednesday, March 7, 2012

BI Accelerator + Applications + Report Services

BI Accelerator + Applications + Report Services

Best way to copy database

Blog Archive

About Me