help on a business case related to status reporting

Hello,

I am a newbie in BI and would appreciate your help on the following case:

I have a customer dimension being a slowly changing dimension and I have fact Subscriptions storing the subscriptions of customers.

The customer dimension has

- CustKey

- CustAltKey (Being the Business Key of SCD)

- CustName

The subscription Fact has:

- CustKey

- Inception Date (linked to Time Dimension)

- Expiry Date (linked to Time Dimension)

- Subscription Amount (measure)

- Is Active (Named Calculation, True when subscription not expired yet)

1) I want to be able to report on Active v.s. Non Active Customers, where an active customer is someone who still has at least one subscription whose Expiry Date is > today's date. I was wondering if this can be done using a Named Calculation on the Customer Dimension, if yes how can I query from subscription fact in the named calculation expression? Or maybe it's the wrong way.

2) I also want to be able to categorize on this status of Customers all through the reports. Let's say I want to be able to find the number of customers I lost this year v.s. the number of customers I lost last year. I think here an MDX would be required. But I can't see how exactly to get the status according to the input years. Appreciate any guidance on this.

Thanks,

Grace

Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:16 AM

Budget and ForeCast

Hi All,

My tough cases are like that in my virtual cube. I have

Fin_time, Bugdet_Version Dimension and Sales_Amount measure.

Fin_time has year, quarter and month dimensions.

I have 14 Budget_version , one is actual data, the other 13 are budget data. All 14 budget_version have a timeID property, like (200404, 200503, YYYYMM format).

Now I need to get a sales_Amount, when I choose a budget_version, the number of sales_Amount before the timeid( in budget_version member property) is actual number, and after timeid is budget data.

Thanks

David X

Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:43 AM

Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

Hello all,

Does anyone have any idea how to access a cube from stored procedures in SQL Server 2005?

My idea was to use SQLCLR and write a function in .NET that accessed the cube through ADOMD, but there are problems with that.

See the following code sample:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MDXRdr()
{
    AdomdConnection conn = new AdomdConnection();
    conn.ConnectionString = @"Provider=SQLNCLI.1;Data Source=JOAHSE0\SQL2005;Integrated Security=SSPI;Initial Catalog=dbRenTAK";

    conn.Open();
    // Just output cube name
    string str = conn.Cubes[0].Name;
    conn.Close();

    return new SqlString(str);
}

};

First, I wasn't able to add a reference to AdomdClient from Visual Studio. I then did add a reference manually in the project file and it compiles. But when I try to deploy, I get an error message that the "assembly adomdclient was not found in the SQL catalog".

Thanks in advance for any suggestions or hints!

Best Regards,

Johan Åhlén
Consultant, IFS

Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:43 AM

Reply:

Moving to the "Data Mining" Forum, which is better suited for this question.

------------------------------------

Using Synonyms in query editor bug/feature

I am fairly new to SQL server but I have a lot of Access experience and so far I have been impressed with SQL Server Express (I am soft and I am using the management studio) How ever I think I have found a feature in the express management studio that may be a bug

I have created a linked server to an Acces database which has been supplied by a client of ours using the following code

EXEC sp_addlinkedserver

@server = N'KEY',

@srvproduct = N'Access',

@provider = N'Microsoft.Jet.OLEDB.4.0',

@datasrc = N'C:\Data\DB.mdb'

First comment this work but in the linked server there is no list of objects in the access data base (or any other linked server I have try) this seems to me to be a bug/very annoying feature.

I can now do queries from the database table of the form

SELECT TOP (10) ID FROM KEY...Tbl_1

However if I try to use the query editor the tables in the linked server aren't listed, this I would say is not a bug just a fact.

However when I create a synonym of a table in the table in the linked server using

CREATE SYNONYM Syn_1 FOR KEY...Tbl_1

The synonym is created and can be used in queries as

SELECT TOP (10) ID FROM Tbl_1

The Synonym does not appear in the synonyms list in the query editor add tables list. I think that is a bug since surely thats what the synonyms list is for.

