Need Help?

• bala2014

  SSC Enthusiast

  Points: 171

  More actions

  January 21, 2014 at 6:49 am

  #281440

  Dear All,

  For SSAS Cube process,we need create fact table.

  In sql server 2008,we have table source in Dynamics nav,in sql server we have a master table for company

  it contains 18 company's like 'A','B','C','D','E',..etc.

  each company have separate transaction tables,for example in company A have valueentry table but table name like A$Valueentry,same table in company B in B$Valueentry also for Company C$Valueentry,D$Valueentry,E$Valueentry....

  my situation is i need to insert the sales amount from A$valueentry,B$Valueentry,C$Valueentry..etc table to fact table(like staging query).how to loop this insert query for all company's.

  thanks !

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  January 21, 2014 at 7:15 am

  #1681889

  Hi,

  I can suggest to you considering the Multicast component in SSIS. It makes multiple logical copies of data and can deliver it to multiple destinations.

  Regards,

  IgorMi

  Igor Micev,My blog: www.igormicev.com

• bala2014

  SSC Enthusiast

  Points: 171

  More actions

  January 21, 2014 at 10:31 am

  #1682088

  hi,

  thanks for ur reply..

  i clearly explain u...

  we have company master table ,

  company

  =======

  A

  B

  C

  D

  E

  ...etc

  transaction table per company as

  A$Item

  A$ValueEntry

  A$Customer

  B$Item

  B$ValueEntry

  C$Item

  C$ValueEntry

  this is tables in sql,now i have design the cube,i need insert sales amount for entrier company(A,B,C,D..etc),

  but sales amount from table A$ValueEntry for Company A, B$ValueEntry for Company B, C$ValueEntry for Company C...likewise etc...

  how we insert sales amount to fact table....this is my question.

  i need sql query for this?

  Please help me on this?

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  January 21, 2014 at 11:01 am

  #1682099

  Is the number of tables for inserting into the Fact table fixed or flexible?

  Igor Micev,My blog: www.igormicev.com

• bala2014

  SSC Enthusiast

  Points: 171

  More actions

  January 21, 2014 at 11:03 am

  #1682100

  fixed

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  January 21, 2014 at 11:10 am

  #1682106

  bala2014 (1/21/2014)

  ---

  fixed

  Than, why do you need a code to iterate the tables? It does not make a difference iterating the tables constructing dynamic insert-statements and execute them compared to writing insert-statement for each table and make the inserts into the Fact table.

  Best practices for inserting data in a datawarehouse is developing a SSIS package. There are many advantages of using SSIS packages. SSIS is developed for that goal. But if you don't have experience with SSIS, then you can write insert-statement codes and schedule them with a job.

  Igor Micev,My blog: www.igormicev.com