Star Model Question
Oct 13, 2000
My clients are not intersted in using Auto generated keys. They are
also get data from many sources they would like to use something like
customerID. Dose anybody know of any reason why we should not do that?
Also they are concern about sql server not being able to handle the datawarehouse in future because they are expecting it to grow in terabites.
Dose anyone have advice on that?
I was thinking of putting the fact table on a different file group don't if it will help.
View 1 Replies
ADVERTISEMENT
Oct 11, 2000
I am re-engineering the data warehouse and my client is currently using autogenerate keys, their concern is that after a certain amount of keys (can't remember the figure) sql server starts having problems, dose anyone know how i should handle it when i am doing the designing?
thanks any input will be appreciated
View 2 Replies
View Related
Dec 5, 2005
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies
View Related
Dec 27, 2004
Hi,
I have tried the following out and would appreciate feedback from experienced users regarding if the following is a good/bad approach:
After bring all the data in my Data Mart, I have created a view which has all the data in a big flat table (totally unnormalized). Then based on this BIG FLAT UNNORMALIZED VIEW :) I have created my various dimensions using the 1st option i.e. Star Schema.
Based on the little testing that I have done, I seem to be getting the correct results across various dimensions... However, can someone kindly comment on this approach and the pros/cons.
Thanks
View 4 Replies
View Related
Dec 18, 2002
does any one know how to built a star schema by DTS:confused:
View 4 Replies
View Related
Nov 23, 2007
I'm designing a DW, and i have some doubts relative to the Distributed Transaction when modeling a star schemma.
My problem is: I have a main dtsx package in wich i call all the child packages in order to create the (Fact and Dimension Tables).
(1) First i have several child packages that create and populate all the Dimension Tables (with the latest values from the relational DB).
(2)Then i have several child packages that create all the fact tables, in this process i use the surrogate keys from the dimension tables (obtained in step 1).
The problem here is , " How do i use the multiple transaction ?" , if i put a "required" Transaction Option on the parent package, then after calling the child packages that creates the dimension tables. The values are not commited, so they are not available when i later execute the childs packages related with the fact tables.
How can i use transaction when modelling a star schemma, in order to have a full roll back or a full commit in all tables (Dimensions and Fact Tables).
Thanks
View 6 Replies
View Related
Jul 8, 2014
In SQL Server I can select a specific column in from of * like so:
Code:
select test_column_1,* from testtable1
I've been googling around and cannot seem to be able to find a definitive answer.
View 1 Replies
View Related
May 16, 2007
Hi All,
we are just starting to do some testing on sql server EE with dimensional models.....we have had one or two problems we have been able to solve using the new peformance dashboards etc.
However, as is inevitable, we are seeing strange behaviour of a query....in a star join it seems to be doing an eager spool and trying to spool the entire fact table to tempdb....hhmmm....
Rather than ask one question at a time.....we have DBAs who went to classes etc at MSFT and the client is some level of MSFT partner.
Could anyone point me to the best documentation for understanding the optimiser and how to influence it to get it to do the right thing in optimising plans for star joins?
Thanks
Peter
View 6 Replies
View Related
Apr 30, 2008
So this has got to be considered a major, major flaw in how SSRS interacts with Oracle. I'm using the "Oracle" data provider, but I've also tried using Microsoft's OLE DB data source, and some others, and in no case does SSRS hand off to Oracle a query that does NOT have bind variables. In other words, typically query parameters get passed off to Oracle as bind variables.
The incredibly major problem that this causes is that it disallows Oracle's use of star transformation queries which is the primary method by which to get fast responses to a data warehouse/star schema, in fact a prime authority on this subject (Bert Scalzo, Oracle DBA Guide to Data Warehouse and Star Schemas, p.86 -- obvioulsy not using Oracle 7x was the first) lists it as in effect the #1 consideration.
So what gives? In effect SSRS cannot be used against large scale Oracle data warehouses? I've had success with Business Objects being able to access Oracle star transformations.
So a guess my question is how the heck can use SSRS in a big, Oracle-based data warehouse?
http://www.dba-oracle.com/oracle10g_tuning/t_star_transformations_sql.htm
For star_transformation join plans, the following parameters must also be considered: ... No BIND VARIABLE in SELECT statement
http://www.orafaq.com/usenet/comp.databases.oracle.server/2003/09/28/2305.htm
Star transformation is not supported for tables with any of the following characteristics:
* Queries that contain bind variables
View 1 Replies
View Related
Jan 19, 2007
Hi all,
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
V
View 4 Replies
View Related
Oct 22, 2015
We have an OLTP database and operational reporting is carried out on a replica server / database. We have plans to build a new data warehouse and an analysis services cube.
Question 1:Should a cube be designed to extract data from a physical star schema rather than a logical one (3NF relational (ODS?) using a data source view to derive the star)? I'm guessing for performance it's better to pull data from similar structures (physical facts and dimensions as required by analysis services) but is the difference significant?
Question 2:Depending on the answer to q1, is it bad practice to ETL data from a staging database (replica > staging) directly to a star schema (multiple data sources and cleansing / business rules required)? Or should it be processed from staging to an ods and only then to a star schema (physical or logical). I still don't know if an ODS is required but I guess the consideration for this decision is whether the business would require daily operational (or ad hoc) reporting on the consolidated data sources (without needing historical DW functionality).
View 2 Replies
View Related
Oct 19, 2015
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.
Productnr Productdesc Language
1 product EN
1 produkt DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
View 2 Replies
View Related
Apr 25, 2008
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
View 6 Replies
View Related
Nov 16, 2007
I am cutting my teeth on star schema design. I have a simple star schema I am building for Headounct analysis at work. I have a factless fact table where a row represents a head in the company. Each head is toed to a particulat week in a Date dimension tabel. There are additional dimensions for things like gender, ethnicity, marital status, age, etc. Now in my department dimension - it's hierarchical. In the DimDepartmnet there is a department which belongs to a company. Comapnies belong to divisions. Now the fun part. Each division has a headcount target for each year. Up to this point I am in a perfect star schema (no snow flaking). How would I integrate in this concept of a headcount target for each division for a given year?
We are using cognos on top of this star schema to provide reporting and analysis services if that is relevant. From the Star Schema design stand point... any thoughts?
Christian Loris
View 3 Replies
View Related
Oct 27, 2007
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
View 1 Replies
View Related
Aug 10, 2005
hi !!i have a question about the connected and disconnected model to access the Sql server DB.......i know that there is better to choose one rather than the other in some situantions and there is no better model in all cases...... os i hope you can help me to decide what shall i choose...i will use the DB to connect to Web services and read data from the DB and wrtie some data back.......i do not know that to use ..... i hope you advise me and tell me about the rules that will allow me to choose what model to choose .... i appreciate your help!!Thanks !!!
View 2 Replies
View Related
Nov 15, 2006
I get the following error when I try to load the mining model in the mining model viewer
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[NeuralNet].[GetAttributeValues]' function does not exist.
I get a similar error when I try to load the Load Mining Accuracy Chart
Failed to execute the query due to the following error:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[AllOther].[GenerateLiftTableUsingDatasource]' function does not exist.
I have OWC 11 installed. What am I missing here?
Thanks
View 7 Replies
View Related
Jun 6, 2001
Hello,
I just installed the enterprise edition of MS SQL Server 2000 in model database there are no store procedure. whereas in MS SQL Server 7.0 there are 18 SP so when I create the new db there is no SP only the one which I've created. Is it normal or did I miss something in installing
Thanks
Rea
View 1 Replies
View Related
Apr 9, 2008
Hi,
I'm new in SQL server DBA and in my company. There are about 40 applications in my company and since there's no DB document available, my boss has asked me to prepare a full documentation package about DB schema of each application. I'm going to use RUP method for that but I'm not sure of including which information in the package.
Any idea is appreciated
View 7 Replies
View Related
Apr 5, 2006
hi
I have changed the initial size of the model DB , now I want to change it again , but I want to make it smaller than the one it is currently can i do that
When I go the EM & try changing the file size it says :-
The new DBFile size must be larger than the current size
Regards
aki
View 2 Replies
View Related
May 7, 2002
Hi there,
My problem is : No de system stocked procedure in the system db "model".
What should I do to visualise theses sp.
any help will be appreciated.
Thanks in advance
View 1 Replies
View Related
Jan 31, 2003
SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.
Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.
1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?
Any suggestion is really appreciate.
Thank you very much - kind regards.
View 2 Replies
View Related
May 7, 2002
Hi there,
My problem is : No de system stocked procedure in the system db "model".
What should I do to visualise theses sp.
any help will be appreciated.
Thanks in advance
View 1 Replies
View Related
May 31, 2002
Hello,
I'm new to MS SQL Server I want to know which recovery model is good, Full or Bulk Logged as I'm doing full backup at 11:00 PM and diffential Backup at 12:30 PM and from 8:00 AM to 6:00 PM 15 min log backups.
Please guide me which recovery model should I choose.
Thanks
Lara
View 1 Replies
View Related
Jun 10, 2004
Hi all,
I need some sugestions from all of you about setting up security model in
our SQL2000 box.
The server was setup using Mixed mode. However, all the applications
(web and MS access) access the server using "sa" userid.
There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)
Application 1: need read/write access to DB1,DB2 and DB3
Application 2: need read/write access to DB5
Application 3: need read/write access to DB4 and DB3
Should I set up three userids and give them the dbo access to those
database that they need to use?
Does that make any sense to you?
Thank you for all your suggestion
View 5 Replies
View Related
Nov 26, 2004
Hello, i have no specific problem but i just want to know your opinion on the issue. Last week I tested my recovery scenario and after installing new server i successfully restored master db. Then I tried to start server, but it wouldn't come up because physical path of model db in master..sysdatabases table didn't correspond to the actual location of db on new server, so that tempdb couldn't be created from model db on server startup. I started server without recovering dbs with exception of master using trace -T3608. Now I hoped that I could restore model using MOVE clause but it gave me error saying that restore operation needs to use temporary table in tempdb. To break this vicious circle I used model db files from installation CD and put them to the expected location. After two restarts server accepted the files and I went on restoring msdb and user databases. But I'm sure I must be missing something and there must be better way to do this but i haven't been able to find anything relating to this problem. Thank you for your input. mojza
View 1 Replies
View Related
Oct 31, 2005
attempted to move my Model and msdb database files to a different drive on the server. I did the following:
* Restarted SQL Server 2K with the -T3608 startup parameter
* successfully detached msdb and model DBs
* moved the msdbdata.mdf, msdblog.ldf, model.mdf and modellog.ldf
files to their new homes
* successfully reattached model and msdb
When I take away the -T3808 startup parameter and restart SQL Server, I do not see the model database in the db list in enterprise manager eventhough my 'attach' seemed to work. Now I get the following error when i try and query against the Model database using query analyzer:
"Could not locate entry in sysdatabases for database 'model'. No entry found with that name. Make sure that the name is entered correctly"
Also, when I look in the sysaltfiles table in Master, I see no reference whatsoever to the Model DB
What did I do wrong?
View 1 Replies
View Related
Jan 11, 2006
What would be the best Recovery Model for: a database which is 4 gig in size and imports via MSAccess queries and also stored procedures approximately 400,000 meg of data each month (and some other update queries are run against it) and it is also queried off of for totals on weekly basis?
The problem is that the SQL Server box only has 512 meg of memory and the tranlog on this database grows tremendously each import and when update queries are run against it. This tends to slow things down a bit on our other databases. We are getting a new SQL Server box but until then, what would be the best recovery model? I currently have it as Bulk-Logged and allow the tranlog to grow by 10% (with a base of 250 meg). The tranlog grows to up to 5-10 gig and in order to shrink it, I have to change the recovery model to Simple, and then back to Bulk-Logged in order to shrink it (I've tried all the dbcc shrinkdatabase, dbcc shrinkfile, dbcc showcontig, and dbcc checkdb commands as well as BACKUP LOG dbName WITH TRUNCACTE_ONLY and nothing will shrink it unless I change the recovery model to simple.)
View 2 Replies
View Related
Jan 30, 2006
hi guys,
i have the following category table...
CREATE TABLE [dbo].[categories] (
[cat_id] [int] IDENTITY (1, 1) NOT NULL ,
[dModified] [datetime] NULL ,
[dCreated] [datetime] NULL ,
[cat_parent_id] [int] NULL ,
[cat_lft] [int] NULL ,
[cat_rgt] [int] NULL ,
[cat_image] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[cat_sort_order] [int] NULL
) ON [PRIMARY]
GO
i now wish to add a levels column which indicates (funnily enough :)) which level each record is on starting from 1.
is there a query i can use which will output for me the levels based on the child/parent relationship?
regards
mike
View 3 Replies
View Related
Sep 6, 2006
I try to create relational database but i cant develop a model. after i create a database i will create an OLAP cube for my project.i dont want to complex model but i cant create a simple database. it may be marketing, CRM or another. i dont know what fields and tables i should create. i saw sample databases such as northwind and foodmart but i must create original database for my project. please help meeeeee
View 1 Replies
View Related
May 17, 2007
Hi,
I am finding nice examples data model of CMS. Do you know any link to data model phorum or any link to data model examples(nice)?
Thanks
View 3 Replies
View Related
Jun 16, 2008
Why would we need to know the data model to write queries?
Sorry but its been a long time since I've worked with databases and I don't remember!
Could anybody lead me in the right direction please?
View 1 Replies
View Related
Jun 16, 2008
Why would we need a data model to write queries?
Sorry but its been a loong time since I've worked with databases and I barely remember.
Could anybody lead me in the right direction please?
Thanks.
View 1 Replies
View Related
