Data Warehousing :: Can Use Dimensional Model For OLTP System?
Jul 9, 2015
Question: Is it feasible to use a star schema dimensional model for an OLTP system that incurs few (750 per day)Sales Orders transactions?
Background: My customer wants to replace an existing OLTP system database because it runs on Oracle and their in-house expertise is in SQL Server. Â The original database developers that designed the Oracle DB have apparently retired. Â The Oracle database has been over-normalized, to say the least. Â The number of sales orders being entered daily is small: about 500-750 per day. Â These entries are done at the five clerks' convenience, from a paper form, and are very unlikely to ever be entered in quick succession. Â Nothing else gets regularly entered into this database except for the occasional change to a customer, but new customers are very few and far between. Â
I've designed a star schema for the replacement database with the Sales Order Header and Sales Order detail table combined into a single 'fact' table, and I've introduced some duplication into dimension tables (like customer) in order to eliminate some of the joins (and confusion) that were built into the original database.
I've never tried this before. Â Is there any reason this would not or should not work?
View 5 Replies
ADVERTISEMENT
Nov 24, 2014
How to write Stored Procedures to load the Data Model from OLTP to DWH ?
View 9 Replies
View Related
Nov 4, 2015
I have been looking at implementing a tabular model based on an OLTP database that's not dimensional. I know that this is possible but during my proof of concept I have encountered numerous problems ...
The things that I have run into are: After setting up the relationships I have found that measures filter context don't propagate along the relationships as I would expect. if the measure is coming from a target table and not a source then an ALL member is returned ( as in multi dimensional when a dimension isn't related to a measure group). Given the lay out of an OLTP database this will be hard to avoid.
One thing I have done to try an mitigate the above problem is to combine the tables used for measures in a view and using that in the source to connect to the rest of the tables. however due to the tables being of different grains this has then created duplication in some of the keys and measures. so the keys cant be used in relationships and the measures aren't accurate.
Are these things other people have come across? or should I give up the ghost and just recommend using dimensional models for the source? is tabular just geared towards a DW the same as multidimensional?
View 2 Replies
View Related
Aug 21, 2006
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
View 28 Replies
View Related
Jul 23, 2005
Greetings All, I was wondering if any of you would share some of yourexperiences regarding the task of loading a data store from an Oltpsource. We are using Analysis Services in a BI product that requiresdata to be pulled from one of our products, an OLTP database. Thedesign is to first run an ETL process from the OLTP source into anoperational data store, from here Analysis Services will pull its datato do its thing. Now, for small OLTP databases (< 1Gb) the storedprocs I have written to do the extraction works well, it is relativelyfast and efficient. However, we have a few databases that are 10Gb'sand the load could end up taking several hours. During this long loadthe OLTP source may be in use and I want to avoid write blocks, or if Iwere to use "select ... NOLOCK" I could get dirty data brought over. Icould used BCP for some of the big tables or Bulk Copy but I wanted tosee if anyone has dealt with this issue and what their specificresolution was for their specific problem. It is my hope that byseeing how others have dealt with this I will be able to architect asolution for my specific problemRegards, TFD.
View 1 Replies
View Related
Oct 15, 2007
Interested in feedback from the SQL grand wizards (and would-be wizards) that haunt these forums.
Let's say you need to constantly stream data into an OLTP system. We are talking multiple level hierarchies totaling upwards of 300 MB a day spread out not unlike a typical human sleep cycle (lower data during off-peak, still 24/7 requirements). All data originates from virtual machines running proprietary algorithms. The VM/data capture infrastructure needs to be massively scalable, meaning that incoming data is going to become more and more frequent and involve many different flat record formats.
The data has tremendous value when viewed both historically as well as in real-time (95% of real-time access will be read-only). The database infrastructure is in it's infancy now and I'm trying to develop a growth plan that can meet the needs of the business as the data requirements grow. I have no doubt that the system will need to work with multiple terabytes of data within a year.
Current database environment is a single server composed of a Dell PowerEdge 2950 (Intel Quad Core 5355, 16 GB RAM, 2 x 73 GB 15K RPM SAS ) with an attached Dell PowerVault MD1000 (15 x 300 GB 10K RPM SAS in RAID 5+0 [2x7] w/hot spare) running Win 2k3 64-bit and SQL Server 2005 x64 Standard, 1-CPU.
I am interested in answering the following questions:
Based on the scaling requirements of the data capture and subsequent ETL, what transmission method would you find most favorable? For instance, we are weighing direct database writes via stored procedures for all VM systems versus establishing processes to collect, aggregate and stream CSVs into a specialized ETL environment running SSIS packages that load data and then call SQL Stored procedures to scrub and prepare for production import. The data will require scrub routines that need access to current production data, so distributing the core data structures to multiple ETL processing systems would be expensive and undesireable.
Cost is very important to the overall solution design. In terms of database infrastructure, how would you maximize business value while keeping cost as low as possible? For instance, do you think there is more value in an ACTIVE/ACTIVE cluster (2 x CPU licenses) where one system acts as ETL and the other as OLTP or would you favor replication of production data from ETL to OLTP or (vice-versa). With the second scenario, am I mistaken in thinking we could get away with a Server/CAL licensing model for the ETL server?.
Are there any third party tools that I should research that would greatly aid me here?
I appreciate all feedback, criticism, and thoughts.
Best Regards,
Shane
View 5 Replies
View Related
Jun 8, 2015
I have  a column in which Email data is available likeÂ
clicuanan@aspenms.com(M)
jteply@mac.com(M)
How to extract in the below format
clicuanan@aspenms.com
jteply@mac.com
tjones@jpmc.com
View 4 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 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
Aug 15, 2000
I am preparing for the exam anybody know of any braindump sites that have data
warehousing braindumps?
View 2 Replies
View Related
Jun 17, 2000
Hello,
I am new to SQL Server. I want to know about DATA WAREHOUSING AND OLAP. Please help me.
View 3 Replies
View Related
Mar 30, 2001
Can anyone suggest a good resource that outlines data warehouse solutions using SQL 7.0 or higher?
View 1 Replies
View Related
Apr 5, 2007
Hi,
I need to implement/set up the Data warehouse/Data mart in one of the department in my company by using SQL server 2005. Do any body knows the steps what I need to follow?
It will be more appreciate that, if any body gives some of the links which will help me to do the implementation/development of the same.
I do have the basic idea however I may face some of the difficulties when I start such as, does the SQL server reporting service allow the end user to customize the report based on their needs etc.?, so any of them having experience in this field please reply me.
Thanks
Ajith Nair
View 1 Replies
View Related
Sep 24, 2015
I am working on to create a data warehouse. I have made a database which will be the data warehouse and will consist of dimension and fact tables. I know that other than dimension and fact table a data warehouse should also consist of a meta data, now my question is what should be the structure of metadata and all the information it should have?
View 2 Replies
View Related
Apr 4, 2007
I'm running SQL2005. The application running under SQL are a small accounting software and Microsoft WSUS. The file that is over 28G is the system model log file (modellog.ldf). I tried shrinking it and that only shrunk it by 2G. Is the model log file supposed to get that big, because the primary model data file is only 1Mg. How do I fix this? Can I delete the model log file?
Thanks
Willie
View 5 Replies
View Related
Sep 20, 2002
Hi:
I am planning to give the microsoft 70-019 data warehousing exam. I am not able to find any good resources to prepare for the exam. Please help!
Thanks in advance
View 1 Replies
View Related
Jan 9, 2006
Hi
We are starting with designing a datawarehouse for my company. I have done some reading on the concepts and steps involved, but what I am seriously lacking is some examples. I'd like to read through some real examples of data warehouses that worked including the full design diagrams.
Can anyone direct me to some good sites for this?
Thanks,
TeaaZA
View 1 Replies
View Related
Jul 3, 2007
Hello,Can I import an OLTP (Reltional DB) as a Data Source into SQL ServerAnalysis Services 2005 and then use the Cube Wizard and the new DataSource View feature to create the OLAP model ?Or do I have to first design an OLAP Data Warehouse with a Star Schemaand then import this DW as a Data Source into my Analysis ServicesProject.With SQL Server 2000 , OLAP would be the way to go..but with SQLServer 2005 , it seems as though the wizard and data source viewfeatures do half the work for you.I have an OLTP DB and am not sure which route I should take ! Anysuggestions / input would be much appreciated.Thanks in Advance...RegardsRusszee
View 1 Replies
View Related
Nov 7, 2001
So every database i created has no system stored procedures. I didn't know how that could happen as I am not the person who installed sql2000. What should I do to get all the system stored procedures back?
TIA
View 1 Replies
View Related
May 12, 2004
Hello, everone:
Anybody can offer technical information about DTS appied in data warehousing? Website, tutorial, forum and books will be great. Thanks.
ZYT
View 1 Replies
View Related
Mar 12, 2008
I created a .net console application within which I connect to a sql server (not local) as follows,
string conn_str = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=QuantEquitySql;Data Source=server name";
SqlConnection conn = new SqlConnection(conn_str);
conn.Open();
.
Everything works fine when I run it from my computer. When I try to run it from a network share I get the following error,
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I am using Visual Studio 2005 and .Net framwork v2.0.50727.
Does anybody know a fix to this problem?
Thanks
View 3 Replies
View Related
Jun 20, 2007
I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :
The Hidden expression for the table €˜table1€™ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Here is the code :
************************************************************************
Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string
i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xxmydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()
for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j
rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function
******************************************************************
Why do i get the error message ?, is this related to Code Access Security issues with .net framework. if yes
how do i set the Security so the report server or report manager allows embedded custom code to be executed. Any advice ?
Chi
View 7 Replies
View Related
Jan 3, 2008
Hi
I hope you can help as I am really scratching my head on this one. I am pulling together an assessment of the Disaster Recovery readiness for an organisation I am working at. Part of the assessment I am doing is the recovery model of each of the databases.
I have scripts that are already pulling lots of data from 40+ servers and 500+ databases. However, I cannot seem to find anywhere within the MASTER or MSDB or the database itself where the Recovery Model flag is held. Obviously I can right click on the database and click properties and it is there, but I need to automate this task (as it will probably be a weekly assessment).
I have checked sysdatabases and almost every other table, but nothing obvious as to where this flag is.
Any ideas?
View 6 Replies
View Related
Aug 4, 2015
How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW.  PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.
View 3 Replies
View Related
Jul 23, 2015
How to use Stored Procedures in SSIS?
View 2 Replies
View Related
Jul 23, 2015
How to use temporary table in SSIS ?
View 2 Replies
View Related
Jul 21, 2015
Is it possible to write a SP (Automate) to generate STATISTICS on any database and then use the output to create the stats on that database.
I ran the tuning adviser and it suggested indexes with lot of STATISTICS on the dev environment. This dev environment is replicated in several other environment with data size in these environment varying. I would  like to know if I can create a SP which generates STATISTICS information pertaining to specific database environment for the query in question for tuning.Â
View 9 Replies
View Related
Aug 24, 2015
I have a large fact table spread across tens of partitions (appx. 1TB each). I found that the business does not need much of the columns in the table. So, as an optimization action, I decided to get rid of these un-needed columns.What is the efficient way to achieve this? Can I simply drop these columns from the table, or use a new table with the reduced structure?
View 2 Replies
View Related
May 2, 2015
I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
1. XML
2. Spatial
3. Clustered
4. Non-Clustered
View 2 Replies
View Related
Aug 11, 2015
SSRS : I want to add parameter to my Cube from report is generating ...
View 5 Replies
View Related
Jan 18, 2008
Hi,
I've written this code multiple times now. But for the first time i get an error at the line underlined. My procedure runs perfectly when i execute it through Sql Query analyzer.
plzz help.. Its urgent and am unable to find the reason for this error "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"
Thanks !SqlConnection conn = new SqlConnection(DbConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "dbo.rqryTradesPRR";
cmd.Parameters.Add("@COBDate",SqlDbType.DateTime).Value = "2002-10-31 00:00:00.000" ;
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
// have written something here
}
Thanks in advance !
View 2 Replies
View Related
Jun 22, 2015
I have a table that is increasing quite largely each day. By now, I have average 300 million of records over 2.5 years. Before we received our new interface, the data we received was aggregated and thus not that big.The problem is that the table is so huge that I cannot use the Slowly Changing Component. I was thinking about making a temp table where I load the incremental data before I load it into the final data mart table.Based on this temporary table I use a script to compare the temp data with the already existing data in the data mart. However, this requires a compare of each records (300 mil records).
View 3 Replies
View Related
Nov 2, 2015
I have a SQL VIEW with col1, col2, col3. I need to add a new column to the view col4 coming from a TABLE in SQL Server.
View 4 Replies
View Related
