Another issue with reference dimensions

Hi guys,

I encountered another issue with reference dimensions which I'm not sure if it's not by design. So I decided to ask

The problem is that I have the following:
- Fact table
- Dimension1
- Dimension2

Dimension1 is having a regular relationship to the fact table. When I deploy the project at this point, everything is working as expected - I can slice the measures by all the memebers of Dimension1's attribute hierarchies.

When I set Dimension2 as a reference materialized dimension to the fact table, using Dimension1 as a reference dimension, I encounter a big problem. As far as I understand how reference dimensions work, what happens behind the scenes is that the a FK to the referenced dimension (Dimension2) is also added to the fact table so that the slicing is possible. However, considering that I don't have a record in dimension2 for each record in dimension1, the result is that in the fact table I don't get all the possible slicings for dimension1 which I used to have before that. I guess that the SQL query it builds for populating the fact table is now an inner join with both tables (dimension1 and dimension2) which is perfectly valid if I'm slicing only through dimension2, but I still need the results which I got before that for dimension1 and the fact table.

I don't know if I made it clear, but the bottom line is that I need to be able to slice normally by all records in both dimensions - the usual and the referenced ones. Currently the setup (at least by default) stores records in the fact table only for the intersection of the two, which is undesired behavior in my case.

Any ideas if this is by design or if there's something wrong that I'm doing?

Cheers,
Alex
I forgot to mention that if I set the reference relationship not to be materialized, then everything works perfectly. But as from what I've read this is really not a good idea cause it's much slower.

So the question is - could I achieve the same behavior by preserving the materialization of the referenced relationship?
|||Any ideas?

another design question

Is there any database design diagram for a library system.. ? Would love to
look at its schema..
http://www.databaseanswers.com/data_...Y_DO_I_DO_THIS
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eimBRZkJEHA.556@.TK2MSFTNGP10.phx.gbl...
> Is there any database design diagram for a library system.. ? Would love
to
> look at its schema..
>

another design question

Is there any database design diagram for a library system.. ? Would love to
look at its schema..http://www.databaseanswers.com/data...HY_DO_I_DO_THIS
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eimBRZkJEHA.556@.TK2MSFTNGP10.phx.gbl...
> Is there any database design diagram for a library system.. ? Would love
to
> look at its schema..
>

another design question

Is there any database design diagram for a library system.. ? Would love to
look at its schema..http://www.databaseanswers.com/data_models/index.htm#WHY_DO_I_DO_THIS
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eimBRZkJEHA.556@.TK2MSFTNGP10.phx.gbl...
> Is there any database design diagram for a library system.. ? Would love
to
> look at its schema..
>

Another database design issue help!

Hello all,
I am developing a web application and im in the middle of database design and facing a design issue. Here it is: A table for products a table for SizeChart (Small, Medium, Large) ColorChart (White, Ash, Yellow, Pink).
Each product is available in combination of different colors and size. Then product has images which are stored based on the selection from ColorChart table. I have created a table where many to many relation between product to color and size are related with primary key identity field for junction table too. I also have created a table tblProdImages with ProductID, ColorID, ImagePath fields
Here is the structure
Product ColorChart SizeChart
--- ---- ----
ProductID ColorID SizeID
Name ColorIcon Sybol

ProductColorSizeChart (junstcion table) ProductImages
------------ ------
ProductColorSizeID (Indentity) ProductImageID (Identity)
ProductID - FK ProductID - FK
ColorID - FK ColorID - FK
SizeID - FK ImagePathThumbnail
InStock ImagePathLarge
Offline

I have write DAL for product and managing junction table relation as collection in my product b/e object.
Here is my question
1- How effective this whole db schema is and how manageable is the relations, Suggest if any better solutions is.
2- Im facing updating problem in ProductDAL for updating collection items in database.
3- How can i manage and implement lazy loading in this scenario in my own written Data Access Layer.

