.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

How to combine 3 Fact Tables into 1: UNION in TSQL vs. MDX Scripts

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

Hi All,

I have 3 Fact Tables I would like to combine them into 1, I am not sure what's the best way to do it.

In Adventure Works, it uses UNION in TSQL to create a sales_summary Fact from Internet Sales and Reseller Sales.

Can I just write a simple Calculated measure?  What's the best way to handle this kind of situation?







[Distribution Partner].[Distribution Partner].[DP]

View Complete Post

More Related Resource Links

Sql Scripts - Delete all Tables, Procedures, Views and Functions


In a shared environment you typically don't have access to delete your database, and recreate it for fresh installs of your product. 

I managed to find these scripts which should help you clean out your database.

Use at your own risk.


Delete All Tables

--Delete All Keys


Relationship between a dimension - two fact tables

I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

Combine two tables

I have two tables: tInv containing invoices. The fields I am interested in: id cusid ' customer id source ' invoice# dtjournal ' invoice date And tSal containing orders (not billed/shipped yet) with the fields: id cusid ' customerid salnr ' sales order number dtsalordr ' sales order date I need to query these two tables and return a single table ordered by date, containing: id,cusid,number,date,type ('in' or 'so') In other words I need to select id and cusid from both tables and dtjournal and dtsalordr from either table and combine into one column 'date' and get source & salnr and combine into 'number' I want a list, ordered by date, of all invoices & orders. Is this a possibility with a SELECT statement or do I need to fetch both tables, and then manually move the rows into a new table?   Thanks, Erwin

Join two fact tables




I have the following two fact tables, upon which a cube was built. As you can see, these two tables have most of the common (shared) dimensions except for ProductTypeID (product dimension) on fct_productsales table. The relationship between these two fact tables is 1(fct_loansales) to Many (fct_productsales).

When I browse these measures from the cube, they look fine (and break down perfectly) through all the shared dimensions.

However, when I try to filter or browse through product dimension (along with other dimensions), the measure values from fct_productsales will filter down correctly, but the values from fct_loansales does not change.  I tryied to create degenerate dimension from fct_loansales, but no luck. Any help is appreciated.



AccountNumber                       char

how to Combine Two tables borders


i am showing border to outer table border="1" and i placed a table inside outer table with border="1" 

I am getting two borders at that point

Is there any way to show only one border. Can i combine both table border

Can I Combine resultset return by union




Is there a way to row two and three of the result set be returned so that the


following query:


Select Distinct A as TotalUniqueTranTypeT, B as TotalUniqueTranTypeO, C as Week from


SELECT     COUNT(DISTINCT EmailTracking.EmailAddress) AS B, 0 as A, DATEPART(Week, EmailTrackingDetail.TranDate) AS C

FROM         EmailTrackingDetail INNER JOIN

                      EmailTracking ON EmailTrackingDetail.EmailTrackingRecordID = EmailTracking.RecordID

WHERE     (EmailTrackingDetail.TranType = 'O')

GROUP BY DATEPART(Week, EmailTrackingDetail.TranDate)


SELECT     0 AS B, COUNT(DISTINCT EmailTracking.EmailAddress) AS A, DATEPART(Week, EmailTrackingDetail.TranDate) AS C

FROM   EmailTrackingDetail INNER JOIN

                      EmailTracking ON EmailTrackingDetail.EmailTrackingRecordID = EmailTracking.RecordID

WHERE   &n

Loading Dimension & Fact tables


Hello Experts,

I am new to SSAS & I need to load Dim & Fact tables for a data warehouse. I've the basic idea to load them, but i dont have the exact picture to load a fact table. For eg. if we have 3 dimension tables like Dim_Time / Dim_Geography / Dim_Product and one Fact Table Fact_Product as described in most of the online examples. Then how to load them. I know for fact Tables we need to do aggregations. But how to apply those aggregations on what criteria.  In the said example if we need to load Fact_Product then we should be able to see sales by product, sales for a given point in time & sales for a given geographic location. Then how to do that. Do we need to apply aggregate for all the dimension tables

I know i am not much clear, but i hope you guys can understand. If not please let me know i will try to explain it more. Please clear my doubts.

Thanks & Regards,


How to take the scripts of the changed SPs/Views/Temp tables/Functions automatically?



  We have a huge Database with more than 100 tables , more than 150 SPs.We are 10 members and we will be creating/deleting/updating SPs , functions, views, tem tables  everyday. Actually at the end of the day, we have to send the  scripts of latest SPs,functions,views,Temp tables to the server. So far what we are doing is  we manually go to the SQL server Scripts Wizard and selecting one by one all the SPs,functions,TTables and views. Is there any quicker way to take the changed stuffs instead going manually?

PK to PK relationship between Fact and Dimension tables


Hi guys,

Is it possible to define a relationship between a Fact Table and Dimension Table using a PK to PK relationship rather than a PK FK relationship?

The reason I ask is that I'm working on building a cube, in which the tables I think I need to use as dimension tables only have matching Primary Keys in the current RDBMS, not a FK.

Is there any reason why I shouldn't use a PK to PK relationship?


Union all in SSIS join two tables row wise or column wise

Union all in SSIS join two tables row wise or column wise

Selecting data from multiple tables whilst using a union statement.



any help on this would be great.

I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different  database that contains information regarding the group of the data.

I so far have the following:

SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx

I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.

Thanks again,


How to create buckets on measures from 2 fact tables, i.e variance between actual and budget coming


1. to put the gist of the problem, i am trying to make an expense analysis report that compares my actual with its budget.

2. actual is coming from 1 fact table e.g. fact_general_ledger (grain = voucher number) and budget is coming from another fact table (grain = account [much higher than voucher number])

3. i have to calculate the variance (actual minus budget) at an account grain and make a percentage measure. hence i will get data like

Account     Var%

a1             -115%

a2              -5%

a3              7%

a4              20%

a5              22%

a6              35%

a7              45%

a8              57%

a9              95%

a10      &

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Combine, minify and compress JavaScript files to load ASP.NET pages faster

Websites are getting more interactive these days and most of this interactivity comes from JavaScript. People are using different JavaScript libraries and frameworks to make their websites more interactive and user friendly.

Temporary Tables - MS SQL Server

Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

scripts file not found on server - 404


Fiddler is showing a 404 error when my published web application runs. the jquery.js file in
/scripts is highlighted in red by fiddler as not found. ( IIS log also shows the 404 error ).
I run the web app from visual studio, on local host - no problems. The publish is successful.
All the files are in the scripts folder of the web app on the server.

In the site.master I have:
<script src="../../Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>

Visual studio does not complain about this. ( which indicates the file is found. )

Oddly, when I fully qualify the path, VS says file not found, but after I publish and run, fiddler
shows the jquery file being found and sent to the browser.
<script src="/MvcApplication1/Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>

when the <script> path is "../../Scripts/jquery-1.4.1.min.js", the fiddler error is
404: /Scripts/jquery-1.4.1.min.js .

when the <script> path is "/MvcApplication1/Scripts/jquery-1.4.1.min.js", fiddler shows the file
being sent to the browser from that /MvcApplication1/Scripts path.

Both the Scripts fol

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend