Showing posts with label similar. Show all posts
Showing posts with label similar. Show all posts

Monday, March 19, 2012

Joining summary data with detail data

I know similar questions have been asked but I wanted to try my luck that my issue is somehow different.

I am querying a database which has detail information (sales transactions) and is grouped by customer. I also have a table with one record for each customer with some historical sales information (summary information). The requirements for the report are to have the sums of the sales for each customer along with the historical data for that customer in the same row in the table. I haven't found a way to do this using one dataset and from what I've read, the current version doesn't support joining multiple datasets over a grouping field (customer).. or at all.

Any one have ideas?

In an ideal world, you would think that you could use SSRS to create the summary information from the detail information. That being said, I have a similar situation where the summary data is not exactly the same information. My summary data is counts by month of eligible accounts. The detail is counts of various rewards by month. I end up having information that looks like

Jan Feb Mar Apr May Jun
Elig 10 12 10 11 8 15
Reward 1 2 1 3 4 1 0
Reward 2 3 3 2 3 2 3
Reward 3 4 1 2 1 0 4

The way I did this was to use a UNION query with constants into a matrix report.

SELECT 0 'Level', 'Elig' Reward, Month, count(*) amt from SummaryTable Group By 1,2,3
UNION
SELECT 1 'Level', Reward, Month, count(*) amt from DetailTable Group By 1,2,3
Order by 1,2,3;

This way the Eligible data (SUMMARY) was at the top and the detail followed.

R

Joining Question

Just a Quick Question..
I have a query that is similar to this
Select colA
, colB
, colC
, colB + colC as ColD
From myTable
Say I wanted to Join 'ColD' to 'myOtherTable' to a column called myOColA
Is that possible?
I'm not sure how to Reference ColD at all in a query..
I've having a problem searching the web for the correct terminology of what
ColD reprents.
www.krushradio.com - Internet Radio for the rest of usSelect *
from myOtherTable a INNER JOIN
(Select colA, ColD
, colB
, colC
, colB + colC as ColD
From myTable) b on a.ColumnFromMyOtherTable = bColD
"Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in message
news:57B9E2D7-0E42-4F99-930C-E702E06F688C@.microsoft.com...
> Just a Quick Question..
> I have a query that is similar to this
> Select colA
> , colB
> , colC
> , colB + colC as ColD
> From myTable
> Say I wanted to Join 'ColD' to 'myOtherTable' to a column called myOColA
> Is that possible?
> I'm not sure how to Reference ColD at all in a query..
> I've having a problem searching the web for the correct terminology of
> what
> ColD reprents.
> --
> www.krushradio.com - Internet Radio for the rest of us|||Select b.colA
, b.colB
, b.colC
, b.colB + b.colC as myColD
, a.ColD
From myTable b
inner join myOtherTable a
on a.ColD = b.colB + b.colC
"Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in message
news:57B9E2D7-0E42-4F99-930C-E702E06F688C@.microsoft.com...
> Just a Quick Question..
> I have a query that is similar to this
> Select colA
> , colB
> , colC
> , colB + colC as ColD
> From myTable
> Say I wanted to Join 'ColD' to 'myOtherTable' to a column called myOColA
> Is that possible?
> I'm not sure how to Reference ColD at all in a query..
> I've having a problem searching the web for the correct terminology of
what
> ColD reprents.
> --
> www.krushradio.com - Internet Radio for the rest of us|||Jim,
The one I wanted to join was (taken from your example) 'myColD' on a
different table.
--
www.krushradio.com - Internet Radio for the rest of us
"Jim Underwood" wrote:

> Select b.colA
> , b.colB
> , b.colC
> , b.colB + b.colC as myColD
> , a.ColD
> From myTable b
> inner join myOtherTable a
> on a.ColD = b.colB + b.colC
> "Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in messag
e
> news:57B9E2D7-0E42-4F99-930C-E702E06F688C@.microsoft.com...
> what
>
>|||MyColID is in the other table (myOtherTable)
ColB and ColC are in the first table (myTable )
"Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in message
news:6C18C431-032C-4761-9B08-607797BF0B05@.microsoft.com...
> Jim,
> The one I wanted to join was (taken from your example) 'myColD' on a
> different table.
> --
> www.krushradio.com - Internet Radio for the rest of us
>
> "Jim Underwood" wrote:
>
message
myOColA

