Dimwit MDX count problem

I have run into a small problem. I have a dimension that identifies certain types of bills. I also have a measure that reports how many bills have been issued. There are several types of bills. But 1 of these is an adjustment bill. This is like a correction to an existing bill. These are represented as "A" bills in the DB

I need to count the number of unique bills, so that's a count of all the bills other than "A" bills.

I've looked at calculated members but am struggling with MDX syntax.

Anyone have any pointers or good articles I should read?

It should be something like this:`

with member [Measures].[Num]

as 'sum(except([Bills].[Level Name].members, [Bills].[ A ]), [Measures].[NumBills])'

Hope this helps,

Santi

Different Types Query

hi ALL,
i want a different types query for practice the sql server.i am just start learning a sql server..so suggest to me a some websites where the query is available..
thnax in advancedHi, try these sites will be the best guide......
http://www.w3schools.com/sql/sql_intro.asp

http://www.sqlcourse.com/

http://sqlzoo.net/

if possible read oracle sql book which will be easy as given with examples...

regards,
vishwanath

Different row delimiters in same connection manager

I have a situation where two CSV dmited types are read in. One file row type could be CRLF while another could be CR. Seeing how the only difference between these file types is the row delimiter, I would like to use one conn manager. Is it possible? Ideas?

Try this:
create a string variable Delimiter.
In Connection Manager window select your csv connection and look at its properties.
Open Expressions form and add an expression to the property RowDelimiter setting it to the variable User::Delimiter
Then you should be able to progamatically switch from one delimiter to another (toggling the variable value) before starting to load from the csv.
(i cannot try now this solution, take is as a hint)
|||

Unfortunately, this solution will not work. The RowDelimiter property is used only initially when no flat file connection columns are defined. After the columns get created the actual row delimiter is a column delimiter of the last column. You cannot do the same trick with the column delimiter because it is not expressionable.

Are you only concerned about the redundancy (with the two connection managers), or you had in mind more elegant data flow (with a multi flat file connection and one flat file source in a loop, perhaps)? I would like to find out more about your motivation to merge those two connection managers.

Thanks,

|||I am looking mainly to cut down the redundancy. There are 100+ inbound conns that need to support CRLF and CR returns, and it would end up being way too redundant to create a seperate manager for each return.|||

I can settle with just looking for CR for row delimiter. So, is there a way to maybe pad (or ignore) any LF's?

|||You can try that. The additional LF character would wrap up to the start of the first column of the next row. You should be able to clean it up using the Derived Column transform. However, I suspect it will make a headache with the incomplete last row, which would consist of a single LF character.

The solution I would go with is to have a Script Task before the Data Flow and preprocess files in it -- to unify the row delimiters.

Thanks,|||

Yah, I thought about doing this, however, won't this drain performance by having to essentially read/write the file twice (keeping in mind that some of these files can be rather large in size)? How would you handle the pre-processing in script?

Thanks.

|||

It will certainly have some performance impact. You should probably do some tests to verify how acceptable the cost is for the added manageability of your packages.

In the script, I would look for CR character and if the next one is not LF I would insert it. If you can be sure about the files with expected initial format ({CR}{LF}), you can skip those.

Thanks,

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.

Different data types

I have successfully made a couple of SSIS packages to read data from a legacy AS400 to SQL Server 2005. These are working fine. I am now working on one that is trying to pull some financial data and I keep getting the following error for the decimal columns:

"The component ciew is unavailable. Make sure the component view has been created. The input column "input column AMT1 (2870) cannot be mapped to "external metadata column AMT1(2852) because they have different data types."

Now I made sure that the data sitting on the AS400 is of type decimal and the SQL server column is the same. What else do I need to look for?

Thanks for the information.

Go to the advanced editor of the source component and compare the datatypes of the input and output column...that may give you a clue. Also remeber that there is a data conversion transform available in data flow.|||

how did you do that, may I ask?

I suggest you change the name of the "output column" in the

datasource this is done by

1. right clicking the datasource

2. show advance editor

3. click input or output properties

4. expand the datareader output if its a datareader

5. click on output columns.

6. look for the columns you want to change and click it,

7. Add an "x" in the column "name" property say "xuser_id" for the orginal column "user_id"

8. click ok

9. check the mappings column mapping by following steps 1 and 2

10 add a derived column transform.

12. transform the column to the desired format

13. in the derived column transform make sure that the "derived Column" property is set to "new column"

and in the derived column name remove the "x" we add in step no. 7 of course you need to set the right format

wheewww. this is long... hahaha

Different behavor with image and varbinary(max)?

Hello,
I am having problem with the different behavior of image and varbinary(max).
I thought these two types are compatible, but seeing some different behavior.

> Cannot insert literal value into "varbinary(max)", but we can with "image".
> "varbinary(max)" and "xml" data type seems to be compatible, but "image" is not.
Can someone tell me if this is expected behavior?
1. If I create table using "image" data type, I can insert string data into
the table.
create table kmimage(c1 image);
insert into kmimage values ('<value> my blob c1 a </value>');
select * from kmimage;
c1
0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
2. If I create table using varbinary(max), I cannot insert string data.
create table kmvarbin(c1 varbinary(max));
insert into kmvarbin values ('<value> my blob c1 a </value>');
==> Implicit conversion from data type varchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query.
No problem using hexadecimal representation.
insert into kmvarbin values
(0x3C76616C75653E206D7920626C6F622063312061203C2F7 6616C75653E);
select * from kmvarbin;
c1
0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
Thank you very much for your help.
KM
> Can someone tell me if this is expected behavior?
This is documented in the SQL 2005 Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm).
Implicit conversion from varchar to image is allowed. Conversion from
varchar to varbinary must be explicit.
Why are you storing character data in a column defined as binary?
Hope this helps.
Dan Guzman
SQL Server MVP
"KM" <KM@.discussions.microsoft.com> wrote in message
news:17377F49-0BBD-4895-8FA6-173FD4214C38@.microsoft.com...
> Hello,
> I am having problem with the different behavior of image and
> varbinary(max).
> I thought these two types are compatible, but seeing some different
> behavior.
>
> Can someone tell me if this is expected behavior?
> 1. If I create table using "image" data type, I can insert string data
> into
> the table.
> create table kmimage(c1 image);
> insert into kmimage values ('<value> my blob c1 a </value>');
> select * from kmimage;
> c1
> --
> 0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
> 2. If I create table using varbinary(max), I cannot insert string data.
> create table kmvarbin(c1 varbinary(max));
> insert into kmvarbin values ('<value> my blob c1 a </value>');
> ==> Implicit conversion from data type varchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query.
> No problem using hexadecimal representation.
> insert into kmvarbin values
> (0x3C76616C75653E206D7920626C6F622063312061203C2F7 6616C75653E);
> select * from kmvarbin;
> c1
> --
> 0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
> Thank you very much for your help.
> KM
>

Different behavor with image and varbinary(max)?

Hello,
I am having problem with the different behavior of image and varbinary(max).
I thought these two types are compatible, but seeing some different behavior
.
[vbcol=seagreen]
> Cannot insert literal value into "varbinary(max)", but we can with "image"
.
> "varbinary(max)" and "xml" data type seems to be compatible, but "image" is not.[/
vbcol]
Can someone tell me if this is expected behavior?
1. If I create table using "image" data type, I can insert string data into
the table.
create table kmimage(c1 image);
insert into kmimage values ('<value> my blob c1 a </value>');
select * from kmimage;
c1
--
0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
2. If I create table using varbinary(max), I cannot insert string data.
create table kmvarbin(c1 varbinary(max));
insert into kmvarbin values ('<value> my blob c1 a </value>');
==> Implicit conversion from data type varchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query.
No problem using hexadecimal representation.
insert into kmvarbin values
(0x3C76616C75653E206D7920626C6F622063312
061203C2F76616C75653E);
select * from kmvarbin;
c1
--
0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
Thank you very much for your help.
KM> Can someone tell me if this is expected behavior?
This is documented in the SQL 2005 Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5
-6f5a22343ea8.htm).
Implicit conversion from varchar to image is allowed. Conversion from
varchar to varbinary must be explicit.
Why are you storing character data in a column defined as binary?
Hope this helps.
Dan Guzman
SQL Server MVP
"KM" <KM@.discussions.microsoft.com> wrote in message
news:17377F49-0BBD-4895-8FA6-173FD4214C38@.microsoft.com...
> Hello,
> I am having problem with the different behavior of image and
> varbinary(max).
> I thought these two types are compatible, but seeing some different
> behavior.
>
> Can someone tell me if this is expected behavior?
> 1. If I create table using "image" data type, I can insert string data
> into
> the table.
> create table kmimage(c1 image);
> insert into kmimage values ('<value> my blob c1 a </value>');
> select * from kmimage;
> c1
> --
> 0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
> 2. If I create table using varbinary(max), I cannot insert string data.
> create table kmvarbin(c1 varbinary(max));
> insert into kmvarbin values ('<value> my blob c1 a </value>');
> ==> Implicit conversion from data type varchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query.
> No problem using hexadecimal representation.
> insert into kmvarbin values
> (0x3C76616C75653E206D7920626C6F622063312
061203C2F76616C75653E);
> select * from kmvarbin;
> c1
> --
> 0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
> Thank you very much for your help.
> KM
>

Differences between subscription types - help needed

I'm attempting to learn about Data Driven Subscriptions via Books
Online, particularly this tutorial:
http://msdn2.microsoft.com/en-us/library/ms169673.aspx, with no luck
thus far.
I have created essentially the same subscription as a non-data driven
one, and this successfully creates the file (rather than the email
mentioned in the tutorial). What could cause a data-driven subscription
not to work when an almost identical regular subscription does work? I
have tried both methods multiple times, with the same results.
Thanks in advance.This problem has been resolved. The local copy of that tutorial is
outdated, with the new version online. It makes values for a few fields
more explicit, and explores writing to a fileshare rather than to
email.