Thanks in advance.
Mike
There are others that have had the same thought. Is this what you are looking fore(http://prologika.com/CS/blogs/blog/archive/2006/10/21/Linked-Attribute-Hierarchies.aspx) ?
Perhaps in a future release.
HTH
Thomas Ivarsson
|||Hi ThomasThanks for this, if this is in a future release I really think it would be great.
Mike
Hi;
I have been trying to create Time Dimension for one of the column in the table i.e. EntryDate and I am unable to connect the time dimension which I create using the Wizard using the 2nd option which is Without using data source. It created a dimension but it is not connected to any data source and when I process it gives me error, data source is not specified.
I am very new to Analysis service, I am using SQL Server 2005. I want to create a report where I can show my data by month, by year depending upon the EntryDate column of a table.
Thanks
Have you tried the adventure works dw data base that is part of the samples that you can install with SQL Server 2005 and SSAS2005? In that database you have several dimension tables and a time dimension table.
If you would like to test your skills in building dimensions with a data source I recommend this database.
Regards
Thomas Ivarsson
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 dataHow 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.
In a response posted Nov 21 (Clustering Dimension), Jamie wrote...
"The only option of using a table-based model as a dimension is to write out the cluster labels and simply make the cluster label as a dimension attribute. You could even append the cluster label to the source data (e.g. the customer table) and not have a seperate dimension, simply a browseable attribute on the dimension of interest"
Jamie, can you provide more information on how to do this? We'd like to have a series of clusters in an existing household dimension. That is, we need multiple occurences of cluster model results over time browsable in the source cube. I've looked at the data source, dimension, and cube created by the data mining model, but I don't see where the case ID (Household Key) and the cluster name could be extracted to update the existing dimension. We're using the cube for the data mining source.
This would also help to fix a recurring problem we have with keeping the linked cube and the source cube metadata in sync. If I make a change to the source cube, say by adding a new measure, the metadata for the linked cube gets out of sync. I've been deleting the data mining dimension, cube, and dsv and them adding them back in using the data mining menu in the model.
Sorry for the late reply - you can use SSIS to take the results of a query e.g. SELECT t.HouseHoldID, Cluster() FROM MyModel PREDICTION JOIN .... , and save to a table. Then add the table to the DSV you use to process the cube. It may be easier to process the cluster model from the source data rather than the cube, although either option is entirely possible, and if you are using aggregated measures found in the cube, the cube method is likely better.
Unfortunately for your latter question, there is no way to keep the cube metadata in synch with linked measure groups. However, if the only difference is the presence of a single DM dimension, it is easy to drop and recreate the linked cube from the user interface. If you have more than one DM dimension, it's more difficult, but it's probably more reliable than trying to make all of your other changes in both places.
|||Thanks for the repy Jamie. We are using aggregated values from the cube in our model. We'll probably use 4-5 cluster models in our cube, so being able to query them and add them to the DSV will save a lot of time rebuilding the data mining dimensions with the wizard.
Since we're not doing prediction in the query, wouldn't the DMX query be something like:
Select [Household ID], Cluster() From [MyModel]
When I run this I get:
Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 8.
Thanks for your help
|||After some more reading, I think I understand the error. I've been thinking of retrieving the case ID and the cluster from the trained model, not issuing a prediction query to get the cluster name for a new case.
In our model, we're using clustering against a household dimension with a nested fact table containing a profitability fact and the fact components that make up profitability (i.e. clustering around the factors that make someone profitable or not.) These are not attributes that can be used to predict profitability, rather they describe it. It sounds like you're saying that we need to make profitability predictable and issue a prediction query on these facts to get the case ID (Household Key) and Cluster(). Is this correct? I've been trying to use the Mining Model Prediction tab to help me build the query, but I don't see how to get the nested case data in the query.
|||You can enable drillthrough on the model (available in the wizard or the property page for the model) and then you can issue statements like
SELECT * FROM MyModel.CASES
If you want to get the cluster membership for each row you can issue a query like (check syntax) (you won't be able to create with the prediction query builder - you will have to enter by hand)
SELECT t.[Household ID], Cluster() FROM MyModel NATURAL PREDICTION JOIN (SELECT * FROM MyModel.CASES) as t
FYI, you can just add the nested table to a query, e.g.
SELECT t.[Household ID], t.[MyNestedtable], Cluster() ...
or if you want everything
SELECT t.*, Cluster() ...
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