Monday, March 12, 2012

Joining cubes

I have three cubes in AS. How can I have a separate cube which can retrieve
data from these three cubes and then (similar to join function in T-SQL)
can join the results?
Thanks,
Gilgamesh
there is no join option for cubes.
but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
when you link 1 cube in another, the user is able to access the measures of
the 2 cubes at the same time.
you have to use shared/common dimensions to be able to analyze the measures
of the 2 cubes at the same time. (like 1 common time dimension)
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I have three cubes in AS. How can I have a separate cube which can retrieve
> data from these three cubes and then (similar to join function in T-SQL)
> can join the results?
> Thanks,
> Gilgamesh
>
|||How do you share dimensions? I tried nameing them the same in all thecubes,
but when I drag and drop the dimensions to the browser it creates separate
columns even though they're called with the same name.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...[vbcol=seagreen]
> there is no join option for cubes.
> but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
> when you link 1 cube in another, the user is able to access the measures
> of the 2 cubes at the same time.
> you have to use shared/common dimensions to be able to analyze the
> measures of the 2 cubes at the same time. (like 1 common time dimension)
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
|||do you use AS2000 or 2005?
is AS2000 shared dimensions are created in the shared dimension tree of the
management console.
use the SAME dimension in both cube to be able to analyze the measures of
the 2 cubes at the same time.
in 2005 all the dimensions are shared by default, so use the same dimension
in both cubes.
having the same name is not enough.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
> How do you share dimensions? I tried nameing them the same in all
> thecubes, but when I drag and drop the dimensions to the browser it
> creates separate columns even though they're called with the same name.
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...
>
|||Ok. I'm getting very close. How can I combine three fact tables into one? In
other words, how can I JOIN them?
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...[vbcol=seagreen]
> do you use AS2000 or 2005?
> is AS2000 shared dimensions are created in the shared dimension tree of
> the management console.
> use the SAME dimension in both cube to be able to analyze the measures of
> the 2 cubes at the same time.
> in 2005 all the dimensions are shared by default, so use the same
> dimension in both cubes.
> having the same name is not enough.
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
|||well... joining 3 fact tables its different.
can you explain what are the 3 tables and the 3 associated cubes you have.
and the version of the tools (SQL2000 or 2005)
take a look at the foodmart sample or the adventure works sample to see how
different fact tables are combined into 1 cube.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
> Ok. I'm getting very close. How can I combine three fact tables into one?
> In other words, how can I JOIN them?
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...
>
|||I'm using AS2005. I started by creating three differentd data source views
and then created one cube for each view. i.e. one cube for Orders, one for
Invoices, and one for Receipts. That part works fine. Now I need to either
create a fourth cube, or maybe somethings else, whcih allows me to comine
the records of three fact tables into one view. I'll need specifcally six
columns of each fat tables, which have been named the same, to be included
in the new fourth cube.
The issue is not to combine multiple fact tables into one cube. The issue,
maybe, is to combine multiple fact tables into one fact table and then use
that table in a cube.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...[vbcol=seagreen]
> well... joining 3 fact tables its different.
> can you explain what are the 3 tables and the 3 associated cubes you have.
> and the version of the tools (SQL2000 or 2005)
> take a look at the foodmart sample or the adventure works sample to see
> how different fact tables are combined into 1 cube.
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
|||ok, now I see
first use only 1 DSV and add all the tales used in this unique DSV; add the
correct relationship between fact tables and dimension tables.
after, create only 1 cube and 3 measure groups each one linked to the 3
tables;
now you have 1 cube with the measures from the 3 source tables.
the cube wizard will create everything for you if the DSV contains enough
information (ie good links).
again, take a look at the adv. works sample to see how this works.
if your tables come from 3 different database, and if you are not familiar
with SSAS, try to copy your tables into the same database to simplify your
project.
or use the linked servers + views, but try to have only 1 source database
for your cubes.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:enT0ScoYHHA.4008@.TK2MSFTNGP05.phx.gbl...
> I'm using AS2005. I started by creating three differentd data source views
> and then created one cube for each view. i.e. one cube for Orders, one for
> Invoices, and one for Receipts. That part works fine. Now I need to either
> create a fourth cube, or maybe somethings else, whcih allows me to comine
> the records of three fact tables into one view. I'll need specifcally six
> columns of each fat tables, which have been named the same, to be included
> in the new fourth cube.
> The issue is not to combine multiple fact tables into one cube. The issue,
> maybe, is to combine multiple fact tables into one fact table and then use
> that table in a cube.
> -G
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...
>

