I have an OLAP server and would like to use my Chart FX software without having to purchase the OLAP extensions on the server due to budget restraints (ouch).
I've heard that it is possible (although limited) to attach toan OLAP cube using SQL select statements (not MDX).
Basically, I would like to pull the OLAP data in the relational sense.
Is this possible? If so, are any good articles on this subject?
I'm new to OLAP and would like to transition slowly.
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.cmdUpdate = New System.Data.SqlClient.SqlCommand Me.cmdGetAll = New System.Data.SqlClient.SqlCommand Me.cmdSelect = New System.Data.SqlClient.SqlCommand Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand Me.OdbcSelect = New System.Data.Odbc.OdbcCommand Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "this works fine" ' 'cmdUpdate ' Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _ "Holder)" Me.cmdUpdate.Connection = Me.SqlConnection1 Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing)) ' 'cmdGetAll ' Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.cmdGetAll.Connection = Me.SqlConnection1 ' 'cmdSelect ' Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE (CertHolder = @CertHolder)" Me.cmdSelect.Connection = Me.SqlConnection1 Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder")) ' 'OdbcConnection1 ' Me.OdbcConnection1.ConnectionString = "This works fine" ' 'OdbcGetAll ' Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.OdbcGetAll.Connection = Me.OdbcConnection1 ' 'OdbcSelect ' Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE CertHolder = @CertHolder" Me.OdbcSelect.Connection = Me.OdbcConnection1 Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder")) ' 'OdbcUpdate ' Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder" Me.OdbcUpdate.Connection = Me.OdbcConnection1 Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub Protected WithEvents btnSave As System.Web.UI.WebControls.Button Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList Protected WithEvents txtName As System.Web.UI.WebControls.TextBox Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox Protected WithEvents txtState As System.Web.UI.WebControls.TextBox Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Our OLAP environment involves an ETL/Data Warehouse/Data Mart server and a cube publisher server. We would like to learn how to automate the Archival/Restore of OLAP databases. We are currently doing it manually though OLAP Manager. Any help would be appreciated. Thanks. James.
-- James E. Bothamley Senior Database Administrator Dave & Buster's, Inc. 2481 Manana Dallas, TX 75220
Work Phone (214) 904-2296 email jbothaml@DaveAndBusters.Com
"Once in a while you can get shown the light in the strangest of places if you look at it right"
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
On the subject of Data Warehouses, Data Cubes & OLAP….I would like to speak frankly about Data Warehouses, Data Cubes andOLAP (on-line analytical processing). Has it dawned on anyone elsethat these buzz words were created by some geek who decided to take astab at marketing? Knowing that to the backwoods manager who knowslittle of technology that new innovative names for old concepts wouldhelp to sale their products.I mean seriously, what is the story here? In a nut shell, and pleasestop me if you disagree, but isn’t a data warehouse simply adatabase? Can’t you do everything on a conventional databaselike SQL Server, Oracle or DB2 that you can do on these newproprietary Data Warehouse constructs? I mean who are they trying tofool?Take a look, for instance, at Data Cubes. Who hasn’t noticedthe striking similarity between data cubes and views used in all themore robust databases? Also, what about OLAP? OLAP is nothing morethan a report generator. There’s nothing you can do with thesemillion dollar price tagged Data Warehouse total solution packagesthat I can’t do with SQL Server, Oracle or DB2…for thatmatter Microsoft Access.As an example some sales people for Metadata Corporation has the VicePresident of I.T. in Nashville, for Healthspring, sold on their totalsolution data respository which is such a scam. All they had to dowas throw a couple of buzzwords at him and they have him hypnotized.Personally, I feel that these kinds of marketing practices undermineour industry. It helps to unravel what little standards orconsistency we have. What do you guys think?Stuart
I am attempting to pull in data from a flat file data source that contains dates in the following format "01012007 10:22" which translates to Month Day Year and Military Time. I want to turn this into a DateTime format so that I can insert it into the proper column. I have a SQL statement which will do this (see bellow), but I can't figgure out how to run the statement on the data before it reaches its destination.
I have a project which spans multiple servers and aggregates literally billions of rows of data into a much smaller and manageable result set which I store on another server. There are two stored procedures which take up 99.9% of the processing time. Each one of these SPs are estimated to run over 3 hours a piece (gives you an indication of how much data there really is). With in this SSIS package, I have two control flows, one for each SP. When I run the SSIS package from VS2005, I can see that there is a pre-execute phase which takes about an hour abnd a half to complete, and then move on to executing. My question is, what the heck is this, and what is it doing? I know it validates the sql in the procedure, but does it actually run the SP during that pre-execute phase? If it does, is there any way to get around that?
SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:
UPDATE Pearson_Load SET Process_Flag = 'E', Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State' WHERE (Address_Line_1 = ' ' OR City = ' ' OR State = ' ') AND Process_Flag = ' '
Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.
Hi, Writing a report that displays total hours, etc for each day in a given month and I'm having trouble comking up w/ an elegant solution. Tables are set up like this: table1 (id, start_date,end_date,submitter) table2( id,table1_id,start_time,end_time, etc) Each record in table2 is a child to a parent record in table1 and there may be multiple children for each parent. The "start_time" and "end_time" fields are datatype "datetime" and are stored with actual times (not 12:00:00 AM"). I need the report to display the values for each day in a gridview, etc so I can act upon them from the "RowDataBound" sub (more math, etc). The problem that I am running into is that for records that have multiple children for a given day, I need to sum those results and display the summed data as a single row within the GV - can't seem to work that out - I keep getting every child record displaying as its own row in the GV. Any thoughts? I'm sure it's probably fairly simple but I am still a beginner...thanks as always
I am new to SQL, as old as it is. I am not new to programming Inormally just use Access.I have two tables for a little project manager I made. After updates Isent an email to the user. I need to populate the user based on the"Assigned To" field I use, but I only log the username and not theemail address. Is there a way to associate the "Assigned To" to theUser's account in the "User" table so that I can pull thier emailaddress through code?ThanksChuck
I have the report sever running on windows2003 with sql standard server 2005. However, I' m trying to create a report that pull data from a different server with sql express. Could I do this? If so...any links, examples, suggestion I can take a look at.
I am trying to pull data from SQL Server 2000 database onto my Pocket PC and the simple query works on Northwind sample database but does not on another custom build database. I did test the statement in the SQL Query Analyzer and it works.
The error I get is: "Failure setting up a non parameterized query, possible incorrect SQL query."
I know the code is right - thanks to Rory B's screenscasts. Any guidance will be greatly appreciated!
Thanks!
Moldau
Here is the code:
private void RdaPull()
{
try
{
//Create the database
if (File.Exists("\My Documents\Test.sdf"))
File.Delete(\My Documents\Test.sdf);
SqlCeEngine engine = new SqlCeEngine();
engine.LocalConnectionString = localConnection;
engine.CreateDatabase();
engine.Dispose();
//Initialize RDA Object
SqlCeRemoteDataAccess rda = null;
rda = new SqlCeRemoteDataAccess(rdaUrl, localConnection);
rda.Pull ("Customers", "select * from Customers", rdaOleDbConnectionString);
I am starting using OLAP service. The first problem the first day I use it.
I setup a system DSN for OLAP under ODBC source. I use SQL driver to create a data source to a SQL server. I am using SQL SA login account. Then I go into OLAP manager create database. Then in the library I set up a data source using the DSN I created before.In the General Tab I use OLE DB for ODBC provider. After this I try to create a new cube using the wizard. I got error the message that the SQL login is invalid. I double check it is right login account. I failed several times. Then I tried directly create data source under library and not using the DSN I created before. The first time it failed again has the same message. Then the second time when I create data source I use NT authentication instead SQL login. Now everything went fine. I was able to create a cube. But I still wondering what is preventing using SQL login account when creating data source.
Hello,If I wrote the next ebay (yes I know, yawn-snore) and I had a databasewith 5 million auction items in it, what would be a really goodstrategy to get a search done very quickly? Would it involvesomething called OLAP and/or "data mining"? The only technology I amfamiliar with is simply SQL Server databases with stored procedures.I think I'd be guessing correctly and say that this technology simplywouldn't be fast enough *on it's own* to do super fast queries againstmassive amounts of data.Any insights would be of great interest. Thanks.-Frameworker.
Hi, In my application i am storing the data in a database(server A). And i have a scenario where in a middle server(server B) is there in which i need to create a table and pull the data from one of the tables from my original database server(server A) into second database server(server B). Where can i get the code to write a job script which can connect from server B to server A and pull the data from the table and delete that data from the table in server A. Can any give me the code or link where i can find the code. Please help. Thanks
Good Afternoon, I have recently been tasked to come up with a means by which to create SQL data pulls based on user entries. What I mean by this is, I will have a pre defined SQL data pull set up and the user can then select the criteria upon which to pull said data. So, say I want to display a table where one of the colums has is the "Year". I dont however want all years of data pulled, only 2005 - 2006. So, in form field 1, I could select start year as 2005 and in form field 2, I could select end year as 2006. This would then give me all data from this table based on that year range, or whatever year range I select and submit. Is there a way to do this in ASP? I have been reading up on UDF with SQL, but I cant find anything about linking it through the use of web forms or through ASP. Or if there is another way to accomplish this? Any assistance is very much appreciated!
Hello, I am trying to pull run this sql statement but it's bombing out at the comm.ExecuteNonQuery();. ..
Could someone help me figure this out.. Ex.System.Int32 bum = System.Convert.ToInt32(Request.QueryString["dum"]); SqlConnection conn = new SqlConnection("Data Source=**********************");SqlCommand comm = new SqlCommand(); comm.Connection = conn;SqlDataAdapter myadapter = new SqlDataAdapter(comm);DataSet myset = new DataSet(); conn.Open(); comm.CommandText = "Select * from Order_Forms where (Order_Num = " + Session["dum1"] + " ) ";
Hi, I am trying to pull e-mail addresses of certain users from a list of all the users in our company. I have two tables. One contains a single column of only the users I need. The other table contains every user and their e-mail address. Could someone tell me how to pull this data from these two tables. I am new to SQL Query and have been trying to figure out these JOIN statements, but nothing I am doing seems to work.
I run a query from Excel to pull the data from SQL server and I have created another worksheet to add my columns for different analysis and costs. When someone add another line in SQL against an order or a customer then manually added columns data goes out of sync.Is there anyway when I refresh the RAWDATA or anything changes on SQL table then it should sync my formatted worksheet.
I am trying to create a query that will pull a variety of data from three different tables. I've had to join four tables because one set of data is in a completely different table.
I am expecting one row of data but instead I get 4 rows. I suspect this is because I am joining a table indirectly. Here is the code:
There is only one applicable row in SDH, but four rows appear when I run the query. All four rows are identical except for the GL.GLCode column, which lists the three GL codes associated with the DeductionItems table. The fourth row is a duplicate of the third.
but when i form my statement with an inner join, i miss records, with the outer, i get a ton of records (more than both tables combined). i have been working with sql for about week now and can do simple queries but this is beyond me at this point. any help what so ever would be greatly appreciated.
I am working on an application to retrieve data to the windows ce 3.0 handheld from the Sql server database. I am able to retrieve the data from the server using RDA pull method and able to see the data on the local handheld database. To display the retrieved data in the dialog to the user, it requires to query the local databse and get the information and then display the information, which is taking some time.
My question is, to reduce the time and improve the performance , instead of pulling the data to the local table, is there any way to pull the data and have it in memory and display the details to the user?
To develop the above application, I used some of the code to pull the data from the server from the sample application C:Program FilesMicrosoft SQL Server CE 2.0SampleseVCeVCReplRdaHPC. I am developing the application for windows ce 3.0 device using eVC 3.0
I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult to isolate the guilty one by trial and error! Coud someone please help?
I am building RDA process between SQL server and SQL server CE. Since when we use the method 'SqlCeRemoteDataAccess.pull()', the data will be pulled down from SQL server to CE server and a new table is created with some addtional columns. As I only intend to pull the data and never push it back to SQL server, if I pull the data down for the second time, there will be an error saying that table is already existing. I wonder whether I can pull the data to an existing table everytime I do the RDA?
When I execute the package in Debug mode, it works.
I then import the package into SQL Server 2005 and it is showing under Stored Packages --> MSDB - AS400Package
I can right click and Run Package from there and it works fine.
When I set the package up to run as a job, it fails. What I have noticed is that the Sign On that I use in the package shows that there was in "invalid login" after the job fails.
I hope I have this post in the right place and hope I have left enough information behind to help. If not let know what else I need to post. I have only been using Business Intelligence Development for a couple days and SQL Server 2005 for about a week or so. All of the SQL DTS packages that I have created seem to be working fine with their jobs as well as my stored procedures.
The drillthrough in my cube is working fine except for the cases where the dimension member is null. For example I have the dimension PRODUCT (dim) - PROD_TYPE_CD (name = PROD_TYPE_CD || '-' || PROD_TYPE_NM) - PROD_CD -PROD_NM
So for if the data is like the following where the PROD_TYPE_CD is null : - (name = -FOOD) - 123 - bread
The drillthough is not displaying any data no matter I select the PROD_TYPE_CD, PROD_CD or PROD_NM in the dimension drop down altough if query the DB directly I found data to display in the drillthrough Any ideas why this is happening and how to solve it.
I prepared an OLAP cube for the report data source in the SSAS 2005. The OLAP cube consists of more than 20 dimensions and several measure groups. I then created the subset/view of the OLAP cube using the "Prepective" function and limit to not more than 7 dimensions on each of the subset. How do I reference the OLAP cube subset as the data source when developing the report in the report designer. Furthermore what is the advantage of creating multiple smaller OLAP cubes with less dimensions comparing to one big OLAP cube with several subset/view attached to it. Thanks.
After reading some threads on this forum about accessing OLAP data from SSIS via an OLE DB Source component (and trying it myself and getting that "pcrsstore.cpp line 325" error), I have worked around the problem by using a Script Task component.
This calls a .NET assembly I wrote that uses ADOMD.NET to get at the data using an MDX query, and then actually posts an event to a Notification Services instance via a stored proc.
Writing, deploying, and maintaining a .NET assembly is more than I wanted to do, but it was worth the effort since now I know a little about extending SSIS when I need to.
But what I really wanted to do was the following, all from within the core components of SSIS:
Import my OLTP data into my relational warehouse (already done using SSIS) Process the cube (already done using SSIS) Access the cube via MDX and post an event to Notification Services It's this last step that needed some special work because of SSIS's apparent limitation accessing cube data (although posting the event to NS would be easy because SSIS can call a stored proc on the NS application database). So I'm hoping that a future release or service pack of SSIS will give us some components to run MDX and get the OLAP data into the pipeline as if it were from any other source. Thanks for reading, and if anyone has any suggestions on a better way to achieve what I need please let me know! -Larry
