I have a cube that tracks sales by sales rep. In this cube, I have dimensions for SalesRep, Product, and Region hierarchies. I also have a Time dimension that provides Fiscal Year, Fiscal Quarter, Fiscal Month, and Calendar Date; the Time dimension also has an attribute showing the first day of the year for any given date (our fiscal year starts on a different date every year).
I have a report that passes StartDate and EndDate parameters back to the cube, and provides sales numbers by Rep, Product, and Region for that given date range. What I would also like is a field that provides YTD sales through the EndDate parameter.
This is a piece of cake for me to implement against the SQL tables, but I am pulling my hair out trying to determine the best way to implement with a cube. Does anyone have any suggestions?
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here  )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
When I try to preview the report , I get a error massege saying "report item expression can only refer to other report items within the same group grouping scope.
Fields!CBNonAccr.Value and Fields!TotNonAccr.Value come from one table. But HE_Non_Accruals.Value comes from another table which does not have a relationship with the first one. Its a seperate table. So I'm wondering how do I do this now?
I have a vision of a beautifully interlocked Business Intelligence system in which I load dimensional fact tables through SSIS, build aggregations in SSAS cubes, and quickly and easily generate SSRS reports using the cubes as data marts.
I am now a month into trying to implement this vision and Reality is stomping all over it. Primarily, I am running into issues with the "cube as a data mart" idea. Each cube is taking up an additional gig or two of space, report queries against them seem to take longer than a straight SQL query against my fact tables, and I am running into serious problems trying to pass time-related calculations such as Year-To-Date to my reports.
Has anyone else tried setting up a BI system that uses cubes as the primary drivers for their reports? Have you seen any benefits to doing it that way? Or should I generate most of my reports through the SQL tables?
I'm nearing the point-of-no-return in choosing my final methodology, and any feedback would be greatly appreciated.
I've got a report that is using a cube as a data source and I can't get the report to show all the data. Only data at the lowest level of the cube is displayed. The problem is that most of the data I'm concerned with is at higher levels. There's no problem with the MDX. I get the correct results when I run the query.
I'm using a table to show the results. I've also tried a matrix, but I get the same results. I'm using SSRS 2005 and SSAS 2000.
Anyone have experience with this? Am I missing something simple?
I've been doing research on the web trying to find if it's possible to create a Report Model that is based on a Data Source that is NOT SQL Server and NOT SSAS.
Specifically I'd like to be able to build a Report Model on a Web Server (or multiple web services).
Does anyone have any information relating to this? Based on my research it looks like there's a few interfaces that need to be implemented (such as ISemanticModelGenerator, etc.).
I did find a post relating to using ODBC data sources for a Report Model and the recommended solution was Linked Tables in SQL Server or UDM in SSAS. Both cases don't look feasible for a Web Service-based Data Source.
Has anyone been able to get time calculations to show up in Report Builder? I'm using the BI Wizard standard calculations for YTD & Period over period growth, but I can't figure out how to use them within Report Builder. They are available in Report Designer, however. I did find the info about assigning the calculations to a measure group. I tried that, but it didn't have any affect.
I am trying to get a date diff in a named calculation using inner join I am getting
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. #
I have checked online and have been able to get some of my codes right by using the where condition in place of the inner join, but I have not been able to work around the datediff one
(select DATEDIFF(day,TDate,[DID]) FROM [dv].[dbo].[Dail] as D inner join [dv].[dbo].[ANT] as A on a.AID = d.AID)
, Hi In this code how can I create a new data source and new data source view and model and structure that it run dynamic. In this code I have a lot of errors, that they are about server and database don€™t have in current code, In this code, first I should definition server or no?
How can I create data source and data source view and model and structure? Please say code of that, and guide me. databasename and srv is unknown. Do I add other reference with analysis services? Please explain about these codes: ************************************************************************ 1) RelationalDataSource dsNew = new RelationalDataSource( datasourceName, Utils.GetSyntacticallyValidID( datasourceName, typeof(RelationalDataSource)));
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
hi everybody, i want to create data source and data source view for data mining, with using C Sharp. i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully, and have i build data source and data source view, how to do it.?
Today I was making a few reports. When I tested the reports in Visual Studio, they worked great: I got the expected result. But when I deployed the reports to our reportserver the problem started. When I click on the directory in which my reports are deployed, I got my 4 reports. Till now everything worked correct. But when I click on a report to view the results it went wrong. I got an error: "Cannot create a connection to data source 'Live'" (Live is the name of our data source).
We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work. I also have tried it with all the roles assigned on my account, but then it still won't work.
When I modify the data source, and set it to another server en database it works. The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server. Maybe that is the problem?
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
In the following query, when an es.evstrname DOES have data in os.activity in (5,7), then the query returns the expected results. However, if there was no out of service break, then the query will return results, but there will not be any data returned for the last column of the select statement. This makes sense to me but I was hoping there was some way that I could have data returned. For instance, is there a way for me to make the query read the - (os.actualdeparttime - os.actualarrivetime) as zero so the column would still consider the first part of the equation? Or is there a way to write a subquery that would work around this?
Select s.ldate, es.evstrname as 'Run', d.lastname+', '+d.firstname as 'Driver', po.actualarrivetime as 'PullOut', os.actualarrivetime as 'OOS', os.actualdeparttime as 'IS', pi.actualdeparttime as 'PullIn', os.actualdeparttime - os.actualarrivetime as 'Break' (pi.actualdeparttime - po.actualarrivetime) - (os.actualdeparttime - os.actualarrivetime) as 'PayTime'
From Schedules S
Join eventstrings es On s.schid=es.schid
Join employees d On d.employeeid=es.employeeid
Join events po on po.evstrid=es.evstrid and po.schid=es.schid and po.activity=4
Join events pi on pi.evstrid=es.evstrid and pi.schid=es.schid and pi.activity=3
Left Outer Join events OS on os.evstrid=es.evstrid and os.schid=es.schid and os.activity in (5,7)
Where es.evstrname>=? AND es.evstrname<=? AND s.ldate>=? AND s.ldate<=?
Order by s.ldate, es.evstrname, po.actualarrivetime, os.actualarrivetime, os.actualdeparttime, pi.actualdeparttime
All es.evstrname will always have an 'event activity' (pi.activity, po.activity etc) of 3 and 4. Only es.evstrname that have clocked out of service will have data with os.activity in (5,7) (this is an out of service break).
I'm not sure if this is a format that would help, but here is some sample data. Currently, it returns the following:
Date Run Pullout OOS IS PullIn Break PayTime 10-1 101 10:00 12:00 12:30 16:00 :30 5:30 10-1 102 11:00 ---- ---- 17:00 ---- -----
I would like it to return this:
Date Run PI OOS IS PullIn Break PayTime 10-1 101 700 900 930 945 30 215 10-1 102 700 --- --- 945 -- 215
Hi, I created a SP which is supposed to calculate some values for me and return them as a resultset. I have a RequestTime field, and a ResponseTime field; This sp should calculate how much time does it take for us to respond to a customer's request. if it is more than a specific time, this sp should calculate the extra time and its fine base on a constant fine-per-extra-minute value. It should also calculate total fine for all records. To do so, I wrote it as this:
Code:
CREATE PROCEDURE up_responsetime @sdate smalldatetime, @edate smalldatetime, @TotalFine int OUTPUT AS DECLARE @Fine_Per_Min int DECLARE @Max_ResponseTime int SET @Fine_Per_Min = 3 SET @Max_ResponseTime = 120
SELECT ID,RequestDate,RequestTime,ResponseDate,ResponseTime, -- Calculate exceeded amount of time for each record DATEDIFF(minute,RequestDate+RequestTime,ResponseDate+ResponseTime) - @Max_ResponseTime as ExtraTime, -- Calculate fine for each record (DATEDIFF(minute,RequestDate+RequestTime,ResponseDate+ResponseTime) - @Max_ResponseTime) * @Fine_Per_Min as Fine FROM CusRequests WHERE RequestDate BETWEEN @sdate AND @edate --Calculate sum of all fines and return it in TotalFine variable. SELECT @TotalFine = SUM((DATEDIFF(minute,RequestDate+RequestTime,ResponseDate+ResponseTime) - @Max_ResponseTime) * @Fine_Per_Min) FROM CusRequests GO
but I have two concerns about this: 1- Calculated Fine field returns a negative figure if the ResponseTime is in the desired period of time. But I want it to return zero for such cases, and return only a positive figure when extra time was spent on responding to the request.
2- As you can see, there are many redundant calculation in this code, and this will affect its performance. I wanna know if there is any more optimized way to write such a code?
I have a formula that should use data from 2 slicers:
Spend per period (changing currencies & dates):=[Spend per period]*CALCULATE([Sum of Value],FILTER(Currency,Currency[Date]=[End Date]),FILTER(Currency,Currency[Attribute]=CurrencySlicer[Attribute]))
I managed to link the [End Date] from the slicer to the formula, however the [Attribute] field is not numeric so I can't duplicate the same methodology.Â
{FYI: Â Â End Date:=LASTDATE('Finish Date Slicer'[Column1])Â Â Â }
I assume that I need to build a formula to extract the data chosen in the slicer, and can't connect it directly to the slicer.Â
Input : 1) UPDATE TBL_SO SET QTY='10',Price='100' Where SONo='10' 2)UPDATE TBL_SO SET QTY='10',Price='100' Â Total=Qty*Price Where SONo='10'
Output: What I get? I tried both 1 and 2, It(Query) does not work on first time,I executed (Query) second time It works.Why?What I need?  How to work on First Time?
Hello: I would like to send data feeds to Sales Reps listing customers with their likelihood of purchasing a product from a certain Category. The data will basically contain Customer Name, Category, Likelihood Score (or Probability Score). When sorted in Descending order of Probabililty a Sales Rep can easily see the customers who are most likely to purchase at the top of the list and can call them.
I figured I would explore the use of SSAS data mining for this, and integrate it within an SSIS data flow (needed to send the data out to the sales reps).
I have not worked on Data Mining and so am clueless as to where to begin. I have been reading a book on it but most of the examples this far show me how the results can be viewed within AS, which is not what I am looking for.
Ideally I would like to run an algorithm against all our product categories for 2007 and store the results to a table. Then use that table as a lookup to get the probability score for specific categories that we are targeting in 2008.
I appreciate any advice for the experienced SSAS folks on this forum.
I want to implement population data in sales cube.
Fact table has customer code which is foreign key of Customer master dimension which in turn is linked to census data dimension. Census data dimension have city wise population data having foreign keys of zone and state.
I Recently uninstalled Visual Studio 2005 and SQL Server 2005 Tools from my machine, then I installed Visual Studio 2008 and SQL Server 2005 again. this way I can write normal .NET applications with vs 2008 and still be able to develop my reports with the BIDS.
I thought everything was working oke, until I had to modify a report. When I open the report and preview it...it works fine, but when I go back to the Data Tab, it clears out the mdx queries of all the datasets and I get the default blank designer.
I really need this fixed a.s.a.p. otherwise I can't make fixes to the reports.
I'm sure what I am trying to do is very simple - but I just can't seem to figure it out. I have a report based on a SSAS cube (SQL 2005). The report shows sales based on the dates the user selects from the parameter field (the date parameter field comes from a Y-Q-M-D hierarchy). This all works fine.
What I would like to happen is for the members within the last 3 months to be automatically selected so that the report automatically executes for the last 3 months.
Can anyone help or offer any advice. If possible I would like to achieve this using the GUI features so that power users can use the "plug and pray interface".
I need to extract data from SSAS' cubes into a SQL Server table.
I already read examples using Linked server (with openquery), SSIS, etc. However, the result always return as many columns per dimension as levels. I need to extract all members of a dimension in a column. E.g., when excecuting the following MDX query in Adventure Works 2014:
select [Measures].[Sales Amount] on columns,     Non Empty [Date].[Calendar].members on rows  from [Adventure Works]
I would like to get this result (MDX query in SSMS), but with keys displayed intead of names:
I have 5 cubes, and hierachy defined for all cubes. for example:geography database with 5 continents as cubes and contries as dimensions.Now when i am doing security restrictions on my dimension ex: In USA dimension if i want only to give access to texas region then i should be able to see only texas cities. But i cansee all the states under USA even after selecting only Texas region under Dimension data tab inside ROles section in SSMS.I have tried security at database ,cube level as well as dimension level.But still not working.is that because of some wrong design of cubes or something related to database design.? I am not able to undersand that except roles everything in my cubes or datawarehouse is working fine without and defect in data.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query sum(CASE WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31 WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0 ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)) AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2 ((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
We have an SSAS project that we want to auto deploy. I am not sure how to handle the external data sources in the project. This one in particular has a single external data source defined to Microsoft SQL Server.
I would like to be able to change the data source based on the environment. In SSIS projects I can do this by setting up environments in the SSISDB and linking them to project parameters in the SSIS project but SSAS projects don't seem to have a similar mechanism.
How to handle this? I would like to be able to have the build/deployment agent pass in server / database information to the data source based on environment (dev, QA, production).
The only way to automate this that I've discovered is to have an intermediary process that executes after the build that updates the generated .asdatabase and .configsettings files in the bin folder replacing the connection string information.
I have a MDX query , where I have a date Range in where clause.
I want to replace it with Cuurent Date and Last 7 days date.
I tried multiple ways using NOW function , but could not get it correct .
modifying the Query so that I can fetch DATA for last 7 daysÂ
SELECT NON EMPTY { [Measures].[X] } ON COLUMNS, NON EMPTY { ([PRODUCT].[PRODUCT].[PRODUCT].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [COLOR].[COLORName].&[BLACK], [COLOR].[COLORName].&[BLUE] } ) ON COLUMNS FROM ( SELECT ( [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[3].&[7].&[20150706] : [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[2].&[6].&[20150629] ) ON COLUMNS FROM [MYCUBE]))
I want to replace Date Hard code value , I have used Calendar Hierarchy of date dimension. to find Last 7 days Data.
Is there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