Joining cubes

I have three cubes in AS. How can I have a separate cube which can retrieve
data from these three cubes and then (similar to join function in T-SQL)
can join the results?
Thanks,
Gilgameshthere is no join option for cubes.
but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
when you link 1 cube in another, the user is able to access the measures of
the 2 cubes at the same time.
you have to use shared/common dimensions to be able to analyze the measures
of the 2 cubes at the same time. (like 1 common time dimension)
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I have three cubes in AS. How can I have a separate cube which can retrieve
> data from these three cubes and then (similar to join function in T-SQL)
> can join the results?
> Thanks,
> Gilgamesh
>|||How do you share dimensions? I tried nameing them the same in all thecubes,
but when I drag and drop the dimensions to the browser it creates separate
columns even though they're called with the same name.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...[vbcol=seagreen]
> there is no join option for cubes.
> but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
> when you link 1 cube in another, the user is able to access the measures
> of the 2 cubes at the same time.
> you have to use shared/common dimensions to be able to analyze the
> measures of the 2 cubes at the same time. (like 1 common time dimension)
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...|||do you use AS2000 or 2005?
is AS2000 shared dimensions are created in the shared dimension tree of the
management console.
use the SAME dimension in both cube to be able to analyze the measures of
the 2 cubes at the same time.
in 2005 all the dimensions are shared by default, so use the same dimension
in both cubes.
having the same name is not enough.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
> How do you share dimensions? I tried nameing them the same in all
> thecubes, but when I drag and drop the dimensions to the browser it
> creates separate columns even though they're called with the same name.
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...
>|||Ok. I'm getting very close. How can I combine three fact tables into one? In
other words, how can I JOIN them?
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...[vbcol=seagreen]
> do you use AS2000 or 2005?
> is AS2000 shared dimensions are created in the shared dimension tree of
> the management console.
> use the SAME dimension in both cube to be able to analyze the measures of
> the 2 cubes at the same time.
> in 2005 all the dimensions are shared by default, so use the same
> dimension in both cubes.
> having the same name is not enough.
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...|||well... joining 3 fact tables its different.
can you explain what are the 3 tables and the 3 associated cubes you have.
and the version of the tools (SQL2000 or 2005)
take a look at the foodmart sample or the adventure works sample to see how
different fact tables are combined into 1 cube.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
> Ok. I'm getting very close. How can I combine three fact tables into one?
> In other words, how can I JOIN them?
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...
>|||I'm using AS2005. I started by creating three differentd data source views
and then created one cube for each view. i.e. one cube for Orders, one for
Invoices, and one for Receipts. That part works fine. Now I need to either
create a fourth cube, or maybe somethings else, whcih allows me to comine
the records of three fact tables into one view. I'll need specifcally six
columns of each fat tables, which have been named the same, to be included
in the new fourth cube.
The issue is not to combine multiple fact tables into one cube. The issue,
maybe, is to combine multiple fact tables into one fact table and then use
that table in a cube.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...[vbcol=seagreen]
> well... joining 3 fact tables its different.
> can you explain what are the 3 tables and the 3 associated cubes you have.
> and the version of the tools (SQL2000 or 2005)
> take a look at the foodmart sample or the adventure works sample to see
> how different fact tables are combined into 1 cube.
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...|||ok, now I see
first use only 1 DSV and add all the tales used in this unique DSV; add the
correct relationship between fact tables and dimension tables.
after, create only 1 cube and 3 measure groups each one linked to the 3
tables;
now you have 1 cube with the measures from the 3 source tables.
the cube wizard will create everything for you if the DSV contains enough
information (ie good links).
again, take a look at the adv. works sample to see how this works.
if your tables come from 3 different database, and if you are not familiar
with SSAS, try to copy your tables into the same database to simplify your
project.
or use the linked servers + views, but try to have only 1 source database
for your cubes.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:enT0ScoYHHA.4008@.TK2MSFTNGP05.phx.gbl...
> I'm using AS2005. I started by creating three differentd data source views
> and then created one cube for each view. i.e. one cube for Orders, one for
> Invoices, and one for Receipts. That part works fine. Now I need to either
> create a fourth cube, or maybe somethings else, whcih allows me to comine
> the records of three fact tables into one view. I'll need specifcally six
> columns of each fat tables, which have been named the same, to be included
> in the new fourth cube.
> The issue is not to combine multiple fact tables into one cube. The issue,
> maybe, is to combine multiple fact tables into one fact table and then use
> that table in a cube.
> -G
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...
>

