How to treat two different subcategories as one absolutely?

As the subject suggests , I encountered that problem when I want to treat two different subcategories as one absolutely!

Every product belongs to one subcategory , and every subcategory belongs to one category.Now in the category X there are two subcategorys : A and B,and I want to treat the merging of A and B as the new subcategory 'AB' logically absolutely , which means that , for example , the products belonging to A and B now belong to 'AB' , which meas the function 'descendants' works fine as before , but the total sales of all subcategories are right(so does "currentmember") when I choose subcategory.AllMembers on some axis.

Any reply are appreciated.

3X.

If you want to do it dynamically on the client, then it isn't easy. I beleive the closest option is to use session cubes with grouping level. Excel is one example of client tool which uses this technique for custom grouping. Below is the sample statement captured through Profiler with Excel 2007 going against AS2005 on Adventure Works cube, where I grouped together two product subcategories - Bike Racks and Bike Stands:

CREATE SESSION CUBE [Adventure Works_XL_GROUPING0] FROM [Adventure Works] ( MEASURE [Adventure Works].[Internet Sales Amount],MEASURE [Adventure Works].[Internet Order Quantity],MEASURE [Adventure Works].[Internet Extended Amount],MEASURE [Adventure Works].[Internet Tax Amount],MEASURE [Adventure Works].[Internet Freight Cost],MEASURE [Adventure Works].[Internet Total Product Cost],MEASURE [Adventure Works].[Internet Standard Product Cost],MEASURE [Adventure Works].[Internet Order Count],MEASURE [Adventure Works].[Customer Count],MEASURE [Adventure Works].[Reseller Sales Amount],MEASURE [Adventure Works].[Reseller Order Quantity],MEASURE [Adventure Works].[Reseller Extended Amount],MEASURE [Adventure Works].[Reseller Tax Amount],MEASURE [Adventure Works].[Reseller Freight Cost],MEASURE [Adventure Works].[Discount Amount],MEASURE [Adventure Works].[Reseller Total Product Cost],MEASURE [Adventure Works].[Reseller Standard Product Cost],MEASURE [Adventure Works].[Reseller Order Count],MEASURE [Adventure Works].[Order Quantity],MEASURE [Adventure Works].[Extended Amount],MEASURE [Adventure Works].[Standard Product Cost],MEASURE [Adventure Works].[Total Product Cost],MEASURE [Adventure Works].[Sales Amount],MEASURE [Adventure Works].[Tax Amount],MEASURE [Adventure Works].[Freight Cost],MEASURE [Adventure Works].[Order Count],MEASURE [Adventure Works].[Sales Amount Quota],MEASURE [Adventure Works].[Amount],MEASURE [Adventure Works].[Average Rate],MEASURE [Adventure Works].[End of Day Rate],MEASURE [Adventure Works].[Internet Sales Amount],MEASURE [Adventure Works].[Internet Unit Price] HIDDEN,MEASURE [Adventure Works].[Internet Transaction Count] HIDDEN,MEASURE [Adventure Works].[Sales Reason Count] HIDDEN,MEASURE [Adventure Works].[Reseller Unit Price] HIDDEN,MEASURE [Adventure Works].[Unit Price Discount Percent] HIDDEN,MEASURE [Adventure Works].[Reseller Transaction Count] HIDDEN,MEASURE [Adventure Works].[Unit Price] HIDDEN,MEASURE [Adventure Works].[Transaction Count] HIDDEN,DIMENSION [Adventure Works].[Account].[Account],DIMENSION [Adventure Works].[Account].[Account Number],DIMENSION [Adventure Works].[Account].[Account Type],DIMENSION [Adventure Works].[Account].[Accounts],DIMENSION [Adventure Works].[Customer].[Address],DIMENSION [Adventure Works].[Customer].[City],DIMENSION [Adventure Works].[Customer].[Commute Distance],DIMENSION [Adventure Works].[Customer].[Country],DIMENSION [Adventure Works].[Customer].[Customer],DIMENSION [Adventure Works].[Customer].[Customer Geography],DIMENSION [Adventure Works].[Customer].[Date of First Purchase],DIMENSION [Adventure Works].[Customer].[Education],DIMENSION [Adventure Works].[Customer].[Email Address],DIMENSION [Adventure Works].[Customer].[Gender],DIMENSION [Adventure Works].[Customer].[Home Owner],DIMENSION [Adventure Works].[Customer].[Marital Status],DIMENSION [Adventure Works].[Customer].[Number of Cars Owned],DIMENSION [Adventure Works].[Customer].[Number of Children At Home],DIMENSION [Adventure Works].[Customer].[Occupation],DIMENSION [Adventure Works].[Customer].[Phone],DIMENSION [Adventure Works].[Customer].[Postal Code],DIMENSION [Adventure Works].[Customer].[State-Province],DIMENSION [Adventure Works].[Customer].[Total Children],DIMENSION [Adventure Works].[Customer].[Yearly Income],DIMENSION [Adventure Works].[Date].[Calendar],DIMENSION [Adventure Works].[Date].[Calendar by Weeks],DIMENSION [Adventure Works].[Date].[Calendar Quarter of Year],DIMENSION [Adventure Works].[Date].[Calendar Semester of Year],DIMENSION [Adventure Works].[Date].[Calendar Week],DIMENSION [Adventure Works].[Date].[Calendar Year],DIMENSION [Adventure Works].[Date].[Date],DIMENSION [Adventure Works].[Date].[Day Name],DIMENSION [Adventure Works].[Date].[Day of Month],DIMENSION [Adventure Works].[Date].[Day of Week],DIMENSION [Adventure Works].[Date].[Day of Year],DIMENSION [Adventure Works].[Date].[Fiscal],DIMENSION [Adventure Works].[Date].[Fiscal Quarter of Year],DIMENSION [Adventure Works].[Date].[Fiscal Semester of Year],DIMENSION [Adventure Works].[Date].[Fiscal Year],DIMENSION [Adventure Works].[Date].[Month of Year],DIMENSION [Adventure Works].[Date].[Week of Year],DIMENSION [Adventure Works].[Date].[Year By Months],DIMENSION [Adventure Works].[Date].[Year By Quarters],DIMENSION [Adventure Works].[Date].[Year By Weeks],DIMENSION [Adventure Works].[Delivery Date].[Calendar],DIMENSION [Adventure Works].[Delivery Date].[Calendar by Weeks],DIMENSION [Adventure Works].[Delivery Date].[Calendar Quarter of Year],DIMENSION [Adventure Works].[Delivery Date].[Calendar Semester of Year],DIMENSION [Adventure Works].[Delivery Date].[Calendar Week],DIMENSION [Adventure Works].[Delivery Date].[Calendar Year],DIMENSION [Adventure Works].[Delivery Date].[Date],DIMENSION [Adventure Works].[Delivery Date].[Day Name],DIMENSION [Adventure Works].[Delivery Date].[Day of Month],DIMENSION [Adventure Works].[Delivery Date].[Day of Week],DIMENSION [Adventure Works].[Delivery Date].[Day of Year],DIMENSION [Adventure Works].[Delivery Date].[Fiscal],DIMENSION [Adventure Works].[Delivery Date].[Fiscal Quarter of Year],DIMENSION [Adventure Works].[Delivery Date].[Fiscal Semester of Year],DIMENSION [Adventure Works].[Delivery Date].[Fiscal Year],DIMENSION [Adventure Works].[Delivery Date].[Month of Year],DIMENSION [Adventure Works].[Delivery Date].[Week of Year],DIMENSION [Adventure Works].[Delivery Date].[Year By Months],DIMENSION [Adventure Works].[Delivery Date].[Year By Quarters],DIMENSION [Adventure Works].[Delivery Date].[Year By Weeks],DIMENSION [Adventure Works].[Department].[Departments],DIMENSION [Adventure Works].[Destination Currency].[Destination Currency],DIMENSION [Adventure Works].[Destination Currency].[Destination Currency Code],DIMENSION [Adventure Works].[Employee].[Base Rate],DIMENSION [Adventure Works].[Employee].[Birth Date],DIMENSION [Adventure Works].[Employee].[Department Name],DIMENSION [Adventure Works].[Employee].[Email Address],DIMENSION [Adventure Works].[Employee].[Emergency Contact Name],DIMENSION [Adventure Works].[Employee].[Emergency Contact Phone],DIMENSION [Adventure Works].[Employee].[Employee],DIMENSION [Adventure Works].[Employee].[Employee Department],DIMENSION [Adventure Works].[Employee].[Employees],DIMENSION [Adventure Works].[Employee].[End Date],DIMENSION [Adventure Works].[Employee].[Gender],DIMENSION [Adventure Works].[Employee].[Hire Date],DIMENSION [Adventure Works].[Employee].[Hire Year],DIMENSION [Adventure Works].[Employee].[Marital Status],DIMENSION [Adventure Works].[Employee].[Pay Frequency],DIMENSION [Adventure Works].[Employee].[Phone],DIMENSION [Adventure Works].[Employee].[Salaried Flag],DIMENSION [Adventure Works].[Employee].[Sales Person Flag],DIMENSION [Adventure Works].[Employee].[Sick Leave Hours],DIMENSION [Adventure Works].[Employee].[Start Date],DIMENSION [Adventure Works].[Employee].[Status],DIMENSION [Adventure Works].[Employee].[Title],DIMENSION [Adventure Works].[Employee].[Vacation Hours],DIMENSION [Adventure Works].[Geography].[City],DIMENSION [Adventure Works].[Geography].[Country],DIMENSION [Adventure Works].[Geography].[Geography],DIMENSION [Adventure Works].[Geography].[Postal Code],DIMENSION [Adventure Works].[Geography].[State-Province],DIMENSION [Adventure Works].[Internet Sales Order Details].[Carrier Tracking Number],DIMENSION [Adventure Works].[Internet Sales Order Details].[Customer PO Number],DIMENSION [Adventure Works].[Internet Sales Order Details].[Internet Sales Orders],DIMENSION [Adventure Works].[Internet Sales Order Details].[Sales Order Line],DIMENSION [Adventure Works].[Internet Sales Order Details].[Sales Order Number],DIMENSION [Adventure Works].[Organization].[Currency Code],DIMENSION [Adventure Works].[Organization].[Organizations],DIMENSION [Adventure Works].[Product].[Category],DIMENSION [Adventure Works].[Product].[Class],DIMENSION [Adventure Works].[Product].[Color],DIMENSION [Adventure Works].[Product].[Days to Manufacture],DIMENSION [Adventure Works].[Product].[Dealer Price],DIMENSION [Adventure Works].[Product].[End Date],DIMENSION [Adventure Works].[Product].[Large Photo],DIMENSION [Adventure Works].[Product].[List Price],DIMENSION [Adventure Works].[Product].[Manufacture Time],DIMENSION [Adventure Works].[Product].[Model Name],DIMENSION [Adventure Works].[Product].[Product],DIMENSION [Adventure Works].[Product].[Product Categories] HIDDEN AS _XL_GROUPING113,DIMENSION [Adventure Works].[Product].[Product Key],DIMENSION [Adventure Works].[Product].[Product Line],DIMENSION [Adventure Works].[Product].[Product Model Categories],DIMENSION [Adventure Works].[Product].[Product Model Lines],DIMENSION [Adventure Works].[Product].[Reorder Point],DIMENSION [Adventure Works].[Product].[Safety Stock Level],DIMENSION [Adventure Works].[Product].[Size],DIMENSION [Adventure Works].[Product].[Size Range],DIMENSION [Adventure Works].[Product].[Standard Cost],DIMENSION [Adventure Works].[Product].[Start Date],DIMENSION [Adventure Works].[Product].[Status],DIMENSION [Adventure Works].[Product].[Stock Level],DIMENSION [Adventure Works].[Product].[Style],DIMENSION [Adventure Works].[Product].[Subcategory],DIMENSION [Adventure Works].[Product].[Weight],DIMENSION [Adventure Works].[Promotion].[Discount Percent],DIMENSION [Adventure Works].[Promotion].[End Date],DIMENSION [Adventure Works].[Promotion].[Max Quantity],DIMENSION [Adventure Works].[Promotion].[Min Quantity],DIMENSION [Adventure Works].[Promotion].[Promotion],DIMENSION [Adventure Works].[Promotion].[Promotion Category],DIMENSION [Adventure Works].[Promotion].[Promotion Type],DIMENSION [Adventure Works].[Promotion].[Promotions],DIMENSION [Adventure Works].[Promotion].[Start Date],DIMENSION [Adventure Works].[Reseller].[Address],DIMENSION [Adventure Works].[Reseller].[Annual Revenue],DIMENSION [Adventure Works].[Reseller].[Annual Sales],DIMENSION [Adventure Works].[Reseller].[Bank Name],DIMENSION [Adventure Works].[Reseller].[Business Type],DIMENSION [Adventure Works].[Reseller].[First Order Year],DIMENSION [Adventure Works].[Reseller].[Last Order Year],DIMENSION [Adventure Works].[Reseller].[Min Payment Amount],DIMENSION [Adventure Works].[Reseller].[Min Payment Type],DIMENSION [Adventure Works].[Reseller].[Number of Employees],DIMENSION [Adventure Works].[Reseller].[Order Frequency],DIMENSION [Adventure Works].[Reseller].[Order Month],DIMENSION [Adventure Works].[Reseller].[Phone],DIMENSION [Adventure Works].[Reseller].[Product Line],DIMENSION [Adventure Works].[Reseller].[Reseller],DIMENSION [Adventure Works].[Reseller].[Reseller Bank],DIMENSION [Adventure Works].[Reseller].[Reseller Order Frequency],DIMENSION [Adventure Works].[Reseller].[Reseller Order Month],DIMENSION [Adventure Works].[Reseller].[Reseller Type],DIMENSION [Adventure Works].[Reseller].[Year Opened],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Carrier Tracking Number],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Customer PO Number],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Reseller Sales Orders],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Sales Order Line],DIMENSION [Adventure Works].[Reseller Sales Order Details].[Sales Order Number],DIMENSION [Adventure Works].[Sales Channel].[Sales Channel],DIMENSION [Adventure Works].[Sales Reason].[Sales Reason],DIMENSION [Adventure Works].[Sales Reason].[Sales Reason Type],DIMENSION [Adventure Works].[Sales Reason].[Sales Reasons],DIMENSION [Adventure Works].[Sales Summary Order Details].[Carrier Tracking Number],DIMENSION [Adventure Works].[Sales Summary Order Details].[Customer PO Number],DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Order Line],DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Order Number],DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Orders],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory Country],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory Group],DIMENSION [Adventure Works].[Sales Territory].[Sales Territory Region],DIMENSION [Adventure Works].[Scenario].[Scenario],DIMENSION [Adventure Works].[Ship Date].[Calendar],DIMENSION [Adventure Works].[Ship Date].[Calendar by Weeks],DIMENSION [Adventure Works].[Ship Date].[Calendar Quarter of Year],DIMENSION [Adventure Works].[Ship Date].[Calendar Semester of Year],DIMENSION [Adventure Works].[Ship Date].[Calendar Week],DIMENSION [Adventure Works].[Ship Date].[Calendar Year],DIMENSION [Adventure Works].[Ship Date].[Date],DIMENSION [Adventure Works].[Ship Date].[Day Name],DIMENSION [Adventure Works].[Ship Date].[Day of Month],DIMENSION [Adventure Works].[Ship Date].[Day of Week],DIMENSION [Adventure Works].[Ship Date].[Day of Year],DIMENSION [Adventure Works].[Ship Date].[Fiscal],DIMENSION [Adventure Works].[Ship Date].[Fiscal Quarter of Year],DIMENSION [Adventure Works].[Ship Date].[Fiscal Semester of Year],DIMENSION [Adventure Works].[Ship Date].[Fiscal Year],DIMENSION [Adventure Works].[Ship Date].[Month of Year],DIMENSION [Adventure Works].[Ship Date].[Week of Year],DIMENSION [Adventure Works].[Ship Date].[Year By Months],DIMENSION [Adventure Works].[Ship Date].[Year By Quarters],DIMENSION [Adventure Works].[Ship Date].[Year By Weeks],DIMENSION [Adventure Works].[Source Currency].[Source Currency],DIMENSION [Adventure Works].[Source Currency].[Source Currency Code],DIMENSION [Adventure Works].[Date].[Calendar Quarter] HIDDEN,DIMENSION [Adventure Works].[Date].[Calendar Semester] HIDDEN,DIMENSION [Adventure Works].[Date].[Fiscal Quarter] HIDDEN,DIMENSION [Adventure Works].[Date].[Fiscal Semester] HIDDEN,DIMENSION [Adventure Works].[Date].[Month Name] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Calendar Quarter] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Calendar Semester] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Fiscal Quarter] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Fiscal Semester] HIDDEN,DIMENSION [Adventure Works].[Delivery Date].[Month Name] HIDDEN,DIMENSION [Adventure Works].[Department].[Department] HIDDEN,DIMENSION [Adventure Works].[Employee].[Sales Territory Key] HIDDEN,DIMENSION [Adventure Works].[Geography].[Geography Key] HIDDEN,DIMENSION [Adventure Works].[Internet Sales Order Details].[Internet Sales Order] HIDDEN,DIMENSION [Adventure Works].[Organization].[Organization] HIDDEN,DIMENSION [Adventure Works].[Reseller].[Geography Key] HIDDEN,DIMENSION [Adventure Works].[Reseller Sales Order Details].[Reseller Sales Order] HIDDEN,DIMENSION [Adventure Works].[Sales Summary Order Details].[Sales Order] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Calendar Quarter] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Calendar Semester] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Fiscal Quarter] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Fiscal Semester] HIDDEN,DIMENSION [Adventure Works].[Ship Date].[Month Name] HIDDEN,DIMENSION [Product].[Product Categories] NOT_RELATED_TO_FACTS FROM _XL_GROUPING113 ( LEVEL [(All)],LEVEL [Category],LEVEL [Category1] GROUPING,LEVEL [Subcategory],LEVEL [Product],GROUP [Category1].[SubcategoryXl_Grp_1] ( MEMBER [Product].[Product Categories].[Subcategory].&[26],MEMBER [Product].[Product Categories].[Subcategory].&[27] ) ) )

After that, you can issue queries directly against the session cube, i.e.

SELECT NON EMPTY Hierarchize({DrilldownLevel(DrilldownMember({{DrilldownLevel({[Product].[Product Categories].[All Products]})}}, {[Product].[Product Categories].[Category].&[4]}),[Product].[Product Categories].[Category1])}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Product].[Product Categories].[Subcategory].[Category],[Product].[Product Categories].[Subcategory].[Category1] ON COLUMNS FROM [Adventure Works_XL_GROUPING0] WHERE ([Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

HTH,

Mosha (http://www.mosha.com/msolap)

how to treat truncations as a warning

I'm using SSIS to migrate data from one system to another. This is a usual extract, transform, cleanse and load type task.

The error handling is critical to get right. E.g. truncation of data on one column should stop that row being loaded but for other columns I might be happy to carry on loading the row but record a warning.

I'm finding the error disposition a bit limiting. I really feel the need for an 'Issue Warning' disposition which will act the same way as 'Ignore Error' in that the row continues being processed but will in addition copy a row to a warning output so that I can write a message to a log file for someone to manually investigate and correct that item of data post the conversion. Alternatively it would be useful to specify a severity (at a column level) when redirecting error output. This way I can put logic into a downstream component which would treat the error row differently depending on the severity of the error.

Am I missing a trick?

There's no built-in switch for enabling this behavior, but you can accomplish it with creative use of error redirection. I use error redirection or a conditional split to identify rows that either cause warnings or errors, flag them appropriately, then send the errors to a logging table. I send the warnings to a multicast that outputs the rows to both a logging table and to a Union All to put them back into the main flow.|||this will work for me, though it's a shame there isn't a built-in feature...|||

Nick Corrie wrote:

this will work for me, though it's a shame there isn't a built-in feature...

Nick,

That could be a good suggestion to make; you can post it at the connect site: http://connect.microsoft.com/VisualStudio/Feedback

How to treat Database Snapshots as if they were one database?

Hi,
I am considering various methods of creating a reporting database from
our production ERP system, in SQL Server 2005, and one of the solutions
suggested by the technical documentation is to create a database mirror
and a sequence of database snapshots. These snapshots, of course, have
different names.
This would be fine for client applications that we manage, in that we
could intercept client requests and connect them to the most recent
snapshots, and clear out the old snapshots as older connections finish.
However, we use applications such as Business Objects which point to a
named database. Does anyone have any suggestions about how we can get
such an application to always connect to the most up-to-date snapshot?
Thanks for any help in advance,
Rich"Rich B" <rjback@.hotmail.com> wrote in message
news:1138093573.261952.197210@.o13g2000cwo.googlegroups.com...
> Hi,
> I am considering various methods of creating a reporting database from
> our production ERP system, in SQL Server 2005, and one of the solutions
> suggested by the technical documentation is to create a database mirror
> and a sequence of database snapshots. These snapshots, of course, have
> different names.
> This would be fine for client applications that we manage, in that we
> could intercept client requests and connect them to the most recent
> snapshots, and clear out the old snapshots as older connections finish.
> However, we use applications such as Business Objects which point to a
> named database. Does anyone have any suggestions about how we can get
> such an application to always connect to the most up-to-date snapshot?
>
You can have a single database full of synonyms or views which point to the
most current snapshot. When you have a new snapshot you need to drop and
recreate all the synonyms or views. So you would need to ensure that client
applications don't hold schema locks (Sch-S) on the target objects for long
periods of time, preventing the switch. And you should recreate the objects
in a transaction so the client always gets a consitent view of the data.
David|||Thankyou, that seems like a reasonable approach.sql

how to tell the difference between upper and lower case?

Anyway to tell the difference BETWEEN uppercase and lowercase in SQL?
say youve two entries in a table that you wish to treat as different words?
"hello world"
and
"HELLO WORLD"
how would i construct a select statement that takes only the lowercase words??Select *
from table
where column = LOWER('VALUE');|||great,
that works
but now, how do you tell the difference between

"Dpi"

and

"dpi"

and

"dPI"

?|||Correct my If I'm wrong, but its seems like you are using MS SQL Server and your database is created with case-insensitive sort order.
(in most cases people have the opposite problem - how to treat mixed case as identical during search :).

You can re-create DB and use case-sensitive sort order. Also if i'm not mistaken SQL Server 2000 allows collation sequence on per column basis.|||Yes im using MS SQL Server 2000

Im gonna create a DB thats case sensitive and test that.

You can re-create DB and use case-sensitive sort order. Also if i'm not mistaken SQL Server 2000 allows collation sequence on per column basis.

whats this colation thingy!?!
im trying the help files, but it seems fairly complicated.|||Originally posted by MaxJam
great,
that works
but now, how do you tell the difference between

"Dpi"

and

"dpi"

and

"dPI"

?

--for Dpi
select * from table where column = initcap('dpi')
--for dpi
select * from table where column = lower('dpi')
--for dPI
select * from table where column != lower('dpi') and column != upper('dpi') and column != initcap('dpi') and lower(column) = lover('dpi')
--for DPI
select * from table where column = upper('dpi')

etc
etc