The use of a view would get round this I know but that doesn't seem as elegant as the synonyms method, this feature isn't going to stop me in my work since I can get round it.

Viewing the edited and original measure values in a write enabled cube

Hi all,

I have a write enabled SSAS cube.

I edited the values of a measure column.

Now when i am querying the cube i am seeing only the changed values

I like to see the original values of the measure column and the changed values simultaneously in a client application like excel pivot table

Is any special MDX query should be used to see the actual cube data

Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:17 AM

All Level Name - How do I change for each attribute?

It looks like if I set the AttributeAllMemberName property at the dimension level, all attributes will inherit this name.

If I have multiple attributes within a dimension, how do I change the 'All' level to read 'All <attribute name>' for each attribute?

Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:17 AM

Programmatically Add Logging to Package

This is a sample for adding logging to a package programmatically.

It's a simple process, but it took some time to find all the pieces required so I wanted to share it for others who can use this in the future to save them the time I spent it.

_Package.LoggingMode = DTSLoggingMode.Enabled

        Dim Log As LogProvider = _Package.LogProviders.Add("DTS.LogProviderEventLog.1")
        Log.Name = "Windows Event Log Logging Provider"
        Log.Description = "Logs Event info to Windows Event Log"
        _Package.LoggingOptions.SelectedLogProviders.Add(Log)

Dim LoggingOptions As LoggingOptions = _Package.LoggingOptions
LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion

Dim Events As String() = {"OnError", "OnTaskFailed", "OnWarning"}
LoggingOptions.EventFilter = Events

        Dim EventColumnFilter As DTSEventColumnFilter = New DTSEventColumnFilter()
        EventColumnFilter.Computer = True
        EventColumnFilter.Operator = True
        EventColumnFilter.SourceName = True
        EventColumnFilter.SourceID = True
        EventColumnFilter.ExecutionID = True
        EventColumnFilter.MessageText = True
        EventColumnFilter.DataBytes = False

LoggingOptions.SetColumnFilter("OnError", EventColumnFilter)

specified sort order in matrix column

i had a column that had data that needed to be sorted in a specified order. (Crystal reports has a specified order choice for sort order)

i created a calculated field (on the data tab, then its available to choice from) for the column that i needed to specify the sort order. if the data was less than and greater than i assigned numbers to it. for example

1-5 assigned 1, 6-15 assigned 2, 16-30 assigned 3, etc.

then i went to the matrix column (edit group...) and used an expression

iif(calculated column = 1, "1-5", iif(calculated column = 2, "6-15", etc...))

i also added the same expression to the column.

this provided me with my specified sort order that i needed.

good luck, hope this helps you out.

chuck

Opening .sql text files and defaulting to the current connection.

In Sql2K's Query Analyzer, if I have an active query I can open an existing text file without having to specify a Server/User/Password.

In SQL Management Studio I can start a new query (CTRL-N) without having to choose a connection, but if I try opening a file I need to specify a Server/User/Password.

I know I can choose 'Remember Password' -- but it seems like a convenience was taken away.

How to avoid concurrent editing of cube by write back

I have a write enabled cube

Multiple users use it with a client application

But when 2 users try to edit the same cell simultaneously, is it possible to notify one user and allow the other user to edit it.

How can we handle such situations

Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:17 AM

Periodical appearence of the data in the cube

Hello!

I have a problem with my cube. The deal is that I have a table that includes, sum of money that person has to pay, the startdate of the period and the period length in month:

Money StartPeriod Length

=======================

50 2006-03-01 3

70 2006-05-21 1

..... ........ ...

35 2006-12-12 12

What I need is to show it in my cube so if the Length is 3 then this 50$ will appear in the cube every three month, e.g. the next time it appears is 2006-06-01, then 2006-09-01, then 2006-12-01.

Can anyone help me with this? Thank you somuch for any hints!

/NatashaKarlsson

Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:47 AM

SQL Server 2005 SP1 Webchat: March 22nd 9:00 am PST

