Data Source for Process Cube

Is the source for the data used to process the cube actually embedded in the cube itself? I.e. I have source data which could move around. How can I programatically modify the source connection string before processing the cube using SSIS?

Why do you want to change the datasource?
You can have several partitions in a cube and each partition with different datasource.
If you want to update data in a datasource you have to proccess only this partition.
That's all|||Because I need to create approximately 2300 unique cubes for different customers that are all sourced off 2300 different SQL databases residing on different servers throughout the data center.

All the source databases use the same schema. I am using the same ETL process to create 2300 warehouses. Now I need to create the unique cubes off those warehouses. Seperate "database"/cubes are required for compliance reasons.

Thanks,

Chris|||Then what you need is a different cube for each warehouse. You can have 1 cube with 1 partition. Because when you proccess full the partition you overwrite data.
If I where you I try to make a client dimension or something like this and then filter data with dimension.
All the data in a unique database/table, and the cube reads this table.
Are you thinking to give access with Excel? If you are using Excel use instead multiple partitions.|||You still don't get it. I need to figure out how to change the Data Source information that is embedded in the cube at the time I do the processing. i.e. I need to figure out how to run an XMLA query against the Analysis Services database that will allow me to change the connection string for the data source so that when I subsequently process the cube I am guaranteed it will find the source database.

The source database could move in the data center for load balancing reasons, etc.

Thanks,

Chris|||Hi Chris,

You can do this with an XMLA query (as you suggested) that looks like the one I posted below. This runs an alter command on the data source for the "Adventure Works DW" Analysis Services database. So, by changing the "Data Source" and "Initial Catalog" settings in the connection string, you update the data source in the deployed database.

Hope that is what you are looking for. BTW, I created this XMLA script by going into SQL Server Management Studio, connecting to Analysis Services, then drilling down within a deployed version of the "Adventure Works DW" database, selecting the data source, then right-clicking on it and selecting "Script Object As", then "ALTER to", then "New Query Editor Window". Great way to capture various XMLA scripts for automating changes to a deployed AS..

Dave Fackler

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>Adventure Works DW</DatabaseID>

<DataSourceID>Adventure Works DW</DataSourceID>

</Object>

<ObjectDefinition>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">

<ID>Adventure Works DW</ID>

<Name>Adventure Works DW</Name>

<ConnectionString>Provider=SQLNCLI.1;Data Source=newserver;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=newdatabase</ConnectionString>

<ImpersonationInfo>

<ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>

</ImpersonationInfo>

<Timeout>PT0S</Timeout>

</DataSource>

</ObjectDefinition>

</Alter>

|||You are brilliant. I knew about the scripting feature however I wasn't thinking about the data source being surfaced in the Management Console for analysis services. Thanks!

Data region in RS allows only 1 data source - virtual cube solutions?

