Showing posts with label views. Show all posts
Showing posts with label views. Show all posts

Tuesday, March 27, 2012

Dimension view

Hello,
I want to make a dimension, where dimension members are either of type 1 or type 2, (to put it in a simple way). I have two views of a table, each containing plus minus 200 records, wich contains strings, wich can be used to check whether a dimension member is of type 1 or 2.
Now I can make a view as dimension table with the occurences labelled these dimension as being of type 1 or 2, but, since there is a huge amount of data being generated, and I want the dimensional database to be updated regularly using a scheduler, I fear that the performance of this update will be extremely slow, since that process needs to search through 400 records for each record to be processed.
I'd prefer to make a view wich filters the dimension members when these are already generated, so I can make MDX queries like: SELECT [Type 1] ON COLUMNS

FYI I use a CLR function (to be able to use regex - I create natural keys with it) in these views, so I really need a similar functionality.
This is my data source

view.
I want to be able to filter the dimension members

generated from the UserAgents (dimension table) by the all_keys table.|||

Well, this is the data source view.
I want to be able to filter the dimension members

generated from the UserAgents (dimension table) by the all_keys table.

|||I made myself a dimension table with a view using a SELECT DISTINCT on the UserAgents (so, kinda like a dimension), and then I made a dimension wich uses an inner join with this view and All_Keys table. I hope this won't give me performance issues...

Monday, March 19, 2012

differnce between 2 aggregate columns

Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
Please post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegr oups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegr oups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||If I cut and paste those statements, they don't work.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegr oups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:

>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP
|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?

differnce between 2 aggregate columns

Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:

>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?

differnce between 2 aggregate columns

Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
--
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?

Wednesday, March 7, 2012

Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.

Sunday, February 19, 2012

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.