Import Design Question

I have at least three tables into which the user can import data.
The three tables are 1) Portfolio 2) Account 3) Transactions. The user
will provide us three files - one for each entity (in the file) related
by a userdefined NUMBER field.
These three tables are linked by foreign keys but the key columns in
the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
TRANSACTIONID).
The files can be really big so the import has to be quick. Right now I
first insert the rows into PORTFOLIO, get the generated IDs , loop
through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
ID and then import the TRANSACTIONS. This process of retreiving and
setting the IDs is consuming a long time and making the import very
slow. I am wondering if using IDENTITY is the right design for the
above mentioned tables. Is it better to generate the key values myself
using a SEED table?
Is there a more elegant solution to the problem?
Thanks.Looping? In SQL?
Don't you have any natural keys in the source files? If there is a natural
key (or at least one or more candidate columns) use that to achieve a
set-based solution.
Whether you use identity or any other kind of key generation is IMHO
irrelevant, since there really should be a more natural way to uniquely
identify each row of data (i.e. a natural relationship between the sets).
ML
http://milambda.blogspot.com/|||S Chapman wrote:
> I have at least three tables into which the user can import data.
> The three tables are 1) Portfolio 2) Account 3) Transactions. The user
> will provide us three files - one for each entity (in the file) related
> by a userdefined NUMBER field.
> These three tables are linked by foreign keys but the key columns in
> the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
> TRANSACTIONID).
> The files can be really big so the import has to be quick. Right now I
> first insert the rows into PORTFOLIO, get the generated IDs , loop
> through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
> ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
> ID and then import the TRANSACTIONS. This process of retreiving and
> setting the IDs is consuming a long time and making the import very
> slow. I am wondering if using IDENTITY is the right design for the
> above mentioned tables. Is it better to generate the key values myself
> using a SEED table?
> Is there a more elegant solution to the problem?
> Thanks.
>
Sounds like your relationships are like this:
Portfolio -> AccountID -> TransactionID
meaning a single portfolio links to multiple Account ID's, a single
account links to multiple Transaction ID's.
You might consider changing this. Look for natural keys to link on
instead of manufacturing one (using an ID value). For instance, a
transaction should link to an account via ACCOUNT NUMBER. A portfolio
should contain multiple ACCOUNT NUMBERS, not account ID's.
What this will allow you to do is to bulk import all three tables
without having to mess with finding ID's, updating ID's, etc. The data
is already naturally linked together.|||Unfortunately there are no natural keys. The Portfolio Number and
Account Number can be gauranteed to be unique only within a batch.
Also, I am NOT looping through in Sql, looping through rows in the
dataset inside the program.
Tracy McKibben wrote:
> S Chapman wrote:
> Sounds like your relationships are like this:
> Portfolio -> AccountID -> TransactionID
> meaning a single portfolio links to multiple Account ID's, a single
> account links to multiple Transaction ID's.
> You might consider changing this. Look for natural keys to link on
> instead of manufacturing one (using an ID value). For instance, a
> transaction should link to an account via ACCOUNT NUMBER. A portfolio
> should contain multiple ACCOUNT NUMBERS, not account ID's.
> What this will allow you to do is to bulk import all three tables
> without having to mess with finding ID's, updating ID's, etc. The data
> is already naturally linked together.

Import & Export