I have a report requiring two different data sources be compared in one
data region. For example, I'm comparing a measure for a client against
a national average. The client measure comes from one cube and the
national average is stored in a sql database. RS won't allow two data
sources to be used in a data region unless you can use an aggregate
function on the second data source. I attempted to solve this by
creating a cube for the second data source (national averages) and then
a virtual cube to make one data source (i.e. one MDX statement from the
virtual cube per data region) for RS. The orginal MDX used for the
first data source required slicing by dimensions not included in the
national average cube and visa versa. I have been able to get the
correct answer for each cube before combining into the virtual cube,
but I can not get the measures correctly in the virtual if, for each
measure, I need to slice by a dimension that is not shared. I haven't
been able to find the information anywhere (and I've tried internet and
2 books), but because my measures are not correct I assume that a
virtual cube in only useful if the correct results are obtained by
slicing into only the shared dimensions? Are there any other solutions
to the rediculous RS limitation of only being able to provide one data
source to a data region? What we they thinking'oops, should have used spell check before submitting. it is a
ridiculous RS limitation.|||Hi Carrie,
I believe you can refer to more than one datasource from a data region such
as a table. Try putting in two data sources on a report and drag data from
them both into a table (note, you have to set the tables data source to one
data set).
see how that goes
<cwilliams@.choosehmc.com> wrote in message
news:1104767162.584780.225260@.z14g2000cwz.googlegroups.com...
> I have a report requiring two different data sources be compared in one
> data region. For example, I'm comparing a measure for a client against
> a national average. The client measure comes from one cube and the
> national average is stored in a sql database. RS won't allow two data
> sources to be used in a data region unless you can use an aggregate
> function on the second data source. I attempted to solve this by
> creating a cube for the second data source (national averages) and then
> a virtual cube to make one data source (i.e. one MDX statement from the
> virtual cube per data region) for RS. The orginal MDX used for the
> first data source required slicing by dimensions not included in the
> national average cube and visa versa. I have been able to get the
> correct answer for each cube before combining into the virtual cube,
> but I can not get the measures correctly in the virtual if, for each
> measure, I need to slice by a dimension that is not shared. I haven't
> been able to find the information anywhere (and I've tried internet and
> 2 books), but because my measures are not correct I assume that a
> virtual cube in only useful if the correct results are obtained by
> slicing into only the shared dimensions? Are there any other solutions
> to the rediculous RS limitation of only being able to provide one data
> source to a data region? What we they thinking'
>|||The answer is to create a calculated member using the ValidMeasure
function for each measure in the virtual cube. Just in case anyone is
interested...sql

Data mining cube with 0-value facts

I'm defining a mining structure against an OLAP dimension. The continuous value that I'm using both as input and for forecasting represents the time to complete a certain process.

There's something that strikes me as if it could be a problem, but I'm not sure. Our fact table has multiple columns (with multiple correponding measures in the cube). The "time-to-complete" measure is only populated on some of the fact rows - the rows that represent completion information. Other rows represent other information, and the "time-to-complete" value is set to 0. This works fine for cumulative time-to-complete and average time-to-complete, but it seems like it could mess up data mining. Will those 0-value facts skew the mining results? I'm not seeing a way to filter out those entries and only include the non-zero facts in the mining processing.

Or perhaps I'm totally misunderstanding something, which is quite possible. :)

The zeroes will mess you up in DM and OLAP. An average of a value ignores nulls, but not zeroes.

You can set your measure to retain null values in the fact table - check out the data binding settings.

Hope this helps,

Richard

|||The zeros are properly handled in our average calculated measures in OLAP (we don't use the EE AverageOfChildren aggregation function).

Sounds like it will screw up DM stuff, though. If the values were nulls instead of 0, would DM handle it correctly?|||

Sine you've modeled the variable as continous, a 0 value will be treated as a possible value whereas a null would be treated as missing by DM. They would hence generate different DM model.

If the only zero value in your data correspond to null (missing) value, you can either filter those out from the input data or have another computed measure be null whenever the measure is 0 and use that measure as input to the data mining algorithm.

Hope this helps

|||>you can either filter those out from the input data

How would I do that? There isn't a dimension slice that represents this condition, and that's the only filtering that I've been able to find so far.

A computed measure wouldn't be ideal, but it might work. I'll think about that.
|||Sorry, my earlier statement was not accurate. You cannot filter by measure values in OLAP Mining Model.|||OK, after doing a little more testing, I'm more confused.

As a test, I created a new calculation that takes the original measure and converts 0-valued facts to Nulls. I added that new calculation to the mining structure and models and processed them. Then I ran a singleton prediction query for both the original and new measures. They came up with the exact same results. So, as far as I can tell, either:

a) The 0-value facts don't affect the mining results
b) The null facts are treated the same as the 0 facts (and not ignored)
c) Something about how I'm doing my test is flawed.

Anyone have any suggestions or thoughts here?|||It's possible that the column doesn't have any impact on the result at all? What algorithm are you using and does the column show up as "important" in the viewer?|||Actually, I'm working with a degeneratively simple test case here, where the mining structure only includes one input - a single continuous value, used both as input and for prediction. Perhaps that's too trivial for a reasonable test.

