Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Help? Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:34 AM
Points: 9, Visits: 41
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 !
Post #1533030
Posted Tuesday, January 21, 2014 7:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 2,729, Visits: 2,646
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
Post #1533047
Posted Tuesday, January 21, 2014 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:34 AM
Points: 9, Visits: 41
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?
Post #1533264
Posted Tuesday, January 21, 2014 11:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 2,729, Visits: 2,646
Is the number of tables for inserting into the Fact table fixed or flexible?
Post #1533276
Posted Tuesday, January 21, 2014 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 8:34 AM
Points: 9, Visits: 41
fixed
Post #1533277
Posted Tuesday, January 21, 2014 11:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 2,729, Visits: 2,646
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.
Post #1533284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse