sql-server-2008,ssas,mdx,business-intelligence,msbi , Some questions about MSBI

Some questions about MSBI


Tag: sql-server-2008,ssas,mdx,business-intelligence,msbi

I have some questions to ask you:

  1. I have my DW and I want at the end to have reports where there are data calculated thus I have to make my cube (table of make and dimensions(size)) except that these calculate there how and where I may make them?
  2. Can i have more than one cube?
  3. Can i have more than one table of make in the same cube with the same dimensions?
  4. We must have a precise number for the dimensions in a cube?
  5. If ever we miss about things in the putting place our conception (table make and dimensions) we can go back to correct where that will be too late?

  1. During the implementation of the cube, i can create calculated measures.
  2. Yes, we can build several cube.
  3. Yes.
  4. It is the type of the analysis wished who fixes the number of dimension.
  5. Yes.

I found all a response to all my questions that's why i shared them with you.

But can we use a view as a fact table or we have to use juste a table?


INSERT single quoted value for all column types in SQL Server

Say you have a column of int (also bit, etc.) CREATE TABLE TEST (a int) It seems it doesn't matter if you insert value with '1' or 2 INSERT INTO TEST VALUES ('1') INSERT INTO TEST VALUES (2) Is there any side-effect if I insert/update values by single-quoting them (e.g.'value')...

Count number of occurrences of each month in db table asp.net linq

I have a table which contains a date col. I want to use LINQ query and count the occurrence of each month and display it like month, occurrence of month in the table 1=2(1 is month, 2 is number of occurrence),2=5,3=12 and so on using asp.net. Can somebody please tell...

Force date format regardless of culture

I have an issue with certain cultures (Spanish in particular), causing the date string, ToShortDateString(), to come in as dd/mm/yyyy where END_DATE between '" + asOfDate.AddDays(-30).ToShortDateString() + "' and '" + This is causing SQL sever errors because we get incorrect date literal strings such as this: where END_DATE between...

How to fix this logic?

i have a query with 3 variable tables: @result, @order and @stock. the logic is the stock qty must be allocated by lotsize (here i set=1) to all order based on priority (FIFO). the stock qty must be allocated till zero and the allocateqty must <= orderqty. the problem is...

Sql inner join with three tables

I have this problem, I am a newbie in SQL. I have 3 tables: TBL_PRODUCT: prod_id, prod_type, prod_code, prod_desc TBL_DOCUMENT: doc_id, doc_name, doc_expiry, doc_term, enable_amt TBL_DOCUMENT_GROUP: grp_id, prod_type, doc_type, doc_id For every document, there are corresponding products, one to many. What I can't figure out is how can I display...

SQL Server 2008 R2 syntax error

Could somebody please tell me why this is causing a syntax error in SQL Server 2008 R2? It works fine in SQL Server 2014. SELECT CustomerId, RequestDate, SUM(InitialActualCount + (KeyCardCount * x.Activity)) OVER (PARTITION BY CustomerId ORDER BY RowNumber) AS TotalActualCount, RowNumber FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerId...

JPA annotation for MS SQL Server 2008 R2 IDENTITY column

I want to know proper JPA annotation setup to handle MS Server 2008 R2 IDENTITY column. MS Server 2008 R2 (RTM) doesn't support SEQUENCE, but the IDENTITY gives auto increment when I tested by raw sql. However, by JPA I can't make it work by any strategy (IDENTITY, AUTO, TABLE)...

SQL Server 2008 - Replace Text Values in Column with Values from Another Table

I've tried flexing my Google-fu to no avail so here I am! Unfortunately I cannot change anything about these tables as they are coming out of an application that I have to report out of. In SQL Server 2008, I'm trying to replace multiple values in one text string column...

How to retrieve a part of a stored procedure's header?

I have a C# WinForms application that manages stored procedures used by different services. What the users see is something like that: exec stored_procedure_name param1, param2, param3 And since param1 doesn't mean anything to them (they can't see the stored procedure), I would like to present to them small descriptions...

Access filter on time

I have a table with a time in a datetime field on an MSSQL2k8R2 server. I have linked the table in Access 2007. The table was migrated with the migration assistant from Microsoft. i.e. the Managemantstudio will give on SELECT TOP 3 [AbZeit], [AbBrP] FROM [dbo].[tSollFahrten] the Result AbZeit AbBrP...

MS SQL Computed column

I want to create a column based on COUNT(*) on another table, and when a record is deleted from that table it should decrease the value in this new column and vice versa. So, here is the query: SELECT COUNT (*) FROM dbo.Korisnik1_FakturaStavka GROUP BY dbo.Korisnik1_FakturaStavka.FakturaID And it returns this:...

SQL Customized search with special characters

I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

Script to add new columns to table [closed]

how to write a script to add 3 new columns to a stockout table in all databases in sql server

Title search in SQL With replacement of noice words [on hold]

I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

Get work time in minutes based on shift schedule

In production we have 3 shifts. Each Shift timing is described in table tbl_ShiftSched: WT - work time, PT - break time. ShiftTmID - schedule for 2 and for 3 shifts. I am looking for easy way to get work time in minutes having start and end time. For example,...

Using GROUP BY in SQL Server

With this query I got 2 columns of distinct values SELECT DISTINCT Transport.ID, Transport.Address FROM Cars LEFT JOIN Transport ON Cars.TransportFrom=Transport.ID ORDER BY address Now I need get one more column with sum: SUM(CASE WHEN Active='True' THEN 1 ELSE 0 END) But I get error if I do queries like...

Table design for questionaire

We are working on an application which will ask users various questions, these questions can have branches, so they don't run in a straight order. for example, if the below is a structure of questions and where they can flow to: Question 2 has a branch if the user answers...

SQL Server : copy data from columns to another table with many columns

I have an existing table wherein I store my data that I got from another server. Now, I want to copy that data to one table wherein that will be my final table on where I could do the CRUD operation. Fist table (reference database) to restore to my TempTable...

SSRS re-sorts data meticulously sorted from the SQL query! Why?

I'm writing up a report using SQL Server Reporting Service via Visual Studio 2008. I have a stored procedure on a SQL Server database that returns a list in which I need the entry with value "F" in colulmn [Column] to be always on top, as such: F A B...

Convert row to columns SQL dynamically [duplicate]

This question already has an answer here: Pivot Dynamic Columns, no Aggregation 1 answer Simple way to transpose columns and rows in Sql? 2 answers I have a table Dev with the data below YYYMMDD Atest BTest CTest 20150525 100 200 300 20150526 110 210 310 20150527 120 220...

Zeroth Row for a OLTP table

I have come across a OLTP Database where all the tables has a default row,value 0 for a primary key and other columns with its default value or a null value.I am not sure why is this row required in first place? can anyone explain what are the advantages and...

Use A Sub-Query With A Case Statement

This may be impossible to achieve, and if so how would I re-write my query to still achieve the same results? I keep getting this error: Msg 130, Level 15, State 1, Line 2 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. And this...

Calculating the duration between two dates in sql

I'm having some difficulty with an SQL query for reporting machine efficiency from a CNC monitoring system. The monitoring software records the duration the machines are in different states like machining, stopped, out of work setup etc. I want to total up the duration of these states for each machine...

Update Query With Inline

Is it possible to do an update query like this? It may not be, just a thought I had as to possibly a solution to my predicament of terrible data-structure. What I am trying to accomplish is: To update the table prodinformation with a count where the entrytype exists in...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

SQL query for details sum on each [closed]

I want to display sum of sales for each month and also all the year For e.g. : january 2000 feb 5000 ... decem 4000 And total sum = sum of january to december. for the given year...

SQL Server query syntax with single quote

I have in my database KIA car model like CEE'D. How to write right syntax to get all car where model = CEE'D Now I am using semipart of model SELECT make, model FROM Cars WHERE model LIKE '%CEE%' But now I need to get by full model name. How...

Implement reference key in SQL Server

I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...

Microsoft SQL Insert into subset of table columns fails [on hold]

Can anyone help me find the column or columns in this table where data would be truncated? I am trying to execute the SQL insert below, but it is failing with the error: String or binary data would be truncated. Which I understand means a value is too big to...

Retrieving Online Users By Login and Logout Records

Consider UserLog Table as above. I save login or logout operation of users into this table. how can i retrieve Online users and Login Time?...

Grouping by similar categoies over time sql

I looked around for awhile, but couldn't find anything. I have a table that looks like this: DATE | Shift | Parts Used 1/1/15 1:15.....1........1 1/1/15 2:06.....1........2 1/1/15 3:45.....1........3 1/1/15 7:33.....2........1 1/1/15 8:14.....2........2 1/1/15 9:00.....2........3 1/1/15 23:01....1........1 1/1/15 23:55....1........2 I would like to group by each individual shift. UNFORTUNATELY shift...

Join SQL query Results and Get-ChildItem Results

Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...

TSQL - Error in stored procedure due to conversion failure

I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...

SQL Server DATEPART for year and month

I have a call in SQL Server 2012 that I need to have working on Server 2005 and 2008. FORMAT(Date, 'yyyyMM') = 201501 is what I am currently using and need to use DATEPART older versions of SQL Server but cannot see a way of doing it easily. Any help...

how to sort alphanumeric data in sql..?

i have items table which is having Size as follows: 1sq.mm. 1.5sq.mm. 0.5sq.mm. 2.5sq.mm. 0.75sq.mm. 4sq.mm. 20mm 25mm 20mtr 75mm x 50mm 100mm x 50mm 100mm x 100mm 75mm x 75mm i wanted to display it has 0.5sq.mm. 0.75sq.mm. 1.5sq.mm. 2.5sq.mm. 4sq.mm. 20mm 20mtr 25mm 75mm x 50mm 75mm x...

Split comma separated string table row into separate rows using TSQL

Say I have a query that returns the following ID SomeValue 1 a,b,c,d 2 e,f,g Id like to return this as follows: ID SomeValue 1 a 1 b 1 c 1 d 2 e 2 f 2 g I already have a UDF calls Split that will accept a string...

Adding a Computed column with After clause

I am trying to create a computed column after a specified column. But the SQL gives syntax error for the query below. Please help me with the correct syntax/way to do this. ALTER TABLE service_ServiceClass ADD LichenClassName AS ([dbo].[UfnGetServiceClassName]([Id])) AFTER Description ...

Force Update from a filtered DataGridView to Database

I have created an application to fix a date conversion problem when transferring data from the machines. The conversion works great. I create a DataGridView that contains all records. I then sort the DataGridView to display the data that needs updating using SqlDataAdapter and DataSet. I then make the changes...

Displaying SQL Data For Same Day

I need to be able to display information where the search parameters show same day. I thought this query would cut the mustard, however it presents an error of Msg 241, Level 16, State 1, Line 13 Conversion failed when converting date and/or time from character string. And this is...

Is it possible to iterate a calculation across distinct values in a column?

I want to iterate a calculation which takes counts of people that fit particular criteria and calculates percentages based on those counts across distinct regions. My code: USE Database1; GO declare @ShouldRegister as float declare @Registered as float SET @ShouldRegister = (SELECT COUNT(*) FROM dbo.TABLE WHERE field1 in.. AND field2...

How to use OR in join while using Entity Framework

Can any one convert following SQL statement to Entity Framework C# or VB.net for me? SQL statement: select t1.*, t2.* from tblWISTransacs t1 inner join tblWCBTransacs t2 on t1.TicketNo = t2.TicketNo or t1.TicketNo = t2.customernumber ...

I need to dynamically set the date in a where statement to only pull results from a table to occur in County's fiscal year

Here is my current code: SELECT nature, inci_id, calltime, agency, gp FROM inmain AS inmain WHERE (calltime >= CONVERT(DATETIME, '2013-07-01 00:00:00', 102)) AND (calltime <= CONVERT(DATETIME, '2014-06-30 23:59:59', 102)) ORDER BY nature, calltime I need this to pull data at the end of every fiscal year without going in and...

Concatenated string as parameter argument value does not give output in stored procedure

I am using SQL SERVER 2008 management studio. I have a stored procedure which accepts several parameters. One parameter name is @recvemail with default value as 'B'. If the value of @recvemail is 'Y' then all the users who opted for email news letter should be displayed in the output....

SQL compute AVG time, but the given time is in NVARCHAR format

I'm having some trouble in converting nvarchar to int. What I want to do is to get the longest and shortest time of a column and then compute the AVG time, however the column is nvarchar....

Intersect Select Query in MDX

I want to have customers intersection in two mdx querys. 1. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Coca-Cola] ); 2. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Fanta Orange CSD] ); My attempted...

One identifier for set of values

I have this two columns. One is filled with some data, second is null. col1|col2 --------- null| 72 null| 72 null| 72 null| 33 null| 33 null| 12 null| 12 null| 55 null| 72 I want to generate values for col1 that will gather and group values from col2. So...

copy table and drop it

I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...

How to insert into a table variable with a dynamic query?

I tried to develop this stored procedure using a temp table but that wouldn't work so I switched to using a table variable. I need to execute an interim dynamic query into the table variable and then I use that table variable to execute the final query. The problem is...

SQL select fields containing uppercase characters

I have a simple SQL Server 2008 update query using LOWER(FIELD_NAME) that should only update where FIELD_NAME contains uppercase characters. UPDATE TABLE SET FIELD_NAME = LOWER(FIELD_NAME) WHERE FIELD_NAME *contains uppercase characters* How can I select only columns with uppercase characters in the where clause?...

What is the execute command to use here

protected void Page_Load(object sender, EventArgs e) { string sqlConnectionString = @"Data Source=phriz-webapp01;Initial Catalog=PFTracking;Integrated Security=True"; string script = "if not exists(select * from sys.servers where name=N'CNCTC-WEB01')begin exec sp_addlinkedserver @server='CNCTC-WEB01'exec sp_addlinkedsrvlogin 'CNCTC-WEB01','false',null,'svc_Phils','[email protected]' end INSERT INTO [PFTracking].[dbo].[TempTable] SELECT c.[pf_id],a.[RequestDate],c.[pf_carrierUsed],b.[PiecePrice] * b.[PartQuantity] as [Amount]...