Hi,
I'm using Reporting Services and MOM 2005.
How can I import existing reports to Visual Studio for design mode?I means to import all the reports and not one by one
Also, is it possible in Visual Studio to configure each report to a
different TargetFolder in the same project?
"×¢×?×?×?" wrote:
> Hi,
> I'm using Reporting Services and MOM 2005.
> How can I import existing reports to Visual Studio for design mode?|||Hi,
I try to upload my report to the reporting services using
PublishSampleReports.rss
I try to upload to a subfolder parentFolder="All SQL Report/performance"
but I ger an error message,
How can I do that ?
"×¢×?×?×?" wrote:
> I means to import all the reports and not one by one
> Also, is it possible in Visual Studio to configure each report to a
> different TargetFolder in the same project?
> "×¢×?×?×?" wrote:
> > Hi,
> >
> > I'm using Reporting Services and MOM 2005.
> > How can I import existing reports to Visual Studio for design mode?|||What error message are you getting?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"'" <@.discussions.microsoft.com> wrote in message
news:CB82802F-3506-4BEA-973C-24D0D35813F8@.microsoft.com...
> Hi,
> I try to upload my report to the reporting services using
> PublishSampleReports.rss
> I try to upload to a subfolder parentFolder="All SQL Report/performance"
> but I ger an error message,
> How can I do that ?
> "'" wrote:
>> I means to import all the reports and not one by one
>> Also, is it possible in Visual Studio to configure each report to a
>> different TargetFolder in the same project?
>> "'" wrote:
>> > Hi,
>> >
>> > I'm using Reporting Services and MOM 2005.
>> > How can I import existing reports to Visual Studio for design mode?|||The error message:
C:\Reporting>rs -i PublishSampleReports.rss -s
http://momreporting/reportserver/
-v parentFolder="Microsoft Operations Manager Reporting/idanl"
System.Web.Services.Protocols.SoapException: The name of the item 'Microsoft
Ope
rations Manager Reporting/idanl' is not valid. The name must be less than
260 ch
aracters long, must not start with slash; other restrictions apply. Check
the do
cumentation for complete set of restrictions. -->
Microsoft.ReportingServices.D
iagnostics.Utilities.InvalidItemNameException: The name of the item
'Microsoft O
perations Manager Reporting/idanl' is not valid. The name must be less than
260
characters long, must not start with slash; other restrictions apply. Check
the
documentation for complete set of restrictions.
"Jeff A. Stucker" wrote:
> What error message are you getting?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "'" <@.discussions.microsoft.com> wrote in message
> news:CB82802F-3506-4BEA-973C-24D0D35813F8@.microsoft.com...
> > Hi,
> > I try to upload my report to the reporting services using
> > PublishSampleReports.rss
> > I try to upload to a subfolder parentFolder="All SQL Report/performance"
> > but I ger an error message,
> > How can I do that ?
> >
> > "'" wrote:
> >
> >> I means to import all the reports and not one by one
> >>
> >> Also, is it possible in Visual Studio to configure each report to a
> >> different TargetFolder in the same project?
> >>
> >> "'" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm using Reporting Services and MOM 2005.
> >> > How can I import existing reports to Visual Studio for design mode?
>
>|||Sorry I can't help you on the RSS issue.
But I can answer the question about Visual Studio. Unfortunately, at this
time there is a one-to-one mapping between project and folder. I set up a
VS solution with multiple projects -- one for each folder I need to publish
to.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"'" <@.discussions.microsoft.com> wrote in message
news:1DA41224-9065-4713-8169-D9F1EE29CAED@.microsoft.com...
> The error message:
> C:\Reporting>rs -i PublishSampleReports.rss -s
> http://momreporting/reportserver/
> -v parentFolder="Microsoft Operations Manager Reporting/idanl"
> System.Web.Services.Protocols.SoapException: The name of the item
> 'Microsoft
> Ope
> rations Manager Reporting/idanl' is not valid. The name must be less than
> 260 ch
> aracters long, must not start with slash; other restrictions apply. Check
> the do
> cumentation for complete set of restrictions. -->
> Microsoft.ReportingServices.D
> iagnostics.Utilities.InvalidItemNameException: The name of the item
> 'Microsoft O
> perations Manager Reporting/idanl' is not valid. The name must be less
> than
> 260
> characters long, must not start with slash; other restrictions apply.
> Check
> the
> documentation for complete set of restrictions.
> "Jeff A. Stucker" wrote:
>> What error message are you getting?
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "'" <@.discussions.microsoft.com> wrote in message
>> news:CB82802F-3506-4BEA-973C-24D0D35813F8@.microsoft.com...
>> > Hi,
>> > I try to upload my report to the reporting services using
>> > PublishSampleReports.rss
>> > I try to upload to a subfolder parentFolder="All SQL
>> > Report/performance"
>> > but I ger an error message,
>> > How can I do that ?
>> >
>> > "'" wrote:
>> >
>> >> I means to import all the reports and not one by one
>> >>
>> >> Also, is it possible in Visual Studio to configure each report to a
>> >> different TargetFolder in the same project?
>> >>
>> >> "'" wrote:
>> >>
>> >> > Hi,
>> >> >
>> >> > I'm using Reporting Services and MOM 2005.
>> >> > How can I import existing reports to Visual Studio for design mode?
>>|||Hi Jeff
I succeed to solve the problem with the path problem by run this command
without the http://momreporting/reportserver/
But while I import the report to the reporting services I get in all the
report a new text box to enter user name & password, why ?
Thanks and have a happy new years
Idan
"Jeff A. Stucker" wrote:
> Sorry I can't help you on the RSS issue.
> But I can answer the question about Visual Studio. Unfortunately, at this
> time there is a one-to-one mapping between project and folder. I set up a
> VS solution with multiple projects -- one for each folder I need to publish
> to.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "'" <@.discussions.microsoft.com> wrote in message
> news:1DA41224-9065-4713-8169-D9F1EE29CAED@.microsoft.com...
> > The error message:
> >
> > C:\Reporting>rs -i PublishSampleReports.rss -s
> > http://momreporting/reportserver/
> > -v parentFolder="Microsoft Operations Manager Reporting/idanl"
> > System.Web.Services.Protocols.SoapException: The name of the item
> > 'Microsoft
> > Ope
> > rations Manager Reporting/idanl' is not valid. The name must be less than
> > 260 ch
> > aracters long, must not start with slash; other restrictions apply. Check
> > the do
> > cumentation for complete set of restrictions. -->
> > Microsoft.ReportingServices.D
> > iagnostics.Utilities.InvalidItemNameException: The name of the item
> > 'Microsoft O
> > perations Manager Reporting/idanl' is not valid. The name must be less
> > than
> > 260
> > characters long, must not start with slash; other restrictions apply.
> > Check
> > the
> > documentation for complete set of restrictions.
> >
> > "Jeff A. Stucker" wrote:
> >
> >> What error message are you getting?
> >>
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "'" <@.discussions.microsoft.com> wrote in message
> >> news:CB82802F-3506-4BEA-973C-24D0D35813F8@.microsoft.com...
> >> > Hi,
> >> > I try to upload my report to the reporting services using
> >> > PublishSampleReports.rss
> >> > I try to upload to a subfolder parentFolder="All SQL
> >> > Report/performance"
> >> > but I ger an error message,
> >> > How can I do that ?
> >> >
> >> > "'" wrote:
> >> >
> >> >> I means to import all the reports and not one by one
> >> >>
> >> >> Also, is it possible in Visual Studio to configure each report to a
> >> >> different TargetFolder in the same project?
> >> >>
> >> >> "'" wrote:
> >> >>
> >> >> > Hi,
> >> >> >
> >> >> > I'm using Reporting Services and MOM 2005.
> >> >> > How can I import existing reports to Visual Studio for design mode?
> >>
> >>
> >>
>
>|||Check the report permissions in Report Manager (http://<server>/Reports)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"'" <@.discussions.microsoft.com> wrote in message
news:5DE0A914-146E-46B9-AF6A-F77127BE5A47@.microsoft.com...
> Hi Jeff
> I succeed to solve the problem with the path problem by run this command
> without the http://momreporting/reportserver/
> But while I import the report to the reporting services I get in all the
> report a new text box to enter user name & password, why ?
> Thanks and have a happy new years
> Idan
>
> "Jeff A. Stucker" wrote:
>> Sorry I can't help you on the RSS issue.
>> But I can answer the question about Visual Studio. Unfortunately, at
>> this
>> time there is a one-to-one mapping between project and folder. I set up
>> a
>> VS solution with multiple projects -- one for each folder I need to
>> publish
>> to.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "'" <@.discussions.microsoft.com> wrote in message
>> news:1DA41224-9065-4713-8169-D9F1EE29CAED@.microsoft.com...
>> > The error message:
>> >
>> > C:\Reporting>rs -i PublishSampleReports.rss -s
>> > http://momreporting/reportserver/
>> > -v parentFolder="Microsoft Operations Manager Reporting/idanl"
>> > System.Web.Services.Protocols.SoapException: The name of the item
>> > 'Microsoft
>> > Ope
>> > rations Manager Reporting/idanl' is not valid. The name must be less
>> > than
>> > 260 ch
>> > aracters long, must not start with slash; other restrictions apply.
>> > Check
>> > the do
>> > cumentation for complete set of restrictions. -->
>> > Microsoft.ReportingServices.D
>> > iagnostics.Utilities.InvalidItemNameException: The name of the item
>> > 'Microsoft O
>> > perations Manager Reporting/idanl' is not valid. The name must be less
>> > than
>> > 260
>> > characters long, must not start with slash; other restrictions apply.
>> > Check
>> > the
>> > documentation for complete set of restrictions.
>> >
>> > "Jeff A. Stucker" wrote:
>> >
>> >> What error message are you getting?
>> >>
>> >> --
>> >> Cheers,
>> >>
>> >> '(' Jeff A. Stucker
>> >> \
>> >>
>> >> Business Intelligence
>> >> www.criadvantage.com
>> >> ---
>> >> "'" <@.discussions.microsoft.com> wrote in message
>> >> news:CB82802F-3506-4BEA-973C-24D0D35813F8@.microsoft.com...
>> >> > Hi,
>> >> > I try to upload my report to the reporting services using
>> >> > PublishSampleReports.rss
>> >> > I try to upload to a subfolder parentFolder="All SQL
>> >> > Report/performance"
>> >> > but I ger an error message,
>> >> > How can I do that ?
>> >> >
>> >> > "'" wrote:
>> >> >
>> >> >> I means to import all the reports and not one by one
>> >> >>
>> >> >> Also, is it possible in Visual Studio to configure each report to a
>> >> >> different TargetFolder in the same project?
>> >> >>
>> >> >> "'" wrote:
>> >> >>
>> >> >> > Hi,
>> >> >> >
>> >> >> > I'm using Reporting Services and MOM 2005.
>> >> >> > How can I import existing reports to Visual Studio for design
>> >> >> > mode?
>> >>
>> >>
>> >>
>>|||Thanks jeff
I know I can change the report permission under each report,
but if I have a lot of report I need to change it each one, Why the import
process cause this ?
"Jeff A. Stucker" wrote:
> Check the report permissions in Report Manager (http://<server>/Reports)
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "'" <@.discussions.microsoft.com> wrote in message
> news:5DE0A914-146E-46B9-AF6A-F77127BE5A47@.microsoft.com...
> > Hi Jeff
> > I succeed to solve the problem with the path problem by run this command
> > without the http://momreporting/reportserver/
> >
> > But while I import the report to the reporting services I get in all the
> > report a new text box to enter user name & password, why ?
> >
> > Thanks and have a happy new years
> >
> > Idan
> >
> >
> > "Jeff A. Stucker" wrote:
> >
> >> Sorry I can't help you on the RSS issue.
> >>
> >> But I can answer the question about Visual Studio. Unfortunately, at
> >> this
> >> time there is a one-to-one mapping between project and folder. I set up
> >> a
> >> VS solution with multiple projects -- one for each folder I need to
> >> publish
> >> to.
> >>
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "'" <@.discussions.microsoft.com> wrote in message
> >> news:1DA41224-9065-4713-8169-D9F1EE29CAED@.microsoft.com...
> >> > The error message:
> >> >
> >> > C:\Reporting>rs -i PublishSampleReports.rss -s
> >> > http://momreporting/reportserver/
> >> > -v parentFolder="Microsoft Operations Manager Reporting/idanl"
> >> > System.Web.Services.Protocols.SoapException: The name of the item
> >> > 'Microsoft
> >> > Ope
> >> > rations Manager Reporting/idanl' is not valid. The name must be less
> >> > than
> >> > 260 ch
> >> > aracters long, must not start with slash; other restrictions apply.
> >> > Check
> >> > the do
> >> > cumentation for complete set of restrictions. -->
> >> > Microsoft.ReportingServices.D
> >> > iagnostics.Utilities.InvalidItemNameException: The name of the item
> >> > 'Microsoft O
> >> > perations Manager Reporting/idanl' is not valid. The name must be less
> >> > than
> >> > 260
> >> > characters long, must not start with slash; other restrictions apply.
> >> > Check
> >> > the
> >> > documentation for complete set of restrictions.
> >> >
> >> > "Jeff A. Stucker" wrote:
> >> >
> >> >> What error message are you getting?
> >> >>
> >> >> --
> >> >> Cheers,
> >> >>
> >> >> '(' Jeff A. Stucker
> >> >> \
> >> >>
> >> >> Business Intelligence
> >> >> www.criadvantage.com
> >> >> ---
> >> >> "'" <@.discussions.microsoft.com> wrote in message
> >> >> news:CB82802F-3506-4BEA-973C-24D0D35813F8@.microsoft.com...
> >> >> > Hi,
> >> >> > I try to upload my report to the reporting services using
> >> >> > PublishSampleReports.rss
> >> >> > I try to upload to a subfolder parentFolder="All SQL
> >> >> > Report/performance"
> >> >> > but I ger an error message,
> >> >> > How can I do that ?
> >> >> >
> >> >> > "'" wrote:
> >> >> >
> >> >> >> I means to import all the reports and not one by one
> >> >> >>
> >> >> >> Also, is it possible in Visual Studio to configure each report to a
> >> >> >> different TargetFolder in the same project?
> >> >> >>
> >> >> >> "'" wrote:
> >> >> >>
> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > I'm using Reporting Services and MOM 2005.
> >> >> >> > How can I import existing reports to Visual Studio for design
> >> >> >> > mode?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>