Analysis :: Temp Table Like Structure In MDX To Process Intermediate Result
Aug 5, 2015
I have started working on SSAS since last week, I need to perform some calculations on the data fetched from the cube based on the parameters. SSRS is used to display the output and SSAS is used as a data source.
How i could perform the operations on the data fetched from the cube in SSAS? Does SSAS provides the storage structure like temp table in stored procedure where we can perform the various operations before sending final data back to the client side tool(SSRS)?
OR Is there any alternative way to perform the operations based on the input provided through the parameters
Hello Room, M stuck with an issue with SQL Query. My requirement is to create a temporary table with the out put of "SELECT" Clause..........
Like....... SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC
This is my "Select" Query and all I want is to store the out put in a #table. Now one way is to craete #tmp_table with the fields same as the out put column of the "SELECT" Clause......... But I want it in direct fashion.... dat means without Creating the #tmp_table directly...........
I tried with "CREATE TBALE #tmp_table AS SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC".......... But it dini work
Hi, I need to create a table which has the columns from the select statement result. I tried in this way drop table j9a SELECT er.* into j9a FROM caCase c LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID Left Join exexposure ee ON ee.cacaseID=c.caCaseID LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID WHERE c.caCallTypeID =0 AND c.Startdate between '1/1/2006' and '12/1/2006' AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10) AND pp.paSpeciesID=1 AND c.PublicID_adOrganization_secondary is null
set @21=(select count(*) from j9a whereIngestion=1) set @22=(select count(*) from j9a whereInhalation/nasal=1) set @23=(select count(*) from j9a whereAspiration=1) set @24=(select count(*) from j9a whereOcular=1) set @25=(select count(*) from j9a whereDermal=1) set @26=(select count(*) from j9a whereBite=1) set @27=(select count(*) from j9a whereParenteral=1) set @28=(select count(*) from j9a whereOtic=1) set @29=(select count(*) from j9a whereRectal=1) set @30=(select count(*) from j9a whereVaginal=1) set @31=(select count(*) from j9a whereOther=1) set @32=(select count(*) from j9a whereUnknown=1)
Create table table9(Route varchar(30),Fatal int) insert into table9 values ('Route_Ingestion',@1,@21) insert into table9 values ('Route_Inhalation',@2,@22) insert into table9 values ('Route_Aspiration',@3,@23) insert into table9 values ('Route_Ocular',@4,@24) insert into table9 values ('Route_Dermal',@5,@25) insert into table9 values ('Route_Bite',@6,@26) insert into table9 values ('Route_Parenteral',@7,@27) insert into table9 values ('Route_Otic',@8,@28) insert into table9 values ('Route_Rectal',@9,@29) insert into table9 values ('Route_Vaginal',@10,@30) insert into table9 values ('Route_Other',@11,@31) insert into table9 values ('Route_Unknown',@12,@32)
select * from table9
The exRoute result is like this 70 Ingestion 71 Inhalation 72 Aspiration 73 Ocular 74 Dermal 75 Bite/sting 76 Parenteral 77 Other 78 Unknown 524 Otic 525 Rectal 526 Vaginal
The above giving the errors Msg 207, Level 16, State 1, Line 19 Invalid column name 'Ingestion'. Msg 207, Level 16, State 1, Line 20 Invalid column name 'Inhalation'.
question: get 10 gram of the gold from Products? i cannot think a good algorithm,just use a clumsy proach to do this job ,but also failed,the last statement occur a Error "#result" have syntax error; here my code: --------test condition-------------Create table Qt(id int identity primary key,Q int)Goinsert into Qt(Q) values(8);insert into Qt(Q) values(6);insert into Qt(Q) values(6);insert into Qt(Q) values(5);insert into Qt(Q) values(3);insert into Qt(Q) values(3);insert into Qt(Q) values(1);insert into Qt(Q) values(0);Go------------------------------- Create procedure Test2( @m int) ASdeclare @i int,@j int,@n int ,@id int ,@q int ,@last intset @i=1;set @j=1;Create table #result(id int, Q int)declare __cursor cursor forselect * from Qt where Qt.Q <=@m order by Qt.q desc for read onlyOpen _cursor;select @n=@@cursor_rows;fetch last from _cursor into @lastif @n > 0 -------------------beginBegin_this:fetch absolute @i from __cursor into @id,@qif @q=@m begininsert into #result(id,Q) values(@id,@q);GoTo End_this;End-------------------else-------------------------------begin------------------------if @q=@lastbeginSet @j=@j+1;------------if @j>@n Goto End_this;elseBeginset @i=@j;Delete from #result;End------------Endelsebegininsert into #result(id,Q) values(@id,@q);set @i=@i+1;end------------------------GoTo Begin_thisEnd----------------------------- End_this:select id,Q from #result--close __cursor--deallocate __cursor .....please help me, =A='
I used sp_executesql to execute a dynamically built string containing an SQL statement, is there any way I can insert the results into a temporary table?
Hi Folx, I am new to SQL Server and I am struggling.
Versions: Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
Microsoft SQL Server Management Studio 9.00.1399.00
I would like to 01. create a temp table 02. load the temp table from a flat file 03. insert into a destination table the rows from the temp table where NOT EXIST the primary key of the destination table.
ISSUES:
Flat File Source will not accept that a resource will be available that does not yet exist (the temp table)
I set the Flat File Source to Ignore Failure? and ran the package. It ran with warnings but did not insert the new rows.
The Ignore Duplicates? radio button is grayed out? because the index is clustered
Now I could work around this thing by keeping a table just for purposes of this process flow. I am opposed to that philosophically and would prefer to do this in the way that I consider appropriateis there a solution?
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income ( ID int not null identity(1, 1) [Date] datetime not null, GoodID int not null, PriceDeliver decimal(18, 2) not null, PriceSalse decimal(18, 2) not null, CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID), CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID ) )
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
com.microsoft.sqlserver.jdbc.SQLServer Exception: A server cursor cannot be opened on the given statement or statements . Use a default result set or client cursor.
If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.
SP :
create proc testProcedure @countrCode varchar(3) as
select countryname INTO #TMPCOU from country where countryCode = @countrCode SELECT COUNTRYNAME FROM #TMPCOU
I am writing a BI solution for a recruitment company. In their business, the can be n number of participants from different dimensions linked to the same fact record. For example, a client can be sent the CV of 50 candidates. That's my first problem. My second problem is the variety of dimension participant types for a given fact record. This results in the need for nullable dimension FK's - which I'm trying to avoid. For example, consider the following two business events. In the first one, a candidate fills a job. Easy, we have a record in the fact table where the fact table has the following columns: DateKey, EventType, CandidateKey, VacancyKey. No nullable columns, great. But there are other events that I want to store in the fact table too. Let's go back to my first example: The client is sent CV's of 50 candidates in one transaction. So there is one client linked to the fact, but 50 candidates. So now I need to extend the fact table and add another column: CandidateGroupKey (which links to and Intermediate Fact Table). But in this case there was no vacancy involved. So do I now have to make the VacancyKey column nullable? That doesn't seem like a good idea... Or do I have to go for a completely different approach and have different fact tables instead of just one?
I know, this is a common OLAP Error, but In fact I'm having trouble with this while trying to process a DM Mining Structure. I'm currently working on a website that gets data from its users and analyzes it using SSAS. The thing is each time we add a new "analysis criterium" (sorry I'm trying to translate our french BI language in English...), we have to build a new mining structure, which needs data about users who have actually answered the question associated with this criterium. Some times, there are thousands, and some other only dozens, which is the case for the structure I'm having trouble with.
I got only 2 hundred tuples in the learning set. So lots of the common criteria weren't filled: I removed them using a stored procedure before feeding the structure, so that I got no column with only "null" values.
Of course, I know that 200 learning cases is really not enough to build an accurate model, but the purpose was just a proof of concept for machine driven Mining Structure building, and that was supposed to work even with so few cases. When I process the MS, it fires: (Sorry it's in french, translation follows) Erreurs dans le moteur de stockage OLAP : La clé d'attribut est introuvable : Table : _x0032_0_EtudeIphone_Apprentissage, Colonne : EtudeIphone, Valeur : le nouvel iPhone (téléphone tactile et musical dApple). Erreurs dans le moteur de stockage OLAP : La clé d'attribut a été convertie en un membre inconnu parce que cette dernière est introuvable. Attribut Id de la dimension : 20_EtudeIPhone ~MC-Id de la base de données : ClassificationVDCE, Enregistrement : 2.
Badly translated it says "Errors in OLAP Storage Engine: Attribute Key not found Table:<StrangeTable> Column <MyPredictableColumn> Value <OneOfTheInterestingValues> Errors in OLAP Storage Engine: Attribute key not found: converted to an unknown member. Attribute Id from dimension..."
Why? Too few cases? I have structures based on the same template but associated with other criteria and they work perfectly.
I'm ready to answer any question, and give any detail. Thanks in advance.
is there a way to query an excel spreadsheet directly from sql without using ssis or excel macros?...and without saving the spreadsheet to a table first?
I have a calculation that granularity is on a specific level and therefore would like the calculation to be only visible/calculate when with a specific dimension structure & attributes
SCOPE([Measures].[Complaint Rate]); SCOPE([Item Dimension].[Item Structure].Members); this=(IIF([Measures].[Sales Units]=0,NULL,(([Measures].[Count]/[Measures].[Sales Units])*1000000))); END SCOPE; END SCOPE;
This displays the calculation all the time even if no item attributes are selected, I only want the calculation applicable to the structure & attributes belonging to structure - I tried children, currentmember etc. but no luck - its probably something small I am missing. Tried this too
I posted a related thread before about this error below when I process a dimension. And seems that the solution by using "ClearCache" can not fingure out the issue when I want to process a mining structure...... .
OLE DB error: OLE DB or ODBC error: There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure SQL Server with more procedure cache. ; Sort failed because there is insufficient procedure cache for the configured number of sort buffers. Please retry the query after configuring lesser number of sort buffers.
Could someone please give me some suggestions? Your help will be very appreciated:-)
I know that I've read two good step-by-step descriptions of the process by which a SELECT statement is processed into a result set. One was written at the theoretical level for ISO standard SQL, and I'm pretty sure that Joe Celko wrote it. The second was a much more practical and "hands on" description for Microsoft SQL Server that I think was written by either Richard Waymire or Euan Garden.
I'd really like to find the Microsoft specific discussion, but I can't for the life of me construct a query for any of the web search engines that will turn up either of these documents. Can anybody help me on this?
I want to perform SPC on a data set. The type of analysis I want to do is Mode,Median,Max,Min,Average,standard deviation.
Does Sql server have any in built functions to accomadate this type of analysis. Is there any information on the net that I could be referred to. I have SQL Server 7.0 Enterprise Edition.
On an Execute Process Task, are there properties I can examine to determine the outcome of a process? I know that I can use a Success/Failure constraint to direct my workflow appropriately, depending on the outcome, but I would prefer the workflow continue on to the same task regardless, and then be able to change the flow later on based on the outcome of the process.
I see there is an ExecValueVariable property, and this may be what I'm looking for, but I have no idea what this is and can not find any documentation on it. I tried using it, but kept getting a runtime error trying to set it to a variable.
I know that a process returns a numeric value, and it would be nice if I could store this somewhere.
I'm building a cube for sales team , to test out I'm trying to process just one dimension called DimCalandar , when I try to process this dimension I get the following error ,
'Either the user abc/def, does not have access to database, or the database does not exist' ...
I want to display my problem. I have a cube that connected to hive DB through views. There are some changes that apply to some related tables on hive. This changes reflect on the cube so i make full process for the cube. I want to process only partitions that have been changed without full process. I detect changes on this table on another table on the local database.
I wonder if you can help with the following requirement.
I want to be able to conditionally execute an 'execute process task' depending on the result of a query. I have a table which I will select one record/row from upon each execution, this record has a char 1 'type' field which is the indicator for what process to then execute.
This should be quite a simple package and will be run every 60 seconds so needs to be as efficient as possible.
I am thinking I should go along the lines of using an Execute SQL task to select my row in to a result set, and using a series of precedence expressions to determine what process to execute. But im not really sure how.....
I am a newbie to SSIS and 2005 in general so would appreciate any help you can provide
I trying to update my Aggregation Design for a partition using BIDS Helper. The current aggregation design contains about 60 aggregations and the new aggregation I am trying to add is across 5 dimension attributes, the product of which is about 500,000 unique values. The fact table is about 13,000,000 rows.
When I deploy the aggregation and run ProcessIndex, I get the follow error:
File system error: The following file is corrupted: Physical file: ?E:Program FilesMicrosoft SQL ServerMSAS10_50.MSSQLSERVEROLAPDataTestDB.14.dbcube_2.607.cub cubemeasuregroup_2.633.detDefaultPartition_2.579.prt852.agg.flex.data. Logical file.
If I remove the new aggregation, deploy, and run ProcessIndex again, it processes fine.
Is there some file size limitation I am running into? The agg.flex.data file is 7.8 GB before adding the new aggregation, so it isn't subject to the same 4 GB limit as .asstore.
I am trying to count the columns of a MDX query but I haven't yet found a solution. Below is the query
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All]},,,INCLUDE_CALC_MEMBERS)}) ON COLUMNS FROM [XCUBE] WHERE ( [TIME].[Year Quarter Month].[Quarter Name].&[Quarter 1, 2015], [CustomerType].[CustomerType].&[Money], [Measures].[Total X] )
With result as
All r k l j g l 7000 1 7 8 5 4 3
The All counts the total of the [Measures].[Total X] and I want to measure the number of columns that presented at the result. Unfortunately my dimension Customers doesn't connect with time dimension...