Monday, February 20, 2012

Join on agragate function between tables

A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:

Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2
where dato > '2006-08-11'
Group By DatePart(dw,dato)

A typical result:

DOW NOR
3 8934
6 22397
7 23328
1 23401
4 1938
2 24399
5 1112

Trying to join two or more tables in all sorts of variants of this:

Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1],
datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2]
FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato)
Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11'
Group By datePart(dw,a1.dato), datePart(dw,a2.dato)

Here I get this as a typical result:

DOW1 Amount 1 DOW2 Amount 2
6 332953802 6 332953802
3 42248886 3 42248886
1 330281714 1 330281714
7 335759904 7 335759904
4 1232568 4 1232568
5 210168 5 210168
2 366985359 2 366985359

Where the numbers are way off.

Any suggestions?

When you Join the tables, based only on the day of the week, your resultset will have each record in Table1 for a day of the week combined with each record in Table2 for the same day of the week. That is why your numbers are so far off.

Here is a simple example that shows what's happening.

Drop Table Table1

Create Table Table1(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

Drop Table Table2

Create Table Table2(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

insert Table1 values( '1/1/2006', 'Fred' )

insert Table1 values( '1/2/2006', 'Barney' )

insert Table2 values( '1/1/2006', 'Wilma' )

insert Table2 values( '1/1/2006', 'Pebbles' )

insert Table2 values( '1/2/2006', 'Betty' )

insert Table2 values( '1/2/2006', 'BamBam' )

Select DatePart(dw, dato ), Count(*)

From Table1

Group

By DatePart(dw, dato )

Select DatePart(dw, dato ), Count(*)

From Table2

Group

By DatePart(dw, dato )

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

t1.valCol,

t2.valCol

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

valCol valCol

-- -- - -

1 1 Fred Wilma

1 1 Fred Pebbles

2 2 Barney Betty

2 2 Barney BamBam

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

Count(t1.dato),

Count(t2.dato)

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

Group

By DatePart(dw, t1.dato ),

DatePart(dw, t2.dato)

-- -- -- --
1 1 2 2
2 2 2 2
Select Coalesce( t1.dow, t2.dow ),

t1.howmany,

t2.howmany

From (

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table1

Group by DatePart(dw, dato )

) t1

Full Outer Join

(

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table2

Group by DatePart(dw, dato )

) t2

On t1.dow = t2.dow

howmany howmany
-- -- --
1 1 2
2 1 2

Select dayofweek,

(Select Count(*) from Table1 where DatePart(dw, dato ) = dayofweek ),

(Select Count(*) from Table2 where DatePart(dw, dato ) = dayofweek )

From (

Select 1 as dayofweek

Union All

Select 2 as dayofweek

Union All

Select 3 as dayofweek

Union All

Select 4 as dayofweek

Union All

Select 5 as dayofweek

Union All

Select 6 as dayofweek

Union All

Select 7 as dayofweek

)t1

dayofweek
-- -- --
1 1 2
2 1 2
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0

There are several ways to get the days of the week. If you know that one of the tables will have records for every day, you can use that. There is a system table that has lists of numbers, that is another good source.

|||

Excellent stuff. I allready guessed the reason for my results but no idea how to stop it.