We are be hosting a public web chat discussion on March 22^nd 9:00 – 10:00 am PST about the March CTP of SP1. It is the customers' opportunity to engage with the dev team and provide feedback and ask questions. Strong participation from customers will act as a motivator for stronger participation by the dev team and will increase their interest in having these in the future, so we urge you to join and participate.

For more information and sign up for this web chat go to: http://www.microsoft.com/technet/community/chats/default.mspx#06_0322_TN_SQL.

Reply:

Would a transcript be available later on for us to review?

Thanks...

------------------------------------

Problems with writeback and nonemptycrossjoin function in SSAS 2000

Hi,

I am trying to update SSAS 2000 cube data using our application, but I get errors when I attempt to retrieve data from cells affected by the write back Update Cube command. Our application uses ADOMD.NET 8.0 to retrieve cell sets from the cube. I only receive an error if the cube I'm writing back to contains MDX using the NONEMPTYCROSSJOIN function.

The following error is received in the ADOMDException when I execute a cell set query:

"Some data has been updated since you began executing this query. - An error
occurred during attempt to get a cell value"

However, if the updated cells are committed to the write back table, the error disappears during a subsequent query for the same cell set. Is this a known issue? Are there any remedies aside from upgrading to SSAS 2005?

Thanks!

Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:48 AM

SQL Express Family CTP Now Available

I'm happy to announce the release to public CTP of the following SQL Express Family products:

SQL Server 2005 Express Editions SP1
SQL Server 2005 Express Edition with Advanced Services
SQL Server 2005 Express Edition Toolkit
SQL Server 2005 Management Studio Express (included as part of Express Advanced & Express Toolkit)

You can download and install these CTPs from the CTP download page. Here is a brief description of each product:

SQL Server Express Edition with SP1
SQL Server 2005 Express Edition (SQL Server Express) is a powerful and reliable data management product that delivers rich features, data protection, and performance for embedded application clients, light Web applications, and local data stores. Designed for easy deployment and rapid prototyping, SQL Server Express is available at no cost, and you are free to redistribute it with applications. If you need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.

SQL Server Express Edition with Advanced Services
SQL Server 2005 Express Edition with Advanced Services is a new, free version of SQL Server Express that includes additional features for reporting and advanced text based searches. In addition to the features offered in SQL Server 2005 Express Edition, SQL Server Express with Advanced Services offers additional components that include SQL Server Management Studio Express (SSMSE), support for full-text catalogs, and support for viewing reports via report server. SQL Server Express Edition with Advanced Services also includes SP1.

SQL Server Express Edition Toolkit
SQL Server 2005 Express Edition Toolkit (SQL Server Express Toolkit) provides tools and resources to manage SQL Server Express and SQL Server Express Edition with Advanced Services. It also allows creating reports by using SQL Server 2005 Reporting Services (SSRS).

SQL Server Management Studio Express
SQL Server Management Studio Express (SSMSE) provides a graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services instances. SSMSE can also manage relational engine instances created by any edition of SQL Server 2005. SSMSE cannot manage Analysis Services, Integration Services, SQL Server 2005 Mobile Edition, Notification Services, Reporting Services, or SQL Server Agent.

Feel free to discuss these releases in the SQL Express Forum.

Report any issues you find in the MSDN Product Feedback Center

Regards,

Mike Wachal
SQL Express team

Reply:

Are there problems with installing these on top of SQL Server 2005 Express? I have:

Microsoft SQL Server Management Studio Express      9.00.1399.00
Microsoft Data Access Components (MDAC)      2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML      2.6 3.0 5.0 6.0
Microsoft Internet Explorer      6.0.2900.2180
Microsoft .NET Framework      2.0.50727.42
Operating System      5.1.2600

but I have already had significant time-wasting problems because I had the Beta Version of SQL Server 2005 installed previously - in the end I wiped my hard drive and re-loaded XP.

It is the Reporting Services I am after - good advice would be appreciated.

regards, David Wilson.