Please clarify 'DAL' and 'b/e'|||1) The schema looks pretty good. The relationships look fairly straight forward and easy to maintain.
2 ) It would help if I had a better idea of what your Object Model & DAL look like.
3) It doesnt look like you have a situation where objects have amassive amount of data associated with them. Im not sureLazyLoading would be worth the trouble. Caching the objects andreturning references might be more useful for you.
|||Thanks Jeffrey for a prompt reply. First of all as far as b/e (business entity objects) are concerned they are blue print parent tables. As for relation btw product, color, size i have created a collection where each item is a structure having two fields for b/e of ColorInfo and SizeInfo (mapper of Color and Size tables). There is a possibility that a product may need to be added where this relation may not be applied as that product has no dependency of color and size so the collection will be empty and in DAL (Data Access Layer) class of product i have this check to determin the count of collection. If empty then Save method for relational entity objects will not be called.
I have implemented DAL code for saving relational object (Product,Color,Size) in product. Is it a right place?|||

asp.dude wrote:

Thanks Jeffrey for a prompt reply. First of allas far as b/e (business entity objects) are concerned theyare blue print parent tables. As for relation btw product, color, sizei have created a collection where each item is a structure havingtwo fields for b/e of ColorInfo and SizeInfo (mapper of Color and Sizetables). There is a possibility that a product may need to be addedwhere this relation may not be applied as that product has nodependency of color and size so the collection will be empty and in DAL(Data Access Layer) class of product i have this check to determin thecount of collection. If empty then Save method for relational entityobjects will not be called.
I have implemented DAL code for saving relational object (Product,Color,Size) in product. Is it a right place?

Thatsounds correct to me. I really couldnt tell you much more without seeing code.

Another aggregation design question

Guys,

I model a cube with dimensions, I create the partitions and their aggregations, then I change the dimensions structure or I change the dataview.

Cube does not complain about aggregations at any time during the change process.

So what is this all about?

I suspect I should run a re-design of aggregations when I am done with structural changes but I wonder why I am never warned about existing aggregations becoming obsolete?

So when should you re-design aggregations? (I use 30% then I add usage based after a while).

Thanks,

Philippe

Yes, you need to go an re-desing your aggregations after you've made stuctural changes to your dimension: You've added, or removed attrubutes from the dimension, you've changed attribute relationships you should go and re-desing aggregations.

If you defined natural hierarchies ( you can search this forum for the definition of these) 30% should be a good start. But in case and if you are using just regual hierarchies without defining attribute relationships, or you browse attributes using attribute hierarchies, you should go over all the attributes you browse by and change AggregationUsage property to Full or Unrestricted.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Annotations in design mode?

Is there any way to persist Annotations in design mode? I'm considering using them to keep track of measure-group specific state, but I'm worried that every time I re-deploy my cube, I'll lose this info.

Does anyone have experience using Annotations? Are they only exposed via the AMO OM?

In design mode, you can switch to raw XMLA code view by right clicking on the object. You can select "view code" and you'll see XML representation of the object.

You got to be careful modifying the XML directly such that if something goes wrong, you might loose consistency of your object. But on the other hand XML representation gives you power to access any property (such as annotations ) directly without UI.

Again. Be careful with using raw XML code view, but if you feel comfortable with modifying XML, adding new tags you should be able to add annotations to pretty much any object in your database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||A collegue and I are looking at possibly adding this feature to BIDS through an open source project we are working on at http://www.codeproject.com/BIDSHelper unfortuntately this is not one of the features we have in the pipeline for the first release, but it is one of the things we have on our list of possible features.|||The BIDS helpers look pretty cool, thanks Darren.|||

The ability to edit annotations is finally coded in the BIDS Helper project. See:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

Hope that's helpful.

Annotations in design mode?

Is there any way to persist Annotations in design mode? I'm considering using them to keep track of measure-group specific state, but I'm worried that every time I re-deploy my cube, I'll lose this info.

Does anyone have experience using Annotations? Are they only exposed via the AMO OM?

