Is this possible in MDX queries

Hi

The cube i have conatins the ProductName , Time as dim and Sales as measure. I need to query from the cube the product name and sales for multiple date ranges. As an example, The I/P shall be in the format

ProductName : A,B

DateRange : 1/1/2004:2/1/2004,1/1/2005:2/1/2005,and so on.

The number of date ranges is not fixed and also is the productname but the date ranges are common across both the Products. I have to query for the above I/P and use SSRS to get the O/P.

Thanking you in advance

regards

saishyam

I am not sure exactly what you are looking for, but here is an example using Adventure Works that list products with sales by total for different date ranges:

WITH

SET [Selected Products] AS

{[Product].[Product Categories].[Subcategory].[Mountain Bikes].Children}

SET [Date Range 2002] AS

{[Date].[Date].[January 1, 2002]:[Date].[Date].[February 1, 2002]}

MEMBER [Date].[Date].[Date Range 2002 Total] AS

Aggregate([Date Range 2002])

SET [Date Range 2003] AS

{[Date].[Date].[January 1, 2003]:[Date].[Date].[February 1, 2003]}

MEMBER [Date].[Date].[Date Range 2003 Total] AS

Aggregate([Date Range 2003])

SET [All Date Range Totals] AS

{[Date].[Date].[Date Range 2002 Total],

[Date].[Date].[Date Range 2003 Total]}

SELECT

{[All Date Range Totals]} ON COLUMNS,

NonEmpty({[Selected Products]},{[All Date Range Totals]}) ON ROWS

FROM

[Adventure Works]

WHERE

([Measures].[Internet Sales Amount])

You can substitute another measure for "Internet Sales Amount" by changing the WHERE clause and get the totals for a different measure.

HTH,

Steve

|||

Hi Steve

Thank you for the response.

The query that you have mentioned conatins the date ranges fixed, in my case the number of date ranges is not fixed. So how will i know , how many Sets i need to create? Is there any way to do that. I hope now my quesition is more clear.

regards

Saishyam

|||

You don't have to define the sets explicitly, you should be able to just do this:

{ { Date1member : Date2member}, { Date3member : Date4member } }

Alternatively, MDX has a function called Union.

sql

is this layout in analysis services possible?

Hi,

I have a cube which should show top 10 products and a percentage for that versus the overall performance of the product.

Can this be possible?

Amount Percentage

Product 1 1000 5%

Product 2 2000 10%

Product 3 1500 7.5%

.

.

.

Product 10 4000 20%

SubTotal for top 10 10000 50%

Total 20000

This is definitely possible, showing the sub total and grand total might be tricky in a pivot table, but it is not too hard with an MDX query.

Is this bad design?

Hey,

If you have a one to many relationship, say a product has many reviews, is it good design to have a flag in the product which indicates whether there are any review in the reviews table?

One might do this so that in a stored procedure one can check to see if there are any product reviews before running a select query on the reviews table. If there aren't then you've saved a select query.

Also, this flag could be used to determine the type of formating required for the data. I suppose it would be quite easy to just look up the count of reviews in the data table to gather the same info.

Cheers,

I.I hate designs like that. That sp is still going to have to query the product table to check the flag, so you haven't gained a thing. A simple Count Where fk=pk will tell you just as fast, or just do the query--you're going to want the records anyway.

The only way I could see this helping would be if ALL of the following conditions were met:
** This is an extremely active database that is being pushed very hard
** It is rare for there to be any reviews
** You already have the product table row

But what a headache to try to keep that flag updated. Maybe a trigger could be used -- add a record, increment the count, delete a record, decrement the count.|||It didn't feel right either.

I think another problem with it is that if a sp determines that there are reviews to gather and then a concurrent user goes and deletes all the reviews, the first sp will return no reviews so the formatting will be set up to show reviews but they'll be none.

"That sp is still going to have to query the product table to check the flag, so you haven't gained a thing."

If you had several flags for several tables then it might save a few selects. You could get all the flags in a single select.

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.comI'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
--
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.com
I'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.comI'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there any plan to add new techniques to improve the data mining part of SQL Server

I have worked with many data mining softwares and found SQL Data mining part of Microsoft product needs to imrove a lot to whoo the customers.

We continue to advance the data mining functionality in future versions of SQL Server. What aspects do you feel need improvement in order to "whoo" customers?

Thx

-Jamie

|||I think It'd be great if you guys were able to implement a little more in the way of the IDE telling you what was/wasn't possible... Such as not being able to create a lift chart for association models, and describing cases/nested tables for example. Or simply refer people to the MSDN documentation. Also, improving the UI, at least for the predictions tab would be nice. One thing I've noticed that annoys me is that when you hit the results button when writing DMX from the "SQL Page", it switches the button to automatically go to the design view, rather than the SQL page again. So everytime I must click on the small dropdown arrow and click "SQL" again...and again. Overall it's very functional and seems to be a big step up from the last version!|||

I will explain.

1.If you add more algorithms like logistic regressions or regression models, Machine learning (both supervised and unsupervised learnings), more advance decision trees algorithms(like CHAID, CART, QUEST and EXHAUSTIVE QUEST), more associative modelings like market basket analysis & sequential modeling, pricipal componenet analysis, it will be great and all algorithms are very good kind of decision making algorithms in respetive fields like Finance, Banking, market research, retail, telecom, CRM etc. For eg: If take risk modeling in Banking or finance domian, the logistic regression model plays an important role to score the default risk.

2. I have seen that all major players in Data mining like SAS or SPSS, they have features to connect with MS SQL SEVRVER and share the modeling features of later. Is there any feature vice versa?

3. Data mining plays a key role in modern CRM. If you have a better data mining features and you can have better CRM analytic software itself.

4. Text mining plays a key role in call center data analysis, Credit card analysis, telecom data modeling and even we can use this for better web mining. Why can not you try this kind of advanced features.

I think this explationation sufficient for your question to whoo customers. A better decision making algorithms and its proper implimentation in time and monitoring helps companies to save millions of dollors, in single year itself (eg: citi corp, DSP Merril lynch, Chase, Wal-Mart, Target (Even I was in the analytical team for some time), etc).

Thanks

Ajesh

|||

Thanks Ajesh - as Jamie says, we will continue to invest in improving the features of SQL Server data mining. It is great to get feedback from people on the forum or in mail that helps us to plan future versions. And, in fact, some of what you are asking for is already in the product - logistic regression, association rules (for market basket analysis) for example. Sharing of models is enabled between different tools mostly by PMML which SLQ Server, SPSS and SAS all support in our own ways.

Data mining does indeed play an important role in CRM. Microsoft Dynamics CRM has released a special "Analytics Foundation" which enables CRM users to integrate SQL Server Analysis Services (OLAP and Data Mining)

You can read more about the Analytics Foundation here:

http://www.microsoft.com/dynamics/crm/product/analyticsfoundation.mspx

And there is an interesting article here in destinationCRM about the role of data mining in the future of CRM and Microsoft's impact: http://www.destinationcrm.com/articles/default.asp?ArticleID=6833

Text mining is available in SQL Server through the SSIS text mining components. See the tutorial here: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/default.aspx

So, as you can see, we have made some very significant investments in these areas, and we fully expect to continue that momentum through many releases to come.

|||One can also add features to support Ontologies, that can be useful for semantic analysis. Also a standardised ontology representation could be maintained to enable any kind of APIs to access the database as and when required.|||

Hi

I really agree with Jamie and Donald but I have a suggession that if you can integrate data mining, text mining and webmining into one platform or one single module, this will be helpful for thousands of users.

Thanks

Visiting lecturer

Madras University-Chennai

India

Also Analyst

JDA Software India, India

|||

Thanks - that is certainly something we should consider for the future.

When you say "web mining" are there specific features and functions that you would like to see?

|||

Hi

I am suggesting the following 2 concepts where you can look into.

1. web mining for business analysis
2. web mining for technical analysis

Also I appreaciate special functional enhancements for applications like

1. Customer profiling
2. personalization
3. market segmentation
4. Target marketing
5. Cross-selling
6. Integration with CRM

Over all a "COMPLETE E-BUSINESS MARKETING SOLUTION" and "Implementaion methodology for solutions". This kind of solution already exist for DataStage, etc.

Thanks

Ajesh

Is there any plan to add new techniques to improve the data mining part of SQL Server

I have worked with many data mining softwares and found SQL Data mining part of Microsoft product needs to imrove a lot to whoo the customers.

We continue to advance the data mining functionality in future versions of SQL Server. What aspects do you feel need improvement in order to "whoo" customers?

Thx

-Jamie

|||I think It'd be great if you guys were able to implement a little more in the way of the IDE telling you what was/wasn't possible... Such as not being able to create a lift chart for association models, and describing cases/nested tables for example. Or simply refer people to the MSDN documentation. Also, improving the UI, at least for the predictions tab would be nice. One thing I've noticed that annoys me is that when you hit the results button when writing DMX from the "SQL Page", it switches the button to automatically go to the design view, rather than the SQL page again. So everytime I must click on the small dropdown arrow and click "SQL" again...and again. Overall it's very functional and seems to be a big step up from the last version!|||

I will explain.

1.If you add more algorithms like logistic regressions or regression models, Machine learning (both supervised and unsupervised learnings), more advance decision trees algorithms(like CHAID, CART, QUEST and EXHAUSTIVE QUEST), more associative modelings like market basket analysis & sequential modeling, pricipal componenet analysis, it will be great and all algorithms are very good kind of decision making algorithms in respetive fields like Finance, Banking, market research, retail, telecom, CRM etc. For eg: If take risk modeling in Banking or finance domian, the logistic regression model plays an important role to score the default risk.

2. I have seen that all major players in Data mining like SAS or SPSS, they have features to connect with MS SQL SEVRVER and share the modeling features of later. Is there any feature vice versa?

3. Data mining plays a key role in modern CRM. If you have a better data mining features and you can have better CRM analytic software itself.

4. Text mining plays a key role in call center data analysis, Credit card analysis, telecom data modeling and even we can use this for better web mining. Why can not you try this kind of advanced features.

I think this explationation sufficient for your question to whoo customers. A better decision making algorithms and its proper implimentation in time and monitoring helps companies to save millions of dollors, in single year itself (eg: citi corp, DSP Merril lynch, Chase, Wal-Mart, Target (Even I was in the analytical team for some time), etc).

Thanks

Ajesh

|||

Thanks Ajesh - as Jamie says, we will continue to invest in improving the features of SQL Server data mining. It is great to get feedback from people on the forum or in mail that helps us to plan future versions. And, in fact, some of what you are asking for is already in the product - logistic regression, association rules (for market basket analysis) for example. Sharing of models is enabled between different tools mostly by PMML which SLQ Server, SPSS and SAS all support in our own ways.

Data mining does indeed play an important role in CRM. Microsoft Dynamics CRM has released a special "Analytics Foundation" which enables CRM users to integrate SQL Server Analysis Services (OLAP and Data Mining)

You can read more about the Analytics Foundation here:

http://www.microsoft.com/dynamics/crm/product/analyticsfoundation.mspx

And there is an interesting article here in destinationCRM about the role of data mining in the future of CRM and Microsoft's impact: http://www.destinationcrm.com/articles/default.asp?ArticleID=6833

Text mining is available in SQL Server through the SSIS text mining components. See the tutorial here: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/default.aspx

So, as you can see, we have made some very significant investments in these areas, and we fully expect to continue that momentum through many releases to come.

|||One can also add features to support Ontologies, that can be useful for semantic analysis. Also a standardised ontology representation could be maintained to enable any kind of APIs to access the database as and when required.|||

Hi

I really agree with Jamie and Donald but I have a suggession that if you can integrate data mining, text mining and webmining into one platform or one single module, this will be helpful for thousands of users.

Thanks

Visiting lecturer

Madras University-Chennai

India

Also Analyst

JDA Software India, India

|||

Thanks - that is certainly something we should consider for the future.

When you say "web mining" are there specific features and functions that you would like to see?

|||

Hi

I am suggesting the following 2 concepts where you can look into.

1. web mining for business analysis
2. web mining for technical analysis

Also I appreaciate special functional enhancements for applications like

1. Customer profiling
2. personalization
3. market segmentation
4. Target marketing
5. Cross-selling
6. Integration with CRM

Over all a "COMPLETE E-BUSINESS MARKETING SOLUTION" and "Implementaion methodology for solutions". This kind of solution already exist for DataStage, etc.

Thanks

Ajesh