I'm using the Neural Network algorithm, but I can't bring up the viewer - I just get an error (which I reported in another thread in these forums).|||

Aha!

That's the reason for this problem and may be the reason for the viewer problem as well. It seems you found a bug in the system, the bug being that the software didn't return an error when you tried to create this model .

When you mark a column as "input" what you are stating is "use this column as an input to predict other columns." When you mark it as output you are stating "determine values of this column based on all other inputs." We have the concept of "input AND output" because we allow you to predict multiple attributes in a single model.

Therefore, what is happening is that for that column, you have no inputs, and it is an input for no other columns, since there are no more columns for which it could be an input. Simply put, it's an invalid model definition which we should have rejected from the start.

Thanks

-Jamie

|||Doh! Did I mention I was a noob with data mining? :)

I fixed the model, and the results seem to make more sense now. However, it didn't fix the problem with the mining model viewer.|||It's hard to say - there may not be enough differentiation for the viewer to show patterns at that point. I would try things out with a more well known dataset - e.g. the tutorial so you can get a feel for how the tools work in a tested environment and then see if the issue seems to be the software not working correctly or your data not being rich enough to demonstrate descriptive results.

data mining and SS05

I've seen a couple of article on creating and scoring some mining models -- very cool stuff. Is the only way I can do this from an OLAP cube? Can I create, train, and score a model just from relational data?

Thanks!

You can create models directly from cubes using the wizard. Scoring models can be done using an MDX input to a prediction join statement e.g.

SELECT Predict(MyColumn) FROM MyModel
PREDICTION JOIN
<MDX Statement>
ON <bindings>

|||

The data mining functionality in SQL Server 2005 Analysis Services is not restricted to source data in OLAP cubes - you can create, train and query (score) mining models based directly on data in any relational data source. You can find more information, including tutorials at http://www.sqlserverdatamining.com.

Data mining against a cube with time dimension

We have a set of cubes and dimensions, and we're experimenting with data mining against the cubes (primarily for forecasting applications). We have a custom time dimension (which we call calendar), not generated by the BIStudio wizard. The dimension has year/month/day/hour/... attributes. But when I try to add this Calendar dimension to the mining structure as a nested table using BI studio, it only shows the Year attribute, not the others. Other dimensions seem to show all the attributes.

Is there something we've done wrong in defining our time dimension? What determines which attributes show up as available for selection in BI studio?Attributes need to be "browseable" to be included in mining models, meaning they need attribute hierarchies. In this case you would need a "YearMonth" attribute to build a model by months. If, for example, you simply used "month", you would only get 12 months - each with the aggregation of all years for each month.|||Thanks, Jamie. But our attributes do have attribute hierarchies enabled (although they are set to invisible, so only our user hierarchies show up in cube browsers). And the attributes use composite keys which include all the relevant time components (e.g. year and month for year attribute), which if I understand you correctly is equivalent to your "YearMonth" example. Yet still, only Year shows up as an attribute for DM.|||I did a little more experimenting with this problem, and here's what I've found.

If I use the New Mining Structure wizard, then when adding a nested table all of the attributes in our calendar dimension are shown correctly, and I can select them.

However, if I don't add the nested table in the wizard, then try to add it later in the designer, only the Year level shows up. I can't seem to get the other attributes to show up as options at that point.

You know, I just went in to Adventure Works and tried setting up a new mining structure against the cube, and it looks like it behaves the same way. So I guess there isn't something wonky about our dimension setup. But why this difference? Why can I add an nested table with an attribute in wizard but not the designer?|||

When you select a Measure Group->Dimension, the attributes you should see are not all the Cube Dimension attributes, but the attributes in the Dimension which relate to the measure group. The attributes shown by the designer is the correct set. In your case, please check whether the measure group relates the Time dimension to the fact table by the Year attribute.

We have confirmed that this is a bug in the wizard because it shows all the attributes of the cube dimension. We’ll address this issue in an upcoming release.

Thanks