In design mode, you can switch to raw XMLA code view by right clicking on the object. You can select "view code" and you'll see XML representation of the object.

You got to be careful modifying the XML directly such that if something goes wrong, you might loose consistency of your object. But on the other hand XML representation gives you power to access any property (such as annotations ) directly without UI.

Again. Be careful with using raw XML code view, but if you feel comfortable with modifying XML, adding new tags you should be able to add annotations to pretty much any object in your database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||A collegue and I are looking at possibly adding this feature to BIDS through an open source project we are working on at http://www.codeproject.com/BIDSHelper unfortuntately this is not one of the features we have in the pipeline for the first release, but it is one of the things we have on our list of possible features.|||The BIDS helpers look pretty cool, thanks Darren.|||

The ability to edit annotations is finally coded in the BIDS Helper project. See:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

Hope that's helpful.

Analysis Services for dba's?

I know this is a silly question so I apologize in advance. Is the
implementation/ configuration/ design/ loading/ etc. of AS typically the
responsibility of a DBA or a Developer of a company? From the perspective
where there is a definate difference between the two roles of course.
--
SQL2K SP3
TIA, ChrisRChris
I am both at work and I firmly believe the dba is reposnible for the
management of the databases and servers in terms of optimising and load
balancing.
Develpers typically come up with a schema or ER diagrams and run them by the
dba with a view for any further normalisation etc.
But I get to do both in my case.
evan
"ChrisR" <bla@.noemail.com> wrote in message
news:Oi$ZIQl3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> I know this is a silly question so I apologize in advance. Is the
> implementation/ configuration/ design/ loading/ etc. of AS typically the
> responsibility of a DBA or a Developer of a company? From the perspective
> where there is a definate difference between the two roles of course.
> --
> SQL2K SP3
> TIA, ChrisR
>

Analysis Services for dba's?

I know this is a silly question so I apologize in advance. Is the
implementation/ configuration/ design/ loading/ etc. of AS typically the
responsibility of a DBA or a Developer of a company? From the perspective
where there is a definate difference between the two roles of course.
SQL2K SP3
TIA, ChrisR
Chris
I am both at work and I firmly believe the dba is reposnible for the
management of the databases and servers in terms of optimising and load
balancing.
Develpers typically come up with a schema or ER diagrams and run them by the
dba with a view for any further normalisation etc.
But I get to do both in my case.
evan
"ChrisR" <bla@.noemail.com> wrote in message
news:Oi$ZIQl3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> I know this is a silly question so I apologize in advance. Is the
> implementation/ configuration/ design/ loading/ etc. of AS typically the
> responsibility of a DBA or a Developer of a company? From the perspective
> where there is a definate difference between the two roles of course.
> --
> SQL2K SP3
> TIA, ChrisR
>

Analysis Services 2005 Design Challenge

Hi to all, I’m designing a SSAS 2005 cube based on an Oracle data source

We have a classical star schema with several millions of records into the fact table.

Now everything goes fine since dimensions are quite small (the greatest one has 100000 records), but users are asking me the possibility of view also more detailed data such order_number, client fiscal code, etc…and this data it has been stored into the fact table as a fact degenerate dimension.

Now I don’t want to load into the cube this degenerate dimension because it contains as I said too many records.

I was thinking to drill-through functionality (achieved setting the storage mode of the degenerate dimension to ROLAP) but after several tests it seems very very slow (it launches queries that incredibly big grouping all the dimensions and all the fields of the fact table or other times launches several heavy queries catching all the distinct values of order_number, client fiscal code, etc…) and some times the client itself (OWC) hangs and I have to kill it.

Note that these tests are done on the development environment where the fact table contains more or less only 70000 records!

I tried also to query directly this degenerate dimension (without drill-through) but the results are the same.

Any suggestions?

Thanks.

Alberto

Try using MOLAP dimensions. They should scale pretty well in AS2005.

You should be able to handle several milion members with MOLAP dimensions.

Hope